sql - 是否可以在SQL中使用if/then/when这样的条件语句？

Question

我有一个包含 id 字段的 SQL 查询 - 将其视为外键。我需要根据此 id 字段的值做出如下决定:

• 如果该值小于 3100，则从表 B 运行嵌套提取。
• 如果值大于 3100，则从表 C 运行嵌套提取。

声明看起来像这样:

Select a.ID, a.SN, a.User_Ident,
    (select b.first_name from b where b.ident = a.User_Ident) as 'First Name',
    (select b.last_name from b where b.ident = a.User_Ident) as 'Last Name',
from a
where ...

我想完成的是这样的:

Select a.ID, a.SN, a.User_Ident,
   when a.User_Ident > 3100 then
       (select b.first_name from b where b.ident = a.User_Ident) as 'First Name',
       (select b.last_name from b where b.ident = a.User_Ident) as 'Last Name'
   else 
       (select c.name from c where c.ident = a.User_Ident) as 'Name'
from a
where ....

这可能吗？

更新:您的回答建议我使用左连接。我的查询已经包含几个左外连接，所以我不知道这将如何工作。这是完整的查询:

select    
A.Ident,
A.Serial_Number,
A.Category_Ident,
C.Description as Category,
A.Purchase_Order,
A.Manufacturer_Ident,
M.Description as Manufacturer,    
A.Hardware_Model,
A.Processor_Quantity,
A.Processor_Speed_Hertz,
A.Memory_Installed_Bytes,
A.Memory_Maximum_Bytes,
A.Memory_Slots_Used,
A.Memory_Slots_Total,
A.Storage_Capacity_Bytes,
A.Video_Memory_Bytes,
A.Screen_Size_Diagonal_Inches,    
A.Software_Ident,
S.Software_Title,    
A.Account_Ident,
T.Description as Account,

A.User_Ident,
(select Q.dbo.P.user_name from Q.dbo.P where Q.dbo.P.ident = A.User_Ident) as 'User Name',
(select Q.dbo.P.first_name from Q.dbo.P where Q.dbo.P.ident = A.User_Ident) as 'First Name',
(select Q.dbo.P.last_name from Q.dbo.P where Q.dbo.P.ident = A.User_Ident) as 'Last Name',
(select Q.dbo.R.description from Q.dbo.R where Q.dbo.R.ident =  (select Q.dbo.P.rank from Q.dbo.P where Q.dbo.P.ident = A.User_Ident)) as 'Rank',
(select Q.dbo.P.phone from Q.dbo.P where Q.dbo.P.ident = A.User_Ident) as 'Phone',
(select Q.dbo.P.smtp_address from Q.dbo.P where Q.dbo.P.ident = A.User_Ident) as 'Email',
(select Q.dbo.O.description from Q.dbo.O where Q.dbo.O.ident =  (select Q.dbo.P.organization_ident from Q.dbo.P where Q.dbo.P.ident = A.User_Ident)) as 'Organization',
(select Q.dbo.L.description from Q.dbo.L where Q.dbo.L.ident =  (select Q.dbo.P.location_ident from Q.dbo.P where Q.dbo.P.ident = A.User_Ident)) as 'Location',

A.Disposition_Ident,
D.Description as Disposition,
A.Notes,
A.Updated,
A.UpdatedBy,
A.Label,
A.Scanned,
S.Licensed

FROM Assets 

left outer join C on A.Category_Ident = C.Ident
left outer join M on A.Manufacturer_Ident = M.Ident
left outer join S on A.Software_Ident = S.Ident
left outer join T on A.Account_Ident = T.Ident
left outer join D on A.Disposition_Ident = D.Ident

WHERE ((T.Description like '%' +  @Account + '%') or (A.Account_Ident like '%' +  @Account + '%'))

order by Serial_Number

Answer 1

很多给猫剥皮的方法，但我认为这种方法值得一试，使用 UNION 组合 2 个不同条件的结果(1 个查询连接到 b 用于 ids > 3100，另一个查询连接到 c 用于ID <= 3100).

您必须返回相同的字段(您不能按照您的意愿返回)，当您为 b 条件返回 2 个字段时，在查看 c 时返回 1 个“名称”字段。因此，在此示例中，当您加入 c 时，它返回“name”作为 First Name，并返回一个空白的 Last Name 值。

Select a.ID, a.SN, a.User_Ident, b.first_name AS 'First Name', b.last_name AS 'Last Name'
FROM a
    JOIN b ON a.User_Ident = b.ident
WHERE (a.User_Ident > 3100)
   AND (......)
UNION ALL
Select a.ID, a.SN, a.User_Ident, c.name AS 'First Name', '' AS 'Last Name'
FROM a
    JOIN c ON a.User_Ident = c.ident
WHERE (a.User_Ident <= 3100)
   AND (......)