附錄：我不知道為什么這票數接近，因為“結果不可重復”。提供了模式和資料腳本，并提供了有問題的腳本。這一切都在那里。
此查詢失敗：
SELECT
P.Profession AS Profession,
CASE
WHEN P.AverageAnnualSalary >= 200000
THEN 'wow'
WHEN P.AverageAnnualSalary >= 100000
AND P.AverageAnnualSalary <= 199999
THEN 'nice'
WHEN P.AverageAnnualSalary >= 0
AND P.AverageAnnualSalary <= 99999
THEN 'ok'
ELSE P.AverageAnnualSalary
END AS Description
FROM
tProfession2 AS P
ORDER BY
Profession ASC
出現此錯誤：
訊息 235，級別 16，狀態 0，第 1 行
無法將字符值轉換為貨幣。char 值的語法不正確。
此查詢有效：
SELECT Profession AS Profession,
CASE
WHEN P.AverageAnnualSalary >= 200000 THEN 'wow'
WHEN P.AverageAnnualSalary >= 100000 THEN 'nice'
ELSE 'ok'
END AS Description
FROM tProfession2 P
ORDER BY Profession ASC
這是架構和資料：
/****** Object: Table [dbo].[tProfession2] Script Date: 12/14/2021 10:48:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tProfession2](
[ProfessionID] [int] IDENTITY(1,1) NOT NULL,
[Profession] [nchar](50) NOT NULL,
[AverageAnnualSalary] [money] NULL,
CONSTRAINT [PK_tProfession2] PRIMARY KEY CLUSTERED
(
[ProfessionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tProfession2] ON
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (1, N'Doctor ', 200000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (2, N'Attorney ', 350000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (3, N'Software Engineer ', 120000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (4, N'Cybersecurity Consultant ', 110000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (5, N'Network Engineer ', 111000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (6, N'Farmer ', 200000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (7, N'Chef ', 250000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (8, N'Truck Driver ', 99000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (9, N'Wilderness Guide ', 45000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (10, N'HVAC Technician ', 79000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (11, N'Electrician ', 80000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (12, N'TV Personality ', 450000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (13, N'NFL Quarterback ', 2000000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (14, N'Soccer Player ', 4000000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (15, N'Electrical Engineer ', 130000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (16, N'Computer Engineer ', 135000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (17, N'Business Owner ', 4500000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (18, N'Politician ', 20000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (19, N'City Manager ', 90000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (20, N'Landscaper ', 80000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (21, N'Pilot ', 200000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (22, N'Insurance Salesperson ', 230000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (23, N'Interior Designer ', 55000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (24, N'Architect ', 600000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (25, N'IRS Agent ', 99000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (26, N'Accountant ', 120000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (27, N'Tax Preparer ', 60000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (28, N'Spy ', 0.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (29, N'Military Officer ', 45000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (30, N'Veterinarian ', 300000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (31, N'Roofer ', 67000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (32, N'Arborist ', 76000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (33, N'Painter ', 60000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (34, N'Flight Attendant ', 50000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (35, N'Amish Farmer ', 10000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (36, N'Carpet Installer ', 40000.0000)
GO
INSERT [dbo].[tProfession2] ([ProfessionID], [Profession], [AverageAnnualSalary]) VALUES (37, N'Baker ', 900000.0000)
GO
SET IDENTITY_INSERT [dbo].[tProfession2] OFF
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_tProfession] Script Date: 12/14/2021 10:48:42 AM ******/
ALTER TABLE [dbo].[tProfession2] ADD CONSTRAINT [IX_tProfession2] UNIQUE NONCLUSTERED
(
[Profession] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
uj5u.com熱心網友回復：
據我所知，你ELSE不應該在那里。對于您擁有的值，您ELSE只會在P.AverageAnnualSalary為負值時回傳（不太可能，我假設您沒有為您作業的員工），NULL（然后回傳它毫無意義，不妨省略ELSE），或介于199999和200000（例如199999.7）或在99999和之間100000（例如99999.5）。
您實際上并不需要上限。例如，如果第一個WHEN不為真，則該值已經隱式小于 200000。然后ELSE完全洗掉，從而洗掉隱式轉換。
CASE WHEN P.AverageAnnualSalary >= 200000 THEN 'wow'
WHEN P.AverageAnnualSalary >= 100000 THEN 'nice'
WHEN P.AverageAnnualSalary >= 0 THEN 'ok'
END
uj5u.com熱心網友回復：
問題簡單明了，在 case 陳述句中，您將回傳 else 情況下的字符值 excpet。所以只需將 else 轉換為字串，它就會起作用
SELECT P.Profession AS Profession,
CASE
WHEN P.AverageAnnualSalary >= 200000
THEN 'wow'
WHEN P.AverageAnnualSalary >= 100000
AND P.AverageAnnualSalary <= 199999
THEN 'nice'
WHEN P.AverageAnnualSalary >= 0
AND P.AverageAnnualSalary <= 99999
THEN 'ok'
ELSE CAST(P.AverageAnnualSalary AS VARCHAR(50))
END AS Description
FROM tProfession2 AS P
ORDER BY Profession ASC
uj5u.com熱心網友回復：
CASE運算式的結果是單一的特定資料型別。SQL Server 回傳“result_expressions 中型別集中的最高優先級型別”
使用資料型別優先順序，您可以將“ok”和“nice”轉換為money
為了解決這個
ELSE CAST( P.AverageAnnualSalary AS VARCHAR( 30 ))
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/381378.html
標籤：sql sql-server