目前我在 mysql 中有 2 個表，它們是串列表和代理表。串列表包含所有狀態、refno 和用于該特定產品的 agent_id。現在這些代理的名稱存盤在我的代理表中。因此，要使用特定串列參考代理名稱，我使用inner join代理表并檢查所有條目，其中agent_id = id. 這給了我正確的輸出。
現在我正在使用各自的代理檢索每個產品的所有狀態的計數：
select SUM(t.status = 'D' ) AS draft,
SUM(t.status = 'N' ) AS unpublish,
SUM(t.status = 'Y' ) AS publish,
SUM(t.status = 'S' ) AS sold,
SUM(t.status = 'T' ) AS let, c.name
FROM listings t INNER JOIN agents c ON t.agent_id = c.id
GROUP BY t.agent_id
但是現在我想要一個額外的行和列來計算每行和每列中的資料總數。像這樣的東西：
dbfiddle 鏈接：https ://dbfiddle.uk/ ? rdbms = mysql_8.0 & fiddle = f1f7ce90a0562b75f62641272ccf1a93
uj5u.com熱心網友回復：
簡單地將 5 個當前的布爾運算式相加：
SELECT
COALESCE(c.name, 'Total') AS name,
SUM(t.status = 'D') AS draft,
SUM(t.status = 'N') AS unpublish,
SUM(t.status = 'Y') AS publish,
SUM(t.status = 'S') AS sold,
SUM(t.status = 'T') AS let,
SUM(t.status = 'D') SUM(t.status = 'N') SUM(t.status = 'Y')
SUM(t.status = 'S') SUM(t.status = 'T') AS Total
FROM listings t
INNER JOIN agents c
ON t.agent_id = c.id
GROUP BY
t.agent_id
WITH ROLLUP;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/322122.html
標籤：mysql