Tsql联合查询

2021-09-14 00:00:00 sql union window-functions tsql sql-server

我正在寻找一种查询表的有效方法.表结构为:

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]

查询应该为每个 caseID 和 userid(userid 可以是 SystemUserCreatedBy 或 SystemUserAssignee)位列返回，显示使用是 Createdby 还是 Assignee我设法这样写:

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.

请看数据示例:

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

根据最新的评论，两列都可以有数据，而且可能是同一个用户，所以你的原始查询是可以的(即使它扫描了两次表)，你唯一需要改变的是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 的答案应该是最好的.

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

相关文章