对id列执行联接以连接两个表。然后根据颜色对项目求和。
-- Find the total cost where item colour is blue.
SELECT SUM(t2.cost) AS TotalCost
FROM Table2 t2
JOIN Table1 t1 ON t2.id = t1.id
WHERE t1.colour = 'blue';
注:为了清晰起见,我使用了表别名t1和t2。当列名不重叠/冲突时,不严格需要它们。
如果需要item count,可以使用MySQL函数count()来查找集合中匹配的行数。
-- Find the number of items & total cost where item colour is blue.
SELECT
COUNT(*) AS BlueItemCount,
SUM(t2.cost) AS CostOfBlueItems
FROM Table2 t2
JOIN Table1 t1 ON t2.id = t1.id
WHERE t1.colour = 'blue';