您好，我的數(shù)據(jù)庫中有兩個(gè)表，客戶和商店。我想獲得所有客戶并澄清客戶是否是債務(wù)人。對(duì)于這項(xiàng)工作，我想創(chuàng)建一個(gè) mysql 查詢來獲取客戶并加入商店表。我想為 if shops.price_statuscolumn = 1 come 和 shops 表中的總價(jià)格值設(shè)置條件。否則，商店表中價(jià)格列的那些行的值等于 0。我的商店表結(jié)構(gòu)和一些示例數(shù)據(jù)id customer_id product_id price price_status1 81 12 300 12 81 12 100 03 81 15 200 14 90 10 600 05 90 15 50 16 63 16 10 0還有我的客戶表（你應(yīng)該知道有些客戶不購買任何產(chǎn)品，因?yàn)槲蚁肟吹剿麄儯﹊d name63. Eva64. Nva81. Ali82. Bill90. Mosh我想得到這個(gè)結(jié)果Eva. 0. // Because price status = 0Nva. 0. Ali. 500 // because have two row that prices status = 1Bill. 0. Mosh. 50. // Because have one row with price status = 1
1 回答
ibeautiful
TA貢獻(xiàn)1993條經(jīng)驗(yàn) 獲得超6個(gè)贊
這可以做[MySQL if][1]
SELECT
c.`name`,sum(if(s.price_status=1,s.price,0)) as price
FROM
customer AS c
LEFT JOIN
shops AS s
ON
c.id=s.customer_id
GROUP BY
c.id;
如果你接受 null 值，price你可以使用
SELECT
c.`name`,sum(s.price_status*s.price) as price
FROM
customer AS c
LEFT JOIN
shops AS s
ON
c.id=s.customer_id
GROUP BY
c.id;
- 1 回答
- 0 關(guān)注
- 151 瀏覽
添加回答
舉報(bào)
0/150
提交
取消