SQLite语法-连接来自3个表的数据

Question

我有3个sqlite表：

表inspections，其中insp_id是primary key

id | name         | deleted
------------------------------
I1 | Inspection A | (null)
I2 | Inspection B | (null)
I3 | Inspection C | 1

表equip_insp，其中equip_id, insp_id是primary keys

equip_id | insp_id  | period  | period_type
--------------------------------------------
E1       | I1       | 1       | Y
E1       | I2       | 6       | M
E2       | I1       | 1       | M

表equip_certif，其中id是primary key

id  | equip_id | insp_id | date       | certif_no | result | info
-------------------------------------------------------------------
C4  | E1      | I1       | 2015-02-01 | A-300     | Good   | (null)
C3  | E1      | I1       | 2015-02-01 | A-200     | Good   | (null)
C2  | E1      | I1       | 2015-01-10 | A-100     | Good   | (null)
C1  | E1      | I2       | 2015-01-06 | B-100     | Good   | (null)

事实上，所有的ID都是数字值，我使用一些字母只是为了便于将它们连接起来。

因此，我想帮助我使用Sqlite语法，即对于项目E1，显示所有定义的检查（升序），然后如果存在，显示周期性，然后显示最新的证书日期（如果在同一日期有两个证书，则获取最新的id）、编号和非信息的结果

结果应该是这样的：

id | name         | period | period_type | certif_no | date       | result
--------------------------------------------------------------------------
I1 | Inspection A | 1      | Y           | A-300     | 2015-02-01 | Good     
I2 | Inspection B | 6      | M           | B-100     | 2015-01-06 | Good

我试过这个，但我不太确定那是正确的。

SELECT inspections.id, inspections.name, equip_insp.period, equip_insp.period_type, equip_certif.certif_no, equip_certif.date AS certif_date, equip_certif.result
FROM inspections
LEFT JOIN equip_insp ON (inspections.id = equip_insp.insp_id AND equip_insp.equip_id = 'E1')
LEFT JOIN equip_certif ON (inspections.id = equip_certif.insp_id AND equip_certif.info IS NULL)
WHERE inspections.deleted IS NULL
GROUP BY equip_insp.insp_id
ORDER BY inspections.id, date(equip_certif.date) DESC, equip_certif.id DESC

Answer 1

要指定返回组中的哪一行，必须使用MAX（）；否则，你会得到一些随机行：

SELECT ..., MAX(equip_certif.date) AS certif_date, ...
FROM ...
GROUP BY equip_insp.insp_id
...

（这只适用于SQLite 3.7.11或更高版本；在早期版本中，查询会变得更复杂。）

Answer 2

玩过SQLite后，我自己得到了解决方案。所以答案是：

SELECT inspections.id, inspections.name, equip_insp.period, equip_insp.period_type, equip_certif.certif_no, equip_certif.date AS certif_date, equip_certif.result
FROM inspections
LEFT JOIN equip_insp ON (inspections.id = equip_insp.insp_id AND equip_insp.equip_id = 'E1')
LEFT JOIN equip_certif ON (inspections.id = equip_certif.insp_id AND equip_certif.equip_id = equip_insp.equip_id AND equip_certif.info IS NULL)
WHERE inspections.deleted IS NULL
GROUP BY inspections.id
ORDER BY inspections.id, date(equip_certif.date) DESC, equip_certif.id DESC