MYSQL数据库IFNULL查询

Question

我尝试使用“IFNULL”将“NULL”值显示为 0，但它返回所有行为 null 或 0。一切正常，直到我添加“lounge2”表和“lounge2order2”表。 order2.lounge2orderid 为“NULL”。我知道它与“JOINS”有关，但不确定在哪里或实现哪个。

谢谢大家...

SELECT orders2.orderid, orders2.orderdate, branch2.branchname, COUNT(orders2.garment2orderid) AS 'no gar orders', SUM(garment2.hireprice) as 'total gar sold',
COUNT(orders2.lounge2orderid+IFNULL(orders2.lounge2orderid,0)) as 'No of lounge sales', SUM(lounge2.hirerate)

from orders2, branch2, garment2, garment2order2, lounge2, lounge2order2

WHERE orders2.orderid IN

            (SELECT orders2.orderid FROM orders2 
                WHERE orders2.branchid = 2 
                AND YEAR(orders2.orderdate)= 2011) 

AND branch2.branchid IN

            (SELECT branch2.branchid from branch2
                  WHERE branch2.branchid = orders2.branchid)

AND garment2order2.garment2orderid IN
            (SELECT garment2order2.garment2orderid FROM garment2order2
                WHERE garment2order2.garment2orderid = orders2.garment2orderid)

AND garment2.garmentid IN

            (SELECT garment2.garmentid FROM garment2
                WHERE garment2.garmentid = garment2order2.garmentid)

AND lounge2order2.lounge2orderid IN
            (SELECT lounge2order2.lounge2orderid FROM lounge2order2
                WHERE lounge2order2.lounge2orderid = orders2.lounge2orderid)

AND lounge2.loungeid IN

            (SELECT lounge2.loungeid FROM lounge2
                WHERE lounge2.loungeid = lounge2order2.loungeid)                    

Answer 1

首先，尽量避免FROMorders2,branch2,clothing2,clothing2order2,lounge2,lounge2order2，它必须是JOIN ... ON语句。这将帮助您进行调试，并帮助我们了解您的逻辑和表关系。

要让 SUM 或 COUNT 等聚合函数正常工作，您应该设置 GROUP BY 语句。

因为我不知道你的数据库结构是什么样的。

这是我的尝试:

SELECT 
  orders2.orderid, 
  orders2.orderdate, 
  COUNT(orders2.garment2orderid) AS 'no gar orders', 
  branch2.branchname, 
  SUM(garment2.hireprice) as 'total gar sold',
  SUM(IF(orders2.lounge2orderid IS NULL,1,0)) as 'No of lounge sales', 
  SUM(lounge2.hirerate)
FROM orders2
LEFT JOIN branch2
ON branch2.branchid = orders2.branchid
LEFT JOIN garment2order2
ON  garment2order2.garment2orderid = orders2.garment2orderid
LEFT JOIN garment2
ON garment2.garmentid = garment2order2.garmentid
LEFT JOIN lounge2order2
ON lounge2order2.lounge2orderid = orders2.lounge2orderid
LEFT JOIN lounge2
ON lounge2.loungeid = lounge2order2.loungeid)    
WHERE orders2.branchid = 2 
  AND YEAR(orders2.orderdate)= 2011
GROUP BY orders2.orderid