撰寫一個SELECT陳述句，回傳具有唯一年薪的每位講師的 LastName、FirstName 和 AnnualSalary 列。換句話說，不要包括與其他講師具有相同年薪的講師。
我試過的代碼是
SELECT DISTINCT
LastName,
FirstName,
AnnualSalary
FROM
Instructors i
WHERE
i.AnnualSalary NOT IN (SELECT AnnualSalary
FROM Instructors i1
WHERE i.AnnualSalary <> i1.AnnualSalary)
uj5u.com熱心網友回復：
你很接近，試試這個改變
SELECT
LastName,
FirstName,
AnnualSalary
FROM
Instructors i
WHERE
i.AnnualSalary NOT IN (SELECT AnnualSalary
FROM Instructors i1
group by AnnualSalary
having count(*) > 1)
我洗掉了 distinct 關鍵字，因為它不是必需的
uj5u.com熱心網友回復：
這是你的答案，
SELECT i1.*
FROM Instructors i1
WHERE (SELECT count(1)
FROM Instructors i2
WHERE i1.AnnualSalary = i2.AnnualSalary and not (i1.FirstName = i2.FirstName and i1.LastName = i2.LastName)
)<>1
希望你能得到你的答案。
uj5u.com熱心網友回復：
SELECT LastName, FirstName, AnnualSalary
FROM Instructors
WHERE AnnualSalary NOT IN
(SELECT AnnualSalary
FROM Instructors
GROUP BY AnnualSalary
HAVING COUNT(AnnualSalary) > 1)
ORDER BY LastName, FirstName;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/467835.html
上一篇：使用EFCore創建視圖
下一篇：將更改的員工和新員工追加到現有表