問題描述

我正在尋找一種查詢表的有效方法.表結(jié)構(gòu)為:

I’m looking for an efficient way to query a table. The table structure is:

CREATE TABLE [dbo].[CaseManager](
    [CaseID] [int] IDENTITY(1,1) NOT NULL,
    [SystemUserCreatedBy] [int] NULL,
    [SystemUserAssignee] [int] NULL,
     CONSTRAINT [PK_Case] PRIMARY KEY CLUSTERED 
(
    [CaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] 

查詢應(yīng)該為每個 caseID 和 userid(userid 可以是 SystemUserCreatedBy 或 SystemUserAssignee)位列返回，顯示使用是 Createdby 還是 Assignee我設(shè)法這樣寫:

The query should return for every caseID and userid (userid can be either SystemUserCreatedBy or SystemUserAssignee) bit columns that show if the use is Createdby or Assignee I managed to write it like this:

select  CaseID,UserID,
       max(CaseUser.IsAssignee) as IsAssignee,
       max(CaseUser.IsCreator) as IsCreator


  FROM
  (
  select CMassignee.CaseID,
         CMassignee.SystemUserAssignee as UserID,
         1 as IsAssignee ,
         0 as IsCreator 
      from CaseManager CMassignee 
  where  CMassignee.SystemUserAssignee is not null
  UNION
   select CMCreator.CaseID,
          CMCreator.SystemUserCreatedBy as UserID,
          0 as IsAssignee ,
          1 as IsCreator 
      from CaseManager CMCreator
  where  CMCreator.SystemUserCreatedBy is not null
    ) CaseUser
  group by CaseID,UserID

我很確定有一種更好的方法來編寫一次掃描該表.在那個例子中，我只顯示了兩列(SystemUserCreatedBy as SystemUserAssignee)，但實際上我有五列需要添加.

I’m pretty sure there is a better way to write it with scanning that table once .In that example I show only two columns (SystemUserCreatedBy as SystemUserAssignee) but actually I have five that need to be added.

請看數(shù)據(jù)示例:

SET IDENTITY_INSERT dbo.casemanager ON;
insert into casemanager(caseid,SystemUserCreatedBy,SystemUserAssignee)
values
(1,2222,3333)
SET IDENTITY_INSERT dbo.casemanager OFF;

在那種情況下，我希望得到:

In that case I’m looking to get :

CaseID      UserID      IsAssignee  IsCreator
----------- ----------- ----------- -----------
1           2222        0           1
1           3333        1           0

(2 row(s) affected)

推薦答案

CaseID 是主鍵，因此不需要聚合(如果兩列中只有一列不是 NULL):

The CaseID is the Primary Key, so there's no need for aggregation (if only one of both columns is NOT NULL):

SELECT
   CaseID,
   COALESCE(SystemUserAssignee, SystemUserCreatedBy) AS UserID,
   CASE WHEN SystemUserAssignee IS NOT NULL THEN 1 ELSE 0 end AS IsAssignee,
   CASE WHEN SystemUserCreatedBy IS NOT NULL THEN 1 ELSE 0 end AS IsCreator
FROM CaseManager CMassignee 

根據(jù)最新的評論，兩列都可以有數(shù)據(jù)，而且可能是同一個用戶，所以你的原始查詢是可以的(即使它掃描了兩次表)，你唯一需要改變的是UNION ALL 而不是 UNION.

Based on the latest comments both columns can have data and it might be the same user, so your original query is ok (even if it scans the table twice), the only thing you need to change is UNION ALL instead of UNION.

但是對于 5 個用戶，@Amit 的答案應(yīng)該是最好的.

But for 5 userid @Amit's answer should be the best.

這篇關(guān)于Tsql聯(lián)合查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！