这个 SQL 怎么写

[yzf86211861]

T_REPORT_CATALOG 表 字段 id，name，isReport，enabled
16777216 人事部 False True
16842753 员工手册 True True
16842754 用工制度 True True
50397185 报告制度 True True
50397186 报告制度1 True True
T_REPORT_CATALOG2 表 字段 Report_Type_Id(关联商标ID)，RightType，userId
16842753 1 00000001
16842753 2 00000001
16842754 1 00000001
16842754 2 00000001
50397185 2 00000001

想得到 这个结果
第一列 显示 isReport=false,第二列， RightType 如果等于1就写是1，不是就为null,第三列， RightType 如果等于2就写是2，不是就为null,第四列userId。

员工手册 1 2 00000001
用工制度 1 2 00000001
报告制度 null 2 00000001
报告制度1 null null null

[zc_0101]

发到sql server版，可以更快的解决

[ws_hgo]

等下

[ws_hgo]

isReport=false,第二列，
应该是true吧

[kao666]

select 'false' as isReport,case when RightType='1' then '1' else 'null' end , .......from

[takako_mu]

create table #temp1 (id int,name nvarchar(20),isReport nvarchar(10),enabled nvarchar(10))
insert into #temp1
select 16777216,'人事部','False','True' union all
select 16842753,'員工手冊','True','True' union all
select 16842754,'用工制度','True','True' union all
select 50397185,'報告制度','True','True' union all
select 50397186,'報告制度1','True','True' 
go
create table #temp2 (Report_Type_Id int,RightType int,userId nvarchar(10))
insert into #temp2
select 16842753,1,'00000001' union all
select 16842753,2,'00000001' union all
select 16842754,1,'00000001' union all
select 16842754,2,'00000001' union all
select 50397185,2,'00000001' 
go


select A.name,min(case when B.RightType=1 then 1 else null end) as RightType,
	min(case when B.RightType=2 then 2 else null end) as RightType2,B.userId
from 
(select * from #temp1 where isReport='True')A left join #temp2 B
on A.id=B.Report_Type_Id
group by A.name,B.userID


name                 RightType   RightType2  userId
-------------------- ----------- ----------- ----------
報告制度1                NULL        NULL        NULL
用工制度                 1           2           00000001
員工手冊                 1           2           00000001
報告制度                 NULL        2           00000001
警告: 彙總或其他 SET 作業已刪除 Null 值。

(4 個資料列受到影響)

[ws_hgo]

create table T_REPORT_CATALOG
(
   id int,
   name varchar(20),
   isReport bit,
   enabled int
)
insert into T_REPORT_CATALOG select 16777216,'人事部',0,1
insert into T_REPORT_CATALOG select 16842753,'员工手册',1,1
insert into T_REPORT_CATALOG select 16842754,'用工制度',1,1
insert into T_REPORT_CATALOG select 50397185,'报告制度',1,1
insert into T_REPORT_CATALOG select 50397186,'报告制度1',1,1

create table T_REPORT_CATALOG2
(
  Report_Type_Id int,
  RightType int,
  userId varchar(30)
)
insert into T_REPORT_CATALOG2 select '16842753',1,'00000001'
insert into T_REPORT_CATALOG2 select '16842753',2,'00000001'
insert into T_REPORT_CATALOG2 select '16842754',1,'00000001'
insert into T_REPORT_CATALOG2 select '16842754',2,'00000001'
insert into T_REPORT_CATALOG2 select '50397185',2,'00000001'

select T.name,
       (case when R.RightType=1 then 1 else null end) RightType,
       (case when R.RightType=2 then 2 else null end) RightType2,
       R.userId
from T_REPORT_CATALOG2 R
left join
(
 select name,id from T_REPORT_CATALOG where isReport=1
) T
on T.id=R.Report_Type_Id

[ws_hgo]

create table T_REPORT_CATALOG
(
   id int,
   name varchar(20),
   isReport bit,
   enabled int
)
insert into T_REPORT_CATALOG select 16777216,'人事部',0,1
insert into T_REPORT_CATALOG select 16842753,'员工手册',1,1
insert into T_REPORT_CATALOG select 16842754,'用工制度',1,1
insert into T_REPORT_CATALOG select 50397185,'报告制度',1,1
insert into T_REPORT_CATALOG select 50397186,'报告制度1',1,1

create table T_REPORT_CATALOG2
(
  Report_Type_Id int,
  RightType int,
  userId varchar(30)
)
insert into T_REPORT_CATALOG2 select '16842753',1,'00000001'
insert into T_REPORT_CATALOG2 select '16842753',2,'00000001'
insert into T_REPORT_CATALOG2 select '16842754',1,'00000001'
insert into T_REPORT_CATALOG2 select '16842754',2,'00000001'
insert into T_REPORT_CATALOG2 select '50397185',2,'00000001'

select T.name,
       max(case when R.RightType=1 then 1 else null end) RightType,
       max(case when R.RightType=2 then 2 else null end) RightType2,
       R.userId
from 
(
 select name,id from T_REPORT_CATALOG where isReport=1
)T
left join
T_REPORT_CATALOG2 R
on T.id=R.Report_Type_Id
group by T.name,R.userId


name                 RightType   RightType2  userId
-------------------- ----------- ----------- ------------------------------
报告制度1                NULL        NULL        NULL
报告制度                 NULL        2           00000001
用工制度                 1           2           00000001
员工手册                 1           2           00000001

[gongsun]

又见课程表

[messi_yang]

不錯 。來學習哈

[yzf86211861]

select B.id,B.name,A.RightType As upload,C.RightType As upRead,A.userId As uploadUserID,C.userId As upReaduserId
from T_REPORT_CATALOG B
left join T_REPORT_CATALOG2 A on A.Report_Type_ID=B.id and A.RightType='1' and A.userid='00000001'
left join T_REPORT_CATALOG2 C on C.Report_Type_ID=B.id and C.RightType='2' and C.userid='00000001'
where B.Isreport=1