我想在一個查詢中計算所有商店和特定商店中客戶的總金額。沒有像我在下面所做的那樣重復相同的查詢兩次，也沒有使用 group_by。
*Simply, I want to set a WHERE condition for just one of "SELECTS"*
我的表是：客戶和訂單
Clients Orders
id name passport id client_id store_id gross_amount
1 Alex xxx 100 1 50 1000
2 Scott zzz 101 2 51 500
預期結果：
all_store my_store
1500 500
我的查詢是
SELECT
all_store.gross_amount,
my_store.gross_amount
FROM
(SELECT
SUM(orders.gross_amount) gross_amount
FROM
clients
JOIN orders ON clients.id = orders.client_id
WHERE
clients.passport = 'xxx' AND FROM_UNIXTIME(orders.date_time) >= NOW() - INTERVAL 1 year) as all_store,
(SELECT
SUM(orders.gross_amount) gross_amount
FROM
clients
JOIN orders ON clients.id = orders.client_id
WHERE
clients.passport = 'xxx' AND FROM_UNIXTIME(orders.date_time) >= NOW() - INTERVAL 1 year AND clients.store_id = '51') as my_store
uj5u.com熱心網友回復：
SUM使用CASE WHEN給定商店獲取特定商店的總金額
SELECT
SUM(orders.gross_amount) all_store,
SUM(CASE WHEN clients.store_id = '51' THEN orders.gross_amount ELSE 0 END) my_store
FROM
clients
JOIN orders ON clients.id = orders.client_id
WHERE
clients.passport = 'xxx' AND FROM_UNIXTIME(orders.date_time) >= NOW() - INTERVAL 1 year
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/360323.html