電腦領域 HKEPC Hardware - Powered by Discuz! Board

標題: 我想要無NetSales within 180日 to 90 日出零... [打印本頁]

作者: luckiejacky 時間: 2013-9-17 12:31 標題: 我想要無NetSales within 180日 to 90 日出零...

想請問如果沒有Sales within that period, how do I get a 0 for that customer?

select c.CustomerID, o.PurchaseDateTime, IFNULL(sum(round(od.NetSales,2)),0) as NetSales180
from customers AS c LEFT JOIN orders AS o
ON (c.CustomerID = o.CustomerID)
LEFT JOIN order_details AS od ON (o.OrderID = od.OrderID)
where DATE(o.PurchaseDateTime) BETWEEN CURDATE() - INTERVAL 180 DAY AND CURDATE() - INTERVAL 90 DAY
group by c.CustomerID

複製代碼

Customer ID    NetSales180
1                         0
2                         3000.0
3                         0

現在我一條row 都無....
THX

作者: henrywho 時間: 2013-9-17 12:38

1st of all, as a rule of thumb:
(a) ifnull() before sum()
(b) sum() before round()

hence, it should be round(sum(IFNULL(od.NetSales,0)),2)

作者: luckiejacky 時間: 2013-9-17 13:30

本帖最後由 luckiejacky 於 2013-9-17 13:31 編輯

Thanks henry
Actually, No customers have sales in that period, but Still i don't get zeros
rows from what you suggested...

作者: rabbit82047 時間: 2013-9-17 13:49

本帖最後由 rabbit82047 於 2013-9-17 13:51 編輯

照常理句 SQL 會出 PurchaseDateTime is not specified in group by clause
catch SQLException 睇下

仲有就係, 既然要攞 sum over time, 點解仲要 PurchaseDateTime?

作者: luckiejacky 時間: 2013-9-17 17:26

照常理句 SQL 會出 PurchaseDateTime is not specified in group by clause
catch SQLException 睇下

仲有 ...
rabbit82047 發表於 2013-9-17 13:49

當我block out段 Date selection code
I get this

[attach]1581541[/attach]

作者: rabbit82047 時間: 2013-9-17 22:10

本帖最後由 rabbit82047 於 2013-9-17 22:12 編輯

select c.CustomerID, min(o.PurchaseDateTime), max(o.PurchaseDateTime), round(sum(coalesce(od.NetSales,0)),2) as NetSales180
from customers AS c LEFT JOIN orders AS o ON (c.CustomerID = o.CustomerID)
LEFT JOIN order_details AS od ON (o.OrderID = od.OrderID) and
DATE(o.PurchaseDateTime) BETWEEN CURDATE() - INTERVAL 180 DAY AND CURDATE() - INTERVAL 90 DAY
group by c.CustomerID

複製代碼

理論上可以完全忽略 where clause, 將個 range condition 寫落去 join condition
===
唔 check 都唔知 mariadb/mysql 有所謂 group by extension

http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

個人覺得與其俾 db 抽 PurchaseDateTime
倒不如加 min, max, 出黎既 result 先有意思
或者乾脆唔要 PurchaseDateTime 好過

作者: franklee006 時間: 2013-9-17 23:44

Ching 你做個 ordering system 有冇咩Demo 比我地睇睇
我上個月留意到你開始做有少少好寄你依加做成點

作者: justlazy 時間: 2013-9-18 03:00

Ching 你做個 ordering system 有冇咩Demo 比我地睇睇
我上個月留意到你開始做有少少好寄你依加做成點 ...
franklee006 發表於 2013-9-17 23:44

好奇 +1

作者: henrywho 時間: 2013-9-18 22:31

本帖最後由 henrywho 於 2013-9-24 10:30 編輯

Actually, No customers have sales in that period, but Still i don't get zeros
rows from what you suggested.
luckiejacky 發表於 2013-9-17 13:30

of course, bcoz i was fixing your another problem which is more fundamental :P