gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-04 18:28:36 24 4
gpt4 key购买 nike

我有一个包含 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

最佳答案

很多给猫剥皮的方法,但我认为这种方法值得一试,使用 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 (......)

关于sql - 是否可以在SQL中使用if/then/when这样的条件语句?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/2186530/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com