有關MySQL合併問題

mysql 資料庫 php 合併

jackabula23 2020-03-04 16:39:53 ‧ 1674 瀏覽

我的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;

撈出結果如下

我現在撈出2筆結果那我想要把重複的值合併成一筆結果，我該怎麼下語法呢?我研究很久都沒有解答，不知道各位前輩有什麼好辦法可以給小弟指點一下迷津，感激不盡!!!!

大大好這是我預期的結果就是那個pin欄位值5跟pef欄位值20在同一筆結果裡面

看更多先前的討論...收起先前的討論...

石頭 iT邦高手 1 級 ‧ 2020-03-04 17:18:00 檢舉

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

jackabula23 iT邦新手 5 級 ‧ 2020-03-04 17:29:06 檢舉

你指的樣本資料是??

jackabula23 iT邦新手 5 級 ‧ 2020-03-04 17:33:25 檢舉

我是有想過用子查詢的寫法來寫，但是怎麼寫都一直錯

石頭 iT邦高手 1 級 ‧ 2020-03-04 17:54:56 檢舉

依照表樣本資料和你預期的結果

jackabula23 iT邦新手 5 級 ‧ 2020-03-04 18:10:00 檢舉

預期結果已經在上面囉!!!!

石頭 iT邦高手 1 級 ‧ 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

jackabula23 iT邦新手 5 級 ‧ 2020-03-04 19:16:02 檢舉

沒是解決了

jackabula23 iT邦新手 5 級 ‧ 2020-03-04 19:27:31 檢舉

那我想問一下你說的Subquery with sum的做法是什麼呢?
可以在說的詳細一些嗎?

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

石頭

iT邦高手 1 級 ‧ 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

回應 3
分享
檢舉

jackabula23 iT邦新手 5 級 ‧ 2020-03-04 18:22:56 檢舉

我直接執行會報錯誤耶

石頭 iT邦高手 1 級 ‧ 2020-03-04 18:43:17 檢舉

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

jackabula23 iT邦新手 5 級 ‧ 2020-03-04 19:01:50 檢舉

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

登入發表回應

我要發表回答

立即登入回答

參賽組數

1123 組

團體組數

52 組

累計文章數

23096 篇

完賽人數

656 人

15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css react 程式設計 vue.js

IT邦幫忙