| 員工 | 數量 | 支付碼 |
|---|---|---|
| 121 | 0 | 薪水 |
| 121 | 5 | 津貼 |
| 122 | 0 | 薪水 |
| 122 | 0 | 津貼 |
| 123 | 5 | 薪水 |
| 123 | -5 | 津貼 |
我被這個問題困住了。我只想選擇所有金額為零（0）的員工。嘗試使用HAVING SUM(Amount)但還需要選擇像上面的員工 123 這樣的記錄。輸出必須是：
| 員工 | 數量 | 支付碼 |
|---|---|---|
| 121 | 0 | 薪水 |
| 121 | 5 | 津貼 |
| 123 | 5 | 薪水 |
| 123 | -5 | 津貼 |
uj5u.com熱心網友回復：
你走在正確的軌道上。考慮這個版本：
SELECT EMPLOYEE
FROM yourTable
GROUP BY EMPLOYEE
HAVING SUM(AMOUNT <> 0) = 0;
您也可以這樣說，零數量計數等于總計數：
SELECT EMPLOYEE
FROM yourTable
GROUP BY EMPLOYEE
HAVING SUM(AMOUNT = 0) = COUNT(*);
uj5u.com熱心網友回復：
您可以使用exists. 首先，我選擇了至少有一行金額不為 0 的所有員工。然后exists()我選擇了這些員工的所有行。
模式和插入陳述句：
create table employee_payment(EMPLOYEE int, AMOUNT int, PAYCODE varchar(50));
insert into employee_payment values(121, 0, 'SALARY');
insert into employee_payment values(121, 5, 'ALLOWANCE');
insert into employee_payment values(122, 0, 'SALARY');
insert into employee_payment values(122, 0, 'ALLOWANCE');
insert into employee_payment values(123, 5, 'SALARY');
insert into employee_payment values(123, -5, 'ALLOWANCE');
詢問：
select * from employee_payment e
where exists
(
SELECT 1
FROM employee_payment ep
where ep.amount<>0 and e.EMPLOYEE=ep.EMPLOYEE
)
輸出：
| 員工 | 數量 | 支付碼 |
|---|---|---|
| 121 | 0 | 薪水 |
| 121 | 5 | 津貼 |
| 123 | 5 | 薪水 |
| 123 | -5 | 津貼 |
db<>在這里擺弄
uj5u.com熱心網友回復：
使用條件 SUM() 和視窗函式來獲取每位員工的總非零金額。然后排除全為 0 的
WITH cte AS (
SELECT *
, SUM(CASE WHEN Amount > 0 THEN 1 ELSE 0 END) OVER(PARTITION BY Employee) AS TotalNonZero
FROM Employee
)
SELECT Employee, Amount, PayCode
FROM cte
WHERE TotalNonZero > 0
;
結果：
| 員工 | 數量 | 支付碼 |
|---|---|---|
| 121 | 0 | 薪水 |
| 121 | 5 | 津貼 |
| 123 | 5 | 薪水 |
| 123 | -5 | 津貼 |
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/439654.html