| 獎金 | 工人 | |
|---|---|---|
| 90 | 1 | |
| 90 | 2 | |
| 50 | 3 | |
| 40 | 4 | |
| 30 | 5 | |
| 20 | 6 | |
| 10 | 7 |
假設我有一個上面顯示的列（它可能以水平順序列出，抱歉錯誤）我撰寫了一個用戶定義的函式并想將獎金乘以 3，但我的列中有兩個相同的值。我希望該功能可以檢測兩個具有相同獎金金額的工人并列印“兩名工人具有相同的獎金金額”誰可以幫助擺脫這種情況？
Create function newfunction
(
@bonus as integer
, @workers as integer
)
returns varchar(25)
as
begin
declare @equality as integer
declare @returnvalue as varchar(25)
Select @equality = Bonus
from Employeestbl
Where Workers = @workers
If @equality = @Bonus set @returnvalue = 'there is an error'
return @returnvalue
end
select *, dbo.newfunction(employeestbl.bonus,employeestbl.workers)
from Employeestbl
uj5u.com熱心網友回復：
我希望該功能可以檢測兩個具有相同獎金金額的工人并列印“兩名工人的獎金金額相同”
這個查詢會發現：
IF EXISTS (SELECT Bonus, COUNT(*) FROM Table GROUP BY Bonus HAVING COUNT(*) > 1) PRINT 'two workers have same bonus amount'
想要將獎金乘以 3
UPDATE MyTable SET Bonus = Bonus * 3
假設您不想在兩個獎金相同的情況下應用獎金，這里有一個腳本來做到這一點：
IF EXISTS (
SELECT Bonus, COUNT(*) FROM Table GROUP BY Bonus HAVING COUNT(*) > 1
)
BEGIN
PRINT 'two workers have same bonus amount'
END
ELSE
BEGIN
UPDATE MyTable SET Bonus = Bonus * 3
END
uj5u.com熱心網友回復：
set returnvalue 這是一個變數，必須是set @returnvalue您在函式中存在語法錯誤。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/346126.html
標籤：sql sql-server 查询语句