我目前在 2 個選擇陳述句上使用 UNION，雖然我得到了正確的資料，但在前端視圖中使用它時，它并不是我真正需要的
我目前正在使用此查詢：
SELECT
T.employee as employee,
'Orders' as TYPE,
SUM(CASE WHEN t.order_count < QUANT THEN t.order_count ELSE QUANT END) as DATA
FROM schemaOne.order_list T
WHERE t.order_date > CURRENT_DATE - 35 DAYS
group by t.employee
UNION
select
T.employee as employee,
'Sales' as TYPE,
sum(price * quant) as DATA
from schemaOne.sales T
WHERE T.sales_date > CURRENT_DATE - 35 DAYS
group by T.employee
order by data desc;
以這些虛擬表為例，得到以下結果：
order_list
employee | order_count | quant | order_date
--------------------------------------------------
123 | 5 | 1 | 2022-03-02
456 | 1 | 5 | 2022-03-02
sales
employee | price | quant | order_date
--------------------------------------------------
123 | 500 | 1 | 2022-03-02
456 | 1000 | 1 | 2022-03-02
結果
employee | type | data
------------------------------------------
123 Orders 1
123 Sales 500
456 Orders 5
456 Sales 1000
有沒有辦法使用 UNION 但對其進行更改，以便我可以為每個員工獲取一行，而只是擺脫型別/資料列，而是將每條資料設定為所需的列（型別將改為列名）像這樣：
期望的結果
employee | Orders | Sales
---------------------------------
123 | 1 | 500
456 | 5 | 1000
uj5u.com熱心網友回復：
嘗試添加外部查詢：
select employee,
MAX(case when type=Orders then data end) as orders ,
MAX(case when type=Sales then data end) as Sales
from (
SELECT T.employee as employee,
'Orders' as TYPE,
SUM(CASE WHEN t.order_count < QUANT THEN t.order_count ELSE QUANT END) as DATA
FROM schemaOne.order_list T
WHERE t.order_date > CURRENT_DATE - 35 DAYS
group by t.employee
UNION
select T.employee as employee,
'Sales' as TYPE,
sum(price * quant) as DATA
from schemaOne.sales T
WHERE T.sales_date > CURRENT_DATE - 35 DAYS
group by T.employee
) as t1
GROUP BY employee;
請注意，我洗掉order by data desc了它在聯合內部沒有效果
uj5u.com熱心網友回復：
您可以通過employee列連接表，例如
SELECT o.employee,
SUM(CASE
WHEN o.order_count < o.quant THEN
o.order_count
ELSE
o.quant
END) AS Orders,
SUM(s.price * s.quant) AS Sales
FROM schemaOne.order_list o
JOIN schemaOne.sales s
ON s.employee = o.employee
AND s.sales_date = o.order_date
WHERE o.order_date > current_date - 35 DAYS
GROUP BY o.employee
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/451600.html
上一篇：SAS服務器行為例外
下一篇：Java模型類中的封裝