Sql-Server
克服子查詢返回超過 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 ;