iT邦幫忙

0

有關MySQL合併問題

我的SQL語法如下

SELECT ps_customer.id_customer,
 	   COUNT(ps_orders.id_order) AS pef,
 	   0 AS pin
FROM ps_customer
LEFT JOIN ps_orders ON ps_orders.id_customer=ps_customer.id_customer		WHERE (ps_orders.id_order IS NULL OR ps_customer.id_customer IN (SELECT id_customer FROM ps_orders) AND valid = 1)
AND siret IS NOT NULL AND mobile IS NOT NULL AND mobile != '' AND active=1
AND date_format(ps_customer.date_add,'%Y-%m')<date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m')
AND ps_customer.id_customer = 88
    UNION
SELECT ps_customer.id_customer,
	   0 AS pef,
 	   COUNT(ps_orders.id_order) AS pin
FROM ps_customer
LEFT JOIN ps_orders ON ps_orders.id_customer=ps_customer.id_customer
WHERE (ps_orders.id_order IS NULL OR ps_customer.id_customer IN (SELECT id_customer FROM ps_orders) AND valid = 0)
AND siret IS NOT NULL AND mobile IS NOT NULL AND mobile != '' AND active=1
AND date_format(ps_customer.date_add,'%Y-%m')<date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m')
AND ps_customer.id_customer = 88;

撈出結果如下
https://ithelp.ithome.com.tw/upload/images/20200304/20115410KAYzkGZphG.png
我現在撈出2筆結果那我想要把重複的值合併成一筆結果,我該怎麼下語法呢?我研究很久都沒有解答,不知道各位前輩有什麼好辦法可以給小弟指點一下迷津,感激不盡!!!!

https://ithelp.ithome.com.tw/upload/images/20200304/20115410FLCzciSfuh.png
大大好這是我預期的結果就是那個pin欄位值5跟pef欄位值20在同一筆結果裡面

看更多先前的討論...收起先前的討論...
石頭 iT邦研究生 2 級 ‧ 2020-03-04 17:18:00 檢舉
Subquery with sum 就可以了
但我覺得你的語法可以更進一步優化,如果提供原始樣本資料 我覺得可以寫出效能更好更漂亮的語法
你指的樣本資料是??
我是有想過用子查詢的寫法來寫,但是怎麼寫都一直錯
石頭 iT邦研究生 2 級 ‧ 2020-03-04 17:54:56 檢舉
依照表樣本資料和你預期的結果
預期結果已經在上面囉!!!!
石頭 iT邦研究生 2 級 ‧ 2020-03-04 19:05:19 檢舉
uh....你上面的不是sample data.

sample data 像是這個問題

https://stackoverflow.com/questions/50064188/group-by-with-sum-without-removing-empty-null-values/50064252#50064252
沒是解決了
那我想問一下你說的Subquery with sum的做法是什麼呢?
可以在說的詳細一些嗎?

1 個回答

0
石頭
iT邦研究生 2 級 ‧ 2020-03-04 17:56:30
最佳解答

如我留言那樣,最快解決你目前問題可以使用 Subquery with sum

但我覺得你的語法可以更進一步優化,如果提供原始樣本資料 我覺得可以寫出效能更好更漂亮的語法

SELECT id_customer,sum(pef),sum(pin)
FROM (
	SELECT ps_customer.id_customer,
 	   COUNT(ps_orders.id_order) AS pef,
 	   0 AS pin
	FROM ps_customer
	LEFT JOIN ps_orders ON ps_orders.id_customer=ps_customer.id_customer WHERE (ps_orders.id_order IS NULL OR ps_customer.id_customer IN (SELECT id_customer FROM ps_orders) AND valid = 1)
	AND siret IS NOT NULL AND mobile IS NOT NULL AND mobile != '' AND active=1
	AND date_format(ps_customer.date_add,'%Y-%m')<date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m')
	AND ps_customer.id_customer = 88
	UNION
	SELECT ps_customer.id_customer,
		   0 AS pef,
		   COUNT(ps_orders.id_order) AS pin
	FROM ps_customer
	LEFT JOIN ps_orders ON ps_orders.id_customer=ps_customer.id_customer
	WHERE (ps_orders.id_order IS NULL OR ps_customer.id_customer IN (SELECT id_customer FROM ps_orders) AND valid = 0)
	AND siret IS NOT NULL AND mobile IS NOT NULL AND mobile != '' AND active=1
	AND date_format(ps_customer.date_add,'%Y-%m')<date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m')
	AND ps_customer.id_customer = 88
)t1
GROUP BY id_customer

我直接執行會報錯誤耶

石頭 iT邦研究生 2 級 ‧ 2020-03-04 18:43:17 檢舉

原因出在別名 已經修正好了 在試試看

那如果我想要再把上面那些欄位給加上去要怎麼處理呢,我試著想要加上去但是有問題

我要發表回答

立即登入回答