有關MySQL合併問題

mysql 資料庫 php 合併

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

分享至

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