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
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
declare @T_REPORT_CATALOG table (id int,name varchar(9),isReport varchar(5),enabled varchar(4))
insert into @T_REPORT_CATALOG
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'
--> 测试数据: @T_REPORT_CATALOG2
declare @T_REPORT_CATALOG2 table (Report_Type_Id int,RightType int,userId varchar(8))
insert into @T_REPORT_CATALOG2
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'select
col1=name,
col2=case when RightType=1 then 1 else null end,
col3=case when RightType =2 then 2 else null end ,
userid
from @T_REPORT_CATALOG a,@T_REPORT_CATALOG2 b
where id=Report_Type_Id
and isReport='true'col1 col2 col3 userid
--------- ----------- ----------- --------
员工手册 1 NULL 00000001
员工手册 NULL 2 00000001
用工制度 1 NULL 00000001
用工制度 NULL 2 00000001
报告制度 NULL 2 00000001(5 行受影响)
第一列 显示 isReport=false看楼主应该是TRUE吧
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/24
-- Version: SQL SERVER 2005
-- =============================================
declare @TB1 table([id] int,name varchar(9),isReport varchar(5),enabled varchar(4))
insert @TB1
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'declare @TB2 table([Report_Type_Id] int,RightType int,userId varchar(8))
insert @TB2
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'
SELECT A.name, (CASE RightType WHEN 1 THEN 1 ELSE NULL END), (CASE RightType WHEN 2 THEN 2 ELSE NULL END) , userId
FROM @TB1 A LEFT JOIN @TB2 B ON A.ID = B.[Report_Type_Id]
WHERE isReport='true'
--测试结果:
/*
name userId
--------- ----------- ----------- --------
员工手册 1 NULL 00000001
员工手册 NULL 2 00000001
用工制度 1 NULL 00000001
用工制度 NULL 2 00000001
报告制度 NULL 2 00000001
报告制度1 NULL NULL NULL(6 row(s) affected)
*/
declare @T_REPORT_CATALOG table (id int,name varchar(9),isReport varchar(5),enabled varchar(4))
insert into @T_REPORT_CATALOG
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'
--> 测试数据: @T_REPORT_CATALOG2
declare @T_REPORT_CATALOG2 table (Report_Type_Id int,RightType int,userId varchar(8))
insert into @T_REPORT_CATALOG2
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'select
col1=name,
col2=max(case when RightType=1 then 1 else null end),
col3=max(case when RightType =2 then 2 else null end) ,
userid
from @T_REPORT_CATALOG a
left join @T_REPORT_CATALOG2 b
on id=Report_Type_Id
where isReport='true'
group by name,userid
col1 col2 col3 userid
--------- ----------- ----------- --------
报告制度1 NULL NULL NULL
报告制度 NULL 2 00000001
用工制度 1 2 00000001
员工手册 1 2 00000001
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)
Create table T_REPORT_CATALOG(id nvarchar(10),[name] nvarchar(10),isReport nvarchar(10),[enabled]nvarchar(10))
insert into T_REPORT_CATALOG 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' Create table T_REPORT_CATALOG2(Report_Type_Id nvarchar(10),RightType int ,userId nvarchar(10))
insert into T_REPORT_CATALOG2 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'
Select A.[name],B.RightType,C.RightType,IsNull(B.UserID,C.UserID) as UserID
From T_REPORT_CATALOG A
left join T_REPORT_CATALOG2 B On A.id=B.Report_Type_Id and B.RightType=1
left join T_REPORT_CATALOG2 C On A.id=C.Report_Type_Id and C.RightType=2
Where A.isReport='True'
/*name RightType RightType UserID
---------- ----------- ----------- ----------
员工手册 1 2 00000001
用工制度 1 2 00000001
报告制度 NULL 2 00000001
报告制度1 NULL NULL NULL(4 row(s) affected)*/
--> 测试数据:@T_REPORT_CATALOG
declare @T_REPORT_CATALOG table([id] int,[name] varchar(9),[isReport] varchar(5),[enabled] varchar(4))
insert @T_REPORT_CATALOG
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'
--> 测试数据:@T_REPORT_CATALOG2
declare @T_REPORT_CATALOG2 table([Report_Type_Id] int,[RightType] int,[userId] varchar(8))
insert @T_REPORT_CATALOG2
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'select a.name,
max(case b.RightType when 1 then 1 else null end) as RightType1,
max(case b.RightType when 2 then 2 else null end) as RightType2,
b.userId
from @T_REPORT_CATALOG a left join @T_REPORT_CATALOG2 b
on a.id = b.Report_Type_Id
where a.isReport = 'true'
group by a.name,a.isReport,b.userId
--结果
------------------------------------
报告制度1 NULL NULL NULL
报告制度 NULL 2 00000001
用工制度 1 2 00000001
员工手册 1 2 00000001
这样应该对了
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/24
-- Version: SQL SERVER 2005
-- =============================================
declare @TB1 table([id] int,name varchar(9),isReport varchar(5),enabled varchar(4))
insert @TB1
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'declare @TB2 table([Report_Type_Id] int,RightType int,userId varchar(8))
insert @TB2
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'
SELECT T.NAME,MAX(Col1) as Col1,MAX(Col2) as Col2, MAX(userId) AS userId
FROM (
SELECT A.name, (CASE RightType WHEN 1 THEN 1 ELSE NULL END) Col1, (CASE RightType WHEN 2 THEN 2 ELSE NULL END) Col2, userId
FROM @TB1 A LEFT JOIN @TB2 B ON A.ID = B.[Report_Type_Id]
WHERE isReport='true'
) T
GROUP BY T.NAME
ORDER BY Col1 DESC, Col2 DESC
--测试结果:
/*
NAME Col1 Col2 userId
--------- ----------- ----------- --------
用工制度 1 2 00000001
员工手册 1 2 00000001
报告制度 NULL 2 00000001
报告制度1 NULL NULL NULL
Warning: Null value is eliminated by an aggregate or other SET operation.(4 row(s) affected)*/
--> Author : wufeng4552
--> Date : 2009-11-24 13:59:44
declare @T_REPORT_CATALOG table (id int,name nvarchar(10),isReport nvarchar(10),enabled nvarchar(8))
insert into @T_REPORT_CATALOG
select 16777216,N'人事部','False','True' union all
select 16842753,N'员工手册','True','True' union all
select 16842754,N'用工制度','True','True' union all
select 50397185,N'报告制度','True','True' union all
select 50397186,N'报告制度1','True','True'
declare @T_REPORT_CATALOG2 table (Report_Type_Id int,RightType int,userId nvarchar(16))
insert into @T_REPORT_CATALOG2
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'
select
a.name,
b.RightType,
c.RightType,
b.userId
from @T_REPORT_CATALOG a
left join @T_REPORT_CATALOG2 b on a.id=b.Report_Type_Id and b.RightType=1
left join @T_REPORT_CATALOG2 c on a.id=c.Report_Type_Id and c.RightType=2
where a.isReport='True'
/*
name RightType RightType userId
---------- ----------- ----------- ----------------
员工手册 1 2 00000001
用工制度 1 2 00000001
报告制度 NULL 2 NULL
报告制度1 NULL NULL NULL(4 個資料列受到影響)
*/
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[name] varchar(9),[isReport] varchar(5),[enabled] varchar(4))
insert [ta]
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'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Report_Type_Id] int,[RightType] int,[userId] varchar(8))
insert [tb]
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'
---查询---
select
distinct
ta.id,
ta.name,
case when exists(select 1 from tb t where t.Report_Type_Id=ta.id and t.RightType=1) then 1 end,
case when exists(select 1 from tb t where t.Report_Type_Id=ta.id and t.RightType=2) then 2 end,
tb.userId
from ta
left join tb on ta.id=tb.Report_Type_Id
where ta.isReport='true'
order by ta.id
---结果---
id name userId
----------- --------- ----------- ----------- --------
16842753 员工手册 1 2 00000001
16842754 用工制度 1 2 00000001
50397185 报告制度 NULL 2 00000001
50397186 报告制度1 NULL NULL NULL(所影响的行数为 4 行)
-- Author: T.O.P
-- Create date: 2009/11/24
-- Version: SQL SERVER 2005
-- =============================================
declare @TB1 table([id] int,name varchar(9),isReport varchar(5),enabled varchar(4))
insert @TB1
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'declare @TB2 table([Report_Type_Id] int,RightType int,userId varchar(8))
insert @TB2
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'SELECT A.name, MAX(CASE RightType WHEN 1 THEN 1 ELSE NULL END) Col1, MAX(CASE RightType WHEN 2 THEN 2 ELSE NULL END) Col2, MAX(userId)
FROM @TB1 A LEFT JOIN @TB2 B ON A.ID = B.[Report_Type_Id]
WHERE isReport='true'
GROUP BY A.name--测试结果:
/*
name Col1 Col2
--------- ----------- ----------- --------
报告制度 NULL 2 00000001
报告制度1 NULL NULL NULL
用工制度 1 2 00000001
员工手册 1 2 00000001(4 row(s) affected)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-24 14:09:25
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[T_REPORT_CATALOG]
if object_id('[T_REPORT_CATALOG]') is not null drop table [T_REPORT_CATALOG]
go
create table [T_REPORT_CATALOG]([id] int,[name] varchar(9),[isReport] varchar(5),[enabled] varchar(4))
insert [T_REPORT_CATALOG]
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'
--> 测试数据:[T_REPORT_CATALOG2]
if object_id('[T_REPORT_CATALOG2]') is not null drop table [T_REPORT_CATALOG2]
go
create table [T_REPORT_CATALOG2]([Report_Type_Id] int,[RightType] int,[userId] varchar(8))
insert [T_REPORT_CATALOG2]
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'
--------------开始查询--------------------------
select
a.name,
max(case b.RightType when 1 then 1 else null end) as RightType1,
max(case b.RightType when 2 then 2 else null end) as RightType2,
b.userId
from
T_REPORT_CATALOG a
left join T_REPORT_CATALOG2 b on
a.id = b.Report_Type_Id
where
a.isReport = 'true'
group by
a.name,a.isReport,b.userId
----------------结果----------------------------
/* name RightType1 RightType2 userId
--------- ----------- ----------- --------
报告制度1 NULL NULL NULL
报告制度 NULL 2 00000001
用工制度 1 2 00000001
员工手册 1 2 00000001
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)
*/
(
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,1create 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
16842753 员工手册 1 2 00000001
16842754 用工制度 1 2 00000001
50397185 报告制度 null 2 00000001
50397186 报告制度1 null null null
谁再帮个忙谢谢。
--> Author : wufeng4552
--> Date : 2009-11-24 13:59:44
declare @T_REPORT_CATALOG table (id int,name nvarchar(10),isReport nvarchar(10),enabled nvarchar(8))
insert into @T_REPORT_CATALOG
select 16777216,N'人事部','False','True' union all
select 16842753,N'员工手册','True','True' union all
select 16842754,N'用工制度','True','True' union all
select 50397185,N'报告制度','True','True' union all
select 50397186,N'报告制度1','True','True'
declare @T_REPORT_CATALOG2 table (Report_Type_Id int,RightType int,userId nvarchar(16))
insert into @T_REPORT_CATALOG2
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'
select
a.id,
a.name,
b.RightType,
c.RightType,
b.userId
from @T_REPORT_CATALOG a
left join @T_REPORT_CATALOG2 b on a.id=b.Report_Type_Id and b.RightType=1
left join @T_REPORT_CATALOG2 c on a.id=c.Report_Type_Id and c.RightType=2
where a.isReport='True'
/*
id name RightType RightType userId
----------- ---------- ----------- ----------- ----------------
16842753 员工手册 1 2 00000001
16842754 用工制度 1 2 00000001
50397185 报告制度 NULL 2 NULL
50397186 报告制度1 NULL NULL NULL(4 個資料列受到影響)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-24 14:09:25
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[T_REPORT_CATALOG]
if object_id('[T_REPORT_CATALOG]') is not null drop table [T_REPORT_CATALOG]
go
create table [T_REPORT_CATALOG]([id] int,[name] varchar(9),[isReport] varchar(5),[enabled] varchar(4))
insert [T_REPORT_CATALOG]
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'
--> 测试数据:[T_REPORT_CATALOG2]
if object_id('[T_REPORT_CATALOG2]') is not null drop table [T_REPORT_CATALOG2]
go
create table [T_REPORT_CATALOG2]([Report_Type_Id] int,[RightType] int,[userId] varchar(8))
insert [T_REPORT_CATALOG2]
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'
--------------开始查询--------------------------
select
a.id,
a.name,
max(case b.RightType when 1 then 1 else null end) as RightType1,
max(case b.RightType when 2 then 2 else null end) as RightType2,
b.userId
from
T_REPORT_CATALOG a
left join T_REPORT_CATALOG2 b on
a.id = b.Report_Type_Id
where
a.isReport = 'true'
group by
a.id,a.name,a.isReport,b.userId
----------------结果----------------------------
/* id name RightType1 RightType2 userId
----------- --------- ----------- ----------- --------
16842753 员工手册 1 2 00000001
16842754 用工制度 1 2 00000001
50397185 报告制度 NULL 2 00000001
50397186 报告制度1 NULL NULL NULL
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-24 14:09:25
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[T_REPORT_CATALOG]
if object_id('[T_REPORT_CATALOG]') is not null drop table [T_REPORT_CATALOG]
go
create table [T_REPORT_CATALOG]([id] int,[name] varchar(9),[isReport] varchar(5),[enabled] varchar(4))
insert [T_REPORT_CATALOG]
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'
--> 测试数据:[T_REPORT_CATALOG2]
if object_id('[T_REPORT_CATALOG2]') is not null drop table [T_REPORT_CATALOG2]
go
create table [T_REPORT_CATALOG2]([Report_Type_Id] int,[RightType] int,[userId] varchar(8))
insert [T_REPORT_CATALOG2]
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'
--------------开始查询--------------------------
select
a.id,
a.name,
max(case b.RightType when 1 then 1 else null end) as RightType1,
max(case b.RightType when 2 then 2 else null end) as RightType2,
b.userId
from
T_REPORT_CATALOG a
left join T_REPORT_CATALOG2 b on
a.id = b.Report_Type_Id
where
a.isReport = 'true'
group by
a.id,a.name,a.isReport,b.userId
----------------结果----------------------------
/* id name RightType1 RightType2 userId
----------- --------- ----------- ----------- --------
16842753 员工手册 1 2 00000001
16842754 用工制度 1 2 00000001
50397185 报告制度 NULL 2 00000001
50397186 报告制度1 NULL NULL NULL
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)
*/
[code]Create table T_REPORT_CATALOG(id nvarchar(10),[name] nvarchar(10),isReport nvarchar(10),[enabled]nvarchar(10))
insert into T_REPORT_CATALOG 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' Create table T_REPORT_CATALOG2(Report_Type_Id nvarchar(10),RightType int ,userId nvarchar(10))
insert into T_REPORT_CATALOG2 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'
Select A.ID,A.[name],B.RightType,C.RightType,IsNull(B.UserID,C.UserID) as UserID
From T_REPORT_CATALOG A
left join T_REPORT_CATALOG2 B On A.id=B.Report_Type_Id and B.RightType=1
left join T_REPORT_CATALOG2 C On A.id=C.Report_Type_Id and C.RightType=2
Where A.isReport='True'
/*ID name RightType RightType UserID
---------- ---------- ----------- ----------- ----------
16842753 员工手册 1 2 00000001
16842754 用工制度 1 2 00000001
50397185 报告制度 NULL 2 00000001
50397186 报告制度1 NULL NULL NULL(4 row(s) affected)
*/
drop table T_REPORT_CATALOG,T_REPORT_CATALOG2
[/code]
Create table T_REPORT_CATALOG(id nvarchar(10),[name] nvarchar(10),isReport nvarchar(10),[enabled]nvarchar(10))
insert into T_REPORT_CATALOG 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' Create table T_REPORT_CATALOG2(Report_Type_Id nvarchar(10),RightType int ,userId nvarchar(10))
insert into T_REPORT_CATALOG2 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'
Select A.ID,A.[name],B.RightType,C.RightType,IsNull(B.UserID,C.UserID) as UserID
From T_REPORT_CATALOG A
left join T_REPORT_CATALOG2 B On A.id=B.Report_Type_Id and B.RightType=1
left join T_REPORT_CATALOG2 C On A.id=C.Report_Type_Id and C.RightType=2
Where A.isReport='True'
/*ID name RightType RightType UserID
---------- ---------- ----------- ----------- ----------
16842753 员工手册 1 2 00000001
16842754 用工制度 1 2 00000001
50397185 报告制度 NULL 2 00000001
50397186 报告制度1 NULL NULL NULL(4 row(s) affected)
*/
drop table T_REPORT_CATALOG,T_REPORT_CATALOG2
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