mysql - 如何在 mySQL- 语句中完成第三个内部连接的结果

Question

我使用 MySQL 数据库。

我今天跑这个题

SELECT t1.catalogID, t2.antfrp, t1.Latin, t1.Popular
FROM `mydb`.`products` T1
INNER JOIN `mydb`.`packages` T2 ON T1.catalogID = T2.catalogID
WHERE T1.Spring = 1
AND T1.lager + T1.sold > 0
AND (T2.packtyp = "L" OR T2.packtyp = "P")

它会产生几千行长的 RecordSet。在同一个 RecordSet 中 - 我希望将第三个表中的这个问题作为额外字段包含在每一行中

SELECT SUM(Numitems)
from oitems
WHERE
    catalogid = (the actual one for each row) AND
    orderid > 25746 AND (packtyp = "L" OR packtyp = "P") AND
    numitems = 1

并为此目的进行了第三次 Inner JOIN 操作

INNER JOIN `mydb`.`oitems` T3
    ON T3.catalogid = T2.catalogID

并将我的 SELECT 更改为

SELECT t1.catalogID, t2.antfrp, t1.Latin, t1.Popular, (SELECT SUM(T3.Numitems)
from oitems AS T3
WHERE T3.orderid > 25746 AND
    (T3.packtyp = "L" OR T3.packtyp = "P") AND T3.numitems = 1)

但是 - 这不是正确的方法 - 我感到被困住了，无法全神贯注地思考如何完成这个 - 对 MySQL 有更好理解的人是否可以看到我哪里出错了？？

Answer 1

你应该加入并做你正在使用聚合函数的事实你应该使用 group by t1.catalogID, t2.antfrp, t1.Latin, t1.Popular

SELECT SUM(T3.Numitems) , t1.catalogID, t2.antfrp, t1.Latin, t1.Popular
FROM `mydb`.`products` T1
INNER JOIN `mydb`.`packages` T2 ON T1.catalogID = T2.catalogID
INNER JOIN oitems T3 ON T3.catalogid = T1.catalogid 
WHERE T1.Spring = 1
AND T1.lager + T1.sold > 0
AND (T2.packtyp = "L" OR T2.packtyp = "P")
AND T3. orderid > 25746 AND (T3.packtyp = "L" OR T3.packtyp = "P") AND T3.numitems = 1
GROUP BY t1.catalogID, t2.antfrp, t1.Latin, t1.Popular