合并两个 SQL 脚本，在 Select 查询中使用 if 条件 [英] Merge two SQL script use if condition in Select query

问题描述

我有 2 个脚本如下:

I have 2 script as following:

第一个:

SELECT [Fm].[Id], [Sdp].[FirstName], [Sdp].[LastName], [Sdp].[SSN],
[Sdp].[StoreName], [Sdp].[PostalCode], [Fc].[Id], [Sdp].[Address]
FROM [SRM].[SiteMembers].[DProfile] AS [Sdp]
INNER JOIN [SRM].[SiteMembers].[Member] AS [Sm]
ON [Sdp].[Member_Id] = [Sm].[Id]
INNER JOIN [FRM].[Members].[Member] AS [Fm]
ON [Sm].[UserId] = [Fm].[UserId]
INNER JOIN [SRM].[General].[City] AS [Sc]
ON [Sdp].[City_Id]=[Sc].[Id]
INNER JOIN [FRM].[General].[City] AS [Fc]
ON [Fc].[Title]=[Sc].[Title] 
COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE ISNUMERIC([Sdp].[PostalCode])=1;

第二个:

SELECT [Fm].[Id], [Sdp].[FirstName], [Sdp].[LastName], [Sdp].[SSN],
[Sdp].[StoreName], 0, [Fc].[Id], [Sdp].[Address]
FROM [SRM].[SiteMembers].[DProfile] AS [Sdp]
INNER JOIN [SRM].[SiteMembers].[Member] AS [Sm]
ON [Sdp].[Member_Id] = [Sm].[Id]
INNER JOIN [FRM].[Members].[Member] AS [Fm]
ON [Sm].[UserId] = [Fm].[UserId]
INNER JOIN [SRM].[General].[City] AS [Sc]
ON [Sdp].[City_Id]=[Sc].[Id]
INNER JOIN [FRM].[General].[City] AS [Fc]
ON [Fc].[Title]=[Sc].[Title] 
COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE ISNUMERIC([Sdp].[PostalCode])=0;

这 2 个脚本之间的区别在于，第一个选择带有数字邮政编码的列，第二个选择 0 作为非数字邮政编码，所以我如何将这 2 个脚本合并到一个脚本中，我不是在谈论 Union，我很感兴趣在选择查询中使用一些条件非数字邮政编码选择 0.有人知道吗?

The difference between this 2 script is that the first one select columns with numeric postal code and second one select 0 for the non numeric postal code so how can I merge this 2 script together in one script, I am not talking about Union, I am interesting to use some condition in select query for non numeric postal code select 0. Does any one have any idea?

推荐答案

在所选列列表中使用 case 语句并删除 where 子句:

Use a case statement in the selected column list and remove the where clause:

SELECT [Fm].[Id], [Sdp].[FirstName], [Sdp].[LastName], [Sdp].[SSN],
[Sdp].[StoreName],
case when isnumeric([Sdp].[PostalCode]) = 1 then [Sdp].[PostalCode] else 0 end,
[Fc].[Id], [Sdp].[Address]
FROM [SRM].[SiteMembers].[DProfile] AS [Sdp]
INNER JOIN [SRM].[SiteMembers].[Member] AS [Sm]
ON [Sdp].[Member_Id] = [Sm].[Id]
INNER JOIN [FRM].[Members].[Member] AS [Fm]
ON [Sm].[UserId] = [Fm].[UserId]
INNER JOIN [SRM].[General].[City] AS [Sc]
ON [Sdp].[City_Id]=[Sc].[Id]
INNER JOIN [FRM].[General].[City] AS [Fc]
ON [Fc].[Title]=[Sc].[Title] 
COLLATE SQL_Latin1_General_CP1_CI_AS

这篇关于合并两个 SQL 脚本，在 Select 查询中使用 if 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！