您好，我的數據庫中有兩個表，客戶和商店。我想獲得所有客戶并澄清客戶是否是債務人。對于這項工作，我想創建一個 mysql 查詢來獲取客戶并加入商店表。我想為 if shops.price_statuscolumn = 1 come 和 shops 表中的總價格值設置條件。否則，商店表中價格列的那些行的值等于 0。我的商店表結構和一些示例數據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還有我的客戶表（你應該知道有些客戶不購買任何產品，因為我想看到他們）id name63. Eva64. Nva81. Ali82. Bill90. Mosh我想得到這個結果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貢獻1993條經驗 獲得超6個贊
這可以做[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 關注
- 139 瀏覽
添加回答
舉報
0/150
提交
取消