克服子查詢返回超過 1 個結果

我發現這個語法已經回答了一個差異問題，但我得到了錯誤

返回超過 1 個結果

為了克服這個錯誤，我需要改變我的語法什麼？

Declare @ONE Table
(
   employee varchar(250)
   ,eod int
)

Declare @Two Table
(
   employee varchar(250)
   ,amtpaid decimal(10,2)
)

Insert Into @One (employee, eod) VALUES
('Frank',   '1122')
,('Frank',  '1188')
,('Bill',   '1100')
,('Larry', '1199')
,('Bill',   '2211')
,('Jerry',  '3311')
,('Jerry',  '3322')
,('Stan',   '3388')
,('Stan',   '3300')
,('Larry',  '3377')

INSERT INTO @Two (employee, amtpaid) VALUES
('Frank', '11.11'),('Bill', '10.10')
,('Larry', '44.44'),('Jerry', '20.32')
,('Stan', '100.33')

SELECT a.employee
   ,[EOD] = b.eod
   ,[Paid] = CASE 
       WHEN b.eod = (
               SELECT min(b.eod)
               FROM @Two
               WHERE b.employee=a.employee
               )
           THEN COALESCE((
                       SELECT TOP 1 SUM(ISNULL(amtpaid, 0))
                       FROM @Two b
                       WHERE a.employee = b.employee
                       ), 0)
       ELSE 0
       END
FROM @One b
INNER JOIN @Two a
ON a.employee = b.employee
GROUP BY a.employee, b.eod, b.employee
ORDER BY a.employee ASC

編輯

我想要的輸出如下所示-amtpaid 僅顯示 1 個員工姓名而不是全部

Employee    EOD Paid
Bill    1100    10.10
Bill    2211    0.00
Frank   1122    11.11
Frank   1188    0.00
Jerry   3311    20.32
Jerry   3322    0.00
Larry   1199    44.44
Larry   3377    0.00
Stan    3388    0.00
Stan    3300    100.33

不GROUP BY，不需要子查詢。答案假設@Two每行只有一行employee：

SELECT 
   a.employee,
   [EOD] = a.eod,
   [Paid] = CASE WHEN a.eod = MIN(a.eod) OVER (PARTITION BY a.employee)
                THEN b.amtpaid
                ELSE 0
            END
FROM 
   @One a
   JOIN @Two b
   ON  a.employee = b.employee ;

另一種方法是使用視窗函式和左連接：

SELECT 
   a.employee,
   [EOD] = a.eod,
   [Paid] = COALESCE(b.amtpaid, 0)
FROM 
   ( SELECT a.employee, a.eod,
            rn = ROW_NUMBER() OVER (PARTITION BY a.employee
                                    ORDER BY a.eod)
     FROM @One AS a
   ) AS a
   LEFT JOIN @Two b
   ON  a.employee = b.employee 
   AND a.rn = 1 ;

@Two如果每個員工有不止一行並且我們想要總和，這將更容易修改：

SELECT 
   a.employee,
   [EOD] = a.eod,
   [Paid] = COALESCE(b.amtpaid, 0)
FROM 
   ( SELECT a.employee, a.eod,
            rn = ROW_NUMBER() OVER (PARTITION BY a.employee
                                    ORDER BY a.eod)
     FROM @One AS a
   ) AS a
   OUTER APPLY
   ( SELECT amtpaid = SUM(b.amtpaid)
     FROM @Two b
     WHERE a.employee = b.employee 
       AND a.rn = 1
   ) AS b ;

引用自：https://dba.stackexchange.com/questions/163679