使用：
SELECt item_name, MAX(red) 'red', MAX(blue) 'blue', MAX(green) 'green' FROM (SELECt t.item_name, CASEWHEN c.color_name = 'red' THEN 1ELSE NULL END 'red', CASEWHEN c.color_name = 'blue' THEN 1ELSE NULL END 'blue', CASEWHEN c.color_name = 'green' THEN 1ELSE NULL END 'green'FROM ITEMS t JOIN ITEM_COLOR ic ON ic.item_id = t.item_id JOIN COLORS c ON c.id = ic.color_id)GROUP BY item_name
如果要与某个项目关联的红色/蓝色/绿色的总数，请将MAX更改为COUNT。
备用使用子查询分解：
WITH icolors AS ( SELECt t.item_name, CASEWHEN c.color_name = 'red' THEN 1ELSE NULL END 'red', CASE WHEN c.color_name = 'blue' THEN 1 ELSE NULL END 'blue', CASE WHEN c.color_name = 'green' THEN 1 ELSE NULL END 'green'FROM ITEMS t JOIN ITEM_COLOR ic ON ic.item_id = t.item_id JOIN COLORS c ON c.id = ic.color_id) SELECt t.item_name, MAX(t.red) 'red', MAX(t.blue) 'blue', MAX(t.green) 'green' FROM icolors tGROUP BY t.item_name
