MS SQL Server
  1. MS SQL Server
  3. Meilleur moyen de vérifier l'état au lieu de cas répété
Intereting Posts
Requêtes SQL dans l'instruction Case Erreur de requête SQL empilée et complications Sélectionnez Count où les valeurs supérieures à 0 SQL Server La transaction nestede ne peut pas être annulée Conversion de données varBinary SQL Server en string C # SQLServer obtient la première ligne de la sous-requête Requête SQL multi-tables renvoyant des résultats inattendus Vider les valeurs de colonne mais conserver des valeurs égales au nombre fourni à l'aide de SQL Query Comment get le nième set de deux lignes qui ont le même ID Quelle est la pénalité liée aux performances du type de données XML dans SQL Server par rapport à NVARCHAR (MAX)? Niveaux d'isolation pris en charge par la notification Sql pour les transactions Récupérer une ligne random dans une fonction définie par l'user? Pouvez-vous choisir une valeur de colonne dans une table returnnée par une fonction? Utilisation de la clause HAVING pour identifier les groupes avec une combinaison d'loggings définie Pivot SQL non agrégé

Meilleur moyen de vérifier l'état au lieu de cas répété

Voici mes tables: 

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

J'ai donc besoin de créer une vue complète de tous mes membres et des colonnes associées, je vais commencer par la requête select suivante: 

 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

Comme vous le voyez, Case When est répété pour find le type de membre et décider de la valeur de sélection de quelle table. Est-ce une bonne idée de créer une vue telle que ma vue? Y a-t-il une meilleure façon de vérifier le type de membre? Je dois mentionner que la colonne Id dans la table MemberType peut être modifiée, donc je ne veux pas utiliser quelque chose comme ceci: WHEN [ME].[MemberType_Id] = 1 Then '' Quelle est votre suggestion?

Je crois que ceci vous aidera 

 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

Si vous prétendez que Manager, Customer et Dealer sont dans la même table, les choses deviennent plus simples: 

 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

Cela suggère clairement que tous vos revendeurs, gestionnaires et clients appartiennent à la même table, ou peut-être table par type avec table commune pour datatables communes. MemberTypeId passerait des membres à la nouvelle table en tant que discriminateur.

Et ça: 

 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]

Vous pouvez également CROSS JOIN avec les types. Vous pourrez toujours comparer les typid de type et les déclarations de case seront beaucoup plus faciles à lire. Comme ça: 

 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