SQL 如何编写一个 select inside case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25767993/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write a select inside case statement
提问by user3929962
I have a stored procedure that contains a case statement inside a select statement.
我有一个存储过程，它在 select 语句中包含一个 case 语句。
select Invoice_ID, 'Unknown' as Invoice_Status,
case when Invoice_Printed is null then '' else 'Y' end as Invoice_Printed,
case when Invoice_DeliveryDate is null then '' else 'Y' end as Invoice_Delivered,
case when Invoice_DeliveryType <> 'USPS' then '' else 'Y' end as Invoice_eDeliver,
Invoice_ContactLName+', '+Invoice_ContactFName as ContactName,
from dbo.Invoice
left outer join dbo.fnInvoiceCurrentStatus() on Invoice_ID=CUST_InvoiceID
where CUST_StatusID= 7
order by Inv_Created
At line case when Invoice_DeliveryType <> 'USPS' then '' else 'Y' end as Invoice_eDeliver
在线 case when Invoice_DeliveryType <> 'USPS' then '' else 'Y' end as Invoice_eDeliver
I need to check for a valid email address (if email is valid, display Y, else display N).
我需要检查有效的电子邮件地址（如果电子邮件有效，则显示 Y，否则显示 N）。
So the line would read:
所以这行会写成：
if Invoice_DeliveryType <> 'USPS' then '' else ( If ISNULL(Select emailaddr from dbo.Client Where Client_ID = SUBSTRING(Invoice_ID, 1, 6)), 'Y', 'N')
if Invoice_DeliveryType <> 'USPS' then '' else ( If ISNULL(Select emailaddr from dbo.Client Where Client_ID = SUBSTRING(Invoice_ID, 1, 6)), 'Y', 'N')
How can I write out this query?
我怎样才能写出这个查询？
采纳答案by Gordon Linoff
You can do this with a case. I think the following is the logic you want:
您可以使用case. 我认为以下是您想要的逻辑：
(case when Invoice_DeliveryType <> 'USPS' then ''
when exists (Select 1
from dbo.Client c
Where c.Client_ID = SUBSTRING(i.Invoice_ID, 1, 6) and
c.emailaddr is not null
)
then 'Y'
else 'N'
end)
