这是我的表：
[Member]:
{ [Id], [UserId], [UserName], [Email], [Status_Id], [MemberType_Id] }
[CustomerProfile] :
{ [Id], [Member_Id], [FirstName], [LastName], [SSN], [Gender], [PostalCode],[City_Id], [Address]}
[DealerProfile]:
{ [Id], [Member_Id], [FirstName], [LastName], [SSN], [Gender], [StoreName], [PostalCode], [City_Id], [Address] }
[ManagerProfile]
{ [Id], [Member_Id], [FirstName], [LastName], [SSN], [Gender] }
[City]:
{ [Id], [Title], [Province_Id] }
所以，我需要创建我的所有成员和相关列的众目睽睽之下，我将开始与以下选择查询：
SELECT
[ME].[Id] AS [MemberId],
[ME].[UserId],
[ME].[UserName],
[ME].[Email],
[ME].[Status_Id],
[ST].[Title] AS [Status],
[ME].[MemberType_Id],
[MT].[Title] AS [MemberType],
CASE
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[Id]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[Id]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN [MAP].[Id]
ELSE 0 END AS [Profile_Id],
CASE
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[FirstName]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[FirstName]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN [MAP].[FirstName]
ELSE 'Unknown' END AS [FirstName],
CASE
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[LastName]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[LastName]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN [MAP].[LastName]
ELSE 'Unknown' END AS [LastName],
CASE
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[SSN]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[SSN]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN [MAP].[SSN]
ELSE 'Unknown' END AS [SSN],
CASE
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[Address]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[Address]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN ''
ELSE 'Unknown' END AS [Address],
CASE
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[PostalCode]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[PostalCode]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN ''
ELSE 'Unknown' END AS [PostalCode],
CASE
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[Gender]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[Gender]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN [MAP].[Gender]
ELSE 'Unknown' END AS [Gender],
CASE
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer') THEN [CUP].[City_Id]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer') THEN [DEP].[City_Id]
WHEN [ME].[MemberType_Id] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager') THEN 0
ELSE 0 END AS [CityId]
FROM [Members].[Member] AS [ME]
INNER JOIN [Members].[MemberType] AS [MT] ON [ME].[MemberType_Id] = [MT].[Id]
INNER JOIN [Members].[Status] AS [ST] ON [ME].[Status_Id]=[ST].[Id]
INNER JOIN [Members].[ManagerProfile] AS [MAP] ON [ME].[Id]= [MAP].[Member_Id]
INNER JOIN [Members].[CustomerProfile] AS [CUP] ON [ME].[Id]=[CUP].[Member_Id]
INNER JOIN [Members].[DealerProfile] AS [DEP] ON [ME].[Id]=[DEP].[Member_Id];
GO
正如你看到有反复盒时，发现会员类型，并决定从表中选择值。 这是好主意，以创建一个视图，如我的看法？ 有没有更好的办法来检查会员类型？ 我要指出的是，在Id列MemberType表可以变化，所以我不希望用这样的： WHEN [ME].[MemberType_Id] = 1 Then ''你有什么建议吗？
Answer 1:
我认为这会帮助你
SELECT
[ME].[Id] AS [MemberId],
[ME].[UserId],
[ME].[UserName],
[ME].[Email],
[ME].[Status_Id],
[ST].[Title] AS [Status],
[ME].[MemberType_Id],
[MT].[Title] AS [MemberType],
[X].[Id] AS [Profile_Id],
[X].[FirstName],
[X].[LastName],
[X].[SSN],
[X].[Address],
[X].[PostalCode],
[X].[Gender],
[X].[City_Id]
FROM [Members].[Member] AS [ME]
INNER JOIN [Members].[MemberType] AS [MT] ON [ME].[MemberType_Id] = [MT].[Id]
INNER JOIN [Members].[Status] AS [ST] ON [ME].[Status_Id]=[ST].[Id]
INNER JOIN
(Select [Member_Id], [Id], [FirstName], [LastName], [SSN], [Address]='', [PostalCode]='', [Gender], [City_Id]=0,
[TYPE] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Customer')
From [Members].[ManagerProfile] AS [MAP] Where [MAP].[Member_Id] = [ME].[Id]
Union All
Select [Member_Id], [Id], [FirstName], [LastName], [SSN], [Address], [PostalCode], [Gender], [City_Id],
[TYPE] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Manager')
From [Members].[CustomerProfile] AS [CUP] Where [CUP].[Member_Id] = [ME].[Id]
Union All
Select [Member_Id], [Id], [FirstName], [LastName], [SSN], [Address], [PostalCode], [Gender], [City_Id],
[TYPE] = (SELECT TOP(1) [TMT].[Id] FROM [Members].[MemberType] AS [TMT] WHERE [TMT].[Title]='Dealer')
From [Members].[DealerProfile] AS [DEP] Where [DEP].[Member_Id] = [ME].[Id])
AS [X] On [ME].[Id] = [X].[Member_Id] AND [ME].[MemberType_Id] = [X].[TYPE]
GO
Answer 2:
如果你假装经理，客户和经销商都在同一桌上的东西变得更简单：
SELECT
[ME].[Id] AS [MemberId],
[ME].[UserId],
[ME].[UserName],
[ME].[Email],
[ME].[Status_Id],
[ST].[Title] AS [Status],
[ME].[MemberType_Id],
[MT].[Title] AS [MemberType],
a.ID,
a.FirstName,
a.LastName,
a.SSN,
a.Address,
a.PostalCode,
a.Gender,
a.City_Id
FROM [Members].[Member] AS [ME]
INNER JOIN [Members].[MemberType] AS [MT] ON [ME].[MemberType_Id] = [MT].[Id]
INNER JOIN [Members].[Status] AS [ST] ON [ME].[Status_Id]=[ST].[Id]
INNER JOIN
(
select 'Manager' MemberType, Member_id, Firstname, LastName, SSN,
Address, PostalCode, Gender, City_id
from ManagerProfile
union all
select 'Customer', Member_id, Firstname, LastName, SSN,
Address, PostalCode, Gender, City_id
from CustomerProfile
union all
select 'Dealer', Member_id, Firstname, LastName, SSN,
'', '', Gender, 0
from DealerProfile
)
on me.id = a.member_id
and MT.TYPE = a.MemberType
这清楚地表明，所有的经销商，经理和客户都属于同一个表，或者表每类型与普通数据公用表。 MemberTypeId会比成员转移到新表作为鉴别。
Answer 3:
那这个呢：
CASE
WHEN [MT].[Title]='Customer' THEN [CUP].[FirstName]
WHEN [MT].[Title]='Dealer' THEN [DEP].[FirstName]
WHEN [MT].[Title]='Manager' THEN [MAP].[FirstName]
ELSE 'Unknown' END AS [FirstName]
Answer 4:
你也可以CROSS JOIN与类型。 你仍然会比较typid的和case说明会更容易阅读。 像这样：
SELECT
[ME].[Id] AS [MemberId],
[ME].[UserId],
[ME].[UserName],
[ME].[Email],
[ME].[Status_Id],
[ST].[Title] AS [Status],
[ME].[MemberType_Id],
[MT].[Title] AS [MemberType],
CASE
WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[Id]
WHEN [ME].[MemberType_Id] = tblTypes.DealerId THEN [DEP].[Id]
WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN [MAP].[Id]
ELSE 0 END AS [Profile_Id],
CASE
WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[FirstName]
WHEN [ME].[MemberType_Id] = (tblTypes.DealerId THEN [DEP].[FirstName]
WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN [MAP].[FirstName]
ELSE 'Unknown' END AS [FirstName],
CASE
WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[LastName]
WHEN [ME].[MemberType_Id] = tblTypes.DealerId THEN [DEP].[LastName]
WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN [MAP].[LastName]
ELSE 'Unknown' END AS [LastName],
CASE
WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[SSN]
WHEN [ME].[MemberType_Id] = tblTypes.DealerId THEN [DEP].[SSN]
WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN [MAP].[SSN]
ELSE 'Unknown' END AS [SSN],
CASE
WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[Address]
WHEN [ME].[MemberType_Id] = tblTypes.DealerId THEN [DEP].[Address]
WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN ''
ELSE 'Unknown' END AS [Address],
CASE
WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[PostalCode]
WHEN [ME].[MemberType_Id] = tblTypes.DealerId THEN [DEP].[PostalCode]
WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN ''
ELSE 'Unknown' END AS [PostalCode],
CASE
WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[Gender]
WHEN [ME].[MemberType_Id] = tblTypes.DealerId THEN [DEP].[Gender]
WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN [MAP].[Gender]
ELSE 'Unknown' END AS [Gender],
CASE
WHEN [ME].[MemberType_Id] = tblTypes.CustomerId THEN [CUP].[City_Id]
WHEN [ME].[MemberType_Id] = tblTypes.DealerId THEN [DEP].[City_Id]
WHEN [ME].[MemberType_Id] = tblTypes.ManagerId THEN 0
ELSE 0 END AS [CityId]
FROM [Members].[Member] AS [ME]
INNER JOIN [Members].[MemberType] AS [MT] ON [ME].[MemberType_Id] = [MT].[Id]
INNER JOIN [Members].[Status] AS [ST] ON [ME].[Status_Id]=[ST].[Id]
INNER JOIN [Members].[ManagerProfile] AS [MAP] ON [ME].[Id]= [MAP].[Member_Id]
INNER JOIN [Members].[CustomerProfile] AS [CUP] ON [ME].[Id]=[CUP].[Member_Id]
INNER JOIN [Members].[DealerProfile] AS [DEP] ON [ME].[Id]=[DEP].[Member_Id]
CROSS JOIN
(
SELECT
CustomerId,
DealerId,
ManagerId
FROM
(
SELECT 1 AS nbr
) AS tblType
LEFT JOIN
(
SELECT TOP(1)
[TMT].[Id] AS CustomerId
FROM
[Members].[MemberType] AS [TMT]
WHERE
[TMT].[Title]='Customer'
) AS Customer ON 1=1
LEFT JOIN
(
SELECT TOP(1)
[TMT].[Id] AS DealerId
FROM
[Members].[MemberType] AS [TMT]
WHERE
[TMT].[Title]='Dealer'
) AS Dealer ON 1=1
LEFT JOIN
(
SELECT TOP(1)
[TMT].[Id] AS ManagerId
FROM
[Members].[MemberType] AS [TMT]
WHERE
[TMT].[Title]='Manager'
) AS Manager ON 1=1
) AS tblTypes
文章来源: Better way to check condition instead of repeated Case When
