iT邦幫忙

0

有關MySQL查詢問題

SELECT All_customer.id_customer,mobile,chinese_firstname,chinese_lastname,firstname,lastname,msg_subscription,email,newsletter,SUM(pef) AS pef,SUM(pin) AS pin,All_customer.date_upd
FROM (SELECT ps_orders.id_order,ps_customer.id_customer,ps_customer.mobile,ps_customer.chinese_firstname,ps_customer.chinese_lastname,ps_customer.firstname,ps_customer.lastname,ps_customer.msg_subscription,ps_customer.email,ps_customer.newsletter,ps_customer.date_upd,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 ps_customer.active = 1
AND date_format(ps_customer.date_add,'%Y-%m')<date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m')GROUP BY ps_customer.id_customer
UNION
SELECT ps_orders.id_order,ps_customer.id_customer,ps_customer.mobile,ps_customer.chinese_firstname,ps_customer.chinese_lastname,ps_customer.firstname,ps_customer.lastname,ps_customer.msg_subscription,ps_customer.email,ps_customer.newsletter,ps_customer.date_upd,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 ps_customer.active = 1
AND date_format(ps_customer.date_add,'%Y-%m')<date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m')GROUP BY ps_customer.id_customer) All_customer

這是我現在下的語法結果如下圖!(先不用管欄位問題,我會自己處理)
https://ithelp.ithome.com.tw/upload/images/20200306/20115410RpNKYf8WVg.png

這語法是購物車的規則的語法

SLLECT * FROM ps_cart_rule WHERE id_cart_rule IN(2049,2048,1605,1604,1115,1114,1113,1103,965,964,963,962,660,659);

https://ithelp.ithome.com.tw/upload/images/20200306/201154108XTqoXnKzh.png

這個語法是訂單所使用的購物車規則,我要從最上面的語法撈出的35W會員裡面排除這張表裡面的4W多個會員

SLLECT * FROM ps_order_cart_rule WHERE id_cart_rule IN(2049,2048,1605,1604,1115,1114,1113,1103,965,964,963,962,660,659);

https://ithelp.ithome.com.tw/upload/images/20200306/20115410qO5DqEzfgj.png

然後這兩張表的id_cart_rule是關聯的

我現在想要排除ps_order_cart_rule裡面符合的id_cart_rule=2049,2048,1605,1604,1115,1114,1113,1103,965,964,963,962,660,659這些條件的會員,但是我不知該怎麼下條件一直報錯誤,請問我該怎麼下語法呢?我不管怎麼弄都是錯的,麻煩各位前輩們給小弟指點迷津,感激不盡!!

看更多先前的討論...收起先前的討論...
ckp6250 iT邦好手 1 級 ‧ 2020-03-06 16:45:45 檢舉
最好把您的 table 放上來!
已經貼上來囉
ckp6250 iT邦好手 1 級 ‧ 2020-03-06 20:58:21 檢舉
不是這種貼法啦,誰有辦法幫您的忙?
您要貼能產生資料的sql語法上來,
讓別人可以重建出和您一樣的結構和資料。
您好,我已經把語法重新貼上來都可以產生資料
你指的是圖片裡面的語法對吧?
ckp6250 iT邦好手 1 級 ‧ 2020-03-07 04:59:09 檢舉
沒看到!
謝謝你們的回答,我問題已經解決囉

1 個回答

0
rogeryao
iT邦大師 1 級 ‧ 2020-03-07 15:18:52
最佳解答
where All_customer.id_order IS NOT NULL
AND ps_order_cart_rule.id_cart_rule NOT IN (2049,2048,1605,1604,1115,1114,1113,1103,965,964,963,962,660,659)

謝謝你們的回答,我問題已經解決囉

我要發表回答

立即登入回答