检查条件的更好方法，而不是重复 Case When [英] Better way to check condition instead of repeated Case When

问题描述

这是我的表格:

[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] }

所以我需要创建我所有成员和相关列的完整视图，我将从以下选择查询开始:

So I need to create a full view of all my members and related columns, I am going to start with the following select query:

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

如您所见，有重复的案例何时查找成员类型并决定从哪个表中选择值.创建像我的视图这样的视图是个好主意吗?有没有更好的方法来检查会员类型?我应该提到 MemberType 表中的 Id 列可以更改，所以我不想使用这样的东西:WHEN [ME].[MemberType_Id] = 1 Then ''你有什么建议?

As you see there is repeated Case When to find the Member Type and decide select value from which table. Is this good idea to create a view such as my view? Is there any better way to check the Member Type? I should mention that the Id column in MemberType table can be change, so I don't want use something like this: WHEN [ME].[MemberType_Id] = 1 Then '' what is your suggestion?

推荐答案

如果你假装经理、客户和经销商在同一张桌子上，事情就会变得更简单:

If you pretend that Manager, Customer and Dealer are in the same table things become simpler:

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 将作为鉴别器从成员移动到新表.

This clearly suggests that all your dealers, managers and customers belong to same table, or perhaps table-per-type with common table for common data. MemberTypeId would than move from Members to new table as a discriminator.

这篇关于检查条件的更好方法，而不是重复 Case When的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！