sql 学习记录

查询在A表不在B表的数据

• 方式1

SELECT DISTINCT seq FROM "bom_component"
where seq not in 
(SELECT DISTINCT component_id FROM "bom_bomdetailed")

输出

• 方式2

SELECT DISTINCT seq, component_id FROM bom_component
left join bom_bomdetailed on bom_component.seq=bom_bomdetailed.component_id

输出

sqlite: RIGHT and FULL OUTER JOINs are not currently supported，sqlite 不支持左外连接和全连接

统计每个bom_id的所对应的sheet_name种类数

select count(sheet_name), bom_id 
from (
	select sheet_name, bom_id 
	from bom_bomdetailed 
	GROUP BY bom_id, sheet_name
	) 
GROUP BY bom_id

查询结果

表结构展示

id	sheet_name	bom_id
1	_BOM	33-20-3-0
2	_BOM	33-20-3-0
3	11_BOM	11-22-33-4
4	11_BOM	11-22-33-4