34,593
社区成员
发帖
与我相关
我的任务
分享
---建临时表
if object_id('Tempdb..#tbMain') is not null drop table #tbMain
if object_id('Tempdb..#tbKh') is not null drop table #tbKh
create table #tbMain(
aid int identity(1,1) not null,
xm nvarchar(100) null,
xh nvarchar(100) null,
gzdw nvarchar(100) null
)
create table #tbKh(
bid int identity(1,1) not null,
xh nvarchar(100) null,
khnd nvarchar(10) null,
khdc nvarchar(10) null
)
---插入测试数据
Insert Into #tbMain
select '张三','A001','市招商局' union all
select '李四','A002','市招商局' union all
select '王五','B001','市教育局' union all
select '张兵','B002','市教育局'
Insert Into #tbKh
select 'A001','2012','优秀' union all
select 'A002','2012','合格' union all
select 'A001','2013','优秀' union all
select 'B001','2012','优秀' union all
select 'B002','2012','合格' union all
select 'B001','2013','优秀' union all
select 'B002','2013','合格'
----开始查询
;with cte(khnd) as(
select '2012' union all
select '2013'
)
select s.xm,s.xh,s.gzdw,z.khnd,z.khdc
from #tbMain s cross join cte t
left join #tbKh z on s.xh=z.xh and t.khnd=z.khnd
where s.gzdw='市招商局' and t.khnd='2013'
---------------------------------
--结果
(4 行受影响)
(7 行受影响)
xm xh gzdw khnd khdc
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- ----------
张三 A001 市招商局 2013 优秀
李四 A002 市招商局 NULL NULL
(2 行受影响)
--> 测试数据: [tbMain]
if object_id('[tbMain]') is not null drop table [tbMain]
go
create table [tbMain] ([xm] varchar(6),[xh] varchar(4),[gzdw] varchar(8))
insert into [tbMain]
select '张三','A011','市招商局' union all
select '李四','A012','市招商局' union all
select '王五','A002','市教育局' union all
select '张小兵','B019','市教育局'
--> 测试数据: [tbKh]
if object_id('[tbKh]') is not null drop table [tbKh]
go
create table [tbKh] ([xh] varchar(4),[khnd] int,[khdc] varchar(4))
insert into [tbKh]
select 'A011',2012,'优秀' union all
select 'A012',2012,'合格' union all
select 'A011',2013,'优秀' union all
select 'A012',2013,'合格' union all
select 'A002',2013,'合格' union all
select 'B019',2013,'优秀'
declare @t table(khnd int)
insert @t
select 2013 union
select 2014
select *
from tbMain s cross join @t t
left join tbKh on s.xh=tbKh.xh and t.khnd=tbKh.khnd
张三 A011 市招商局 2013 A011 2013 优秀
李四 A012 市招商局 2013 A012 2013 合格
王五 A002 市教育局 2013 A002 2013 合格
张小兵 B019 市教育局 2013 B019 2013 优秀
张三 A011 市招商局 2014 NULL NULL NULL
李四 A012 市招商局 2014 NULL NULL NULL
王五 A002 市教育局 2014 NULL NULL NULL
张小兵 B019 市教育局 2014 NULL NULL NULL
select * from tbKh a
left join tbMain b
on a.xh=b.xh
where gzdw ='市招商局' and khnd=2014