我有两张桌子。与公司同桌。与工作人员的另一张桌子。我想向他们的现任官员展示公司。
每个公司都有官员的历史。公司表如下所示：
-------------------------------
| company_number | name |
|-------------------------------|
| B1204_GnR103 | Flower LLC |
| B1204_GnR104 | Marketing LLC|
-------------------------------
官员表看起来像这样：
-----------------------------------------------
| id | name | company_id | start_date |
|-----------------------------------------------|
| 1 | John Doe | B1204_GnR103 | 2018-02-06 |
| 2 | Dianne Ameter| B1204_GnR104 | 2017-01-07 |
| 3 | Dylan Mering | B1204_GnR103 | 2016-04-23 |
| 4 | Fleece Gold | B1204_GnR103 | 2019-05-12 |
| 5 | Frank Kern | B1204_GnR104 | 2019-09-18 |
| 6 | Andreas Frank| B1204_GnR104 | 2017-03-08 |
-----------------------------------------------
我尝试了一个子选择，但无法使其正常工作。
我尝试了以下语句，但收到错误消息。
SELECT DISTINCT company.name,
officer.name,
officer.start_date
FROM company
INNER JOIN officer ON company.company_number =
(SELECT officer.name,
DATE_FORMAT(officer.start_date, '%Y-%m-%d') AS date
FROM officer
WHERE company.company_number = officer.company_id
ORDER BY date
LIMIT 1)
输出应为以下内容：
Company Name | Officer
----------------------------
Flower LLC | Fleece Gold
Marketing LLC| Frank Kern
因为这些人是最晚开始日期的人，并且是公司的现任官员。
但是我收到一条错误消息“ Operand应该包含1列”。
感谢您的任何建议，谢谢！
你需要JOIN你company和officer表最大的派生表start_date每家公司得到您想要的结果：
SELECT c.name AS company,
o.name AS officer,
o.start_date
FROM company c
JOIN officer o ON o.company_id = c.company_number
JOIN (SELECT company_id, MAX(start_date) AS start_date
FROM officer
GROUP BY company_id) m ON m.company_id = o.company_id AND m.start_date = o.start_date
输出：
company officer start_date
Flower LLC Fleece Gold 2019-05-12 00:00:00
Marketing LLC Frank Kern 2019-09-18 00:00:00
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句