34,588
社区成员
发帖
与我相关
我的任务
分享
create table tbMain
(xm varchar(10),xh varchar(10),gzdw varchar(10))
insert into tbMain
select '张三','A001','市招商局' union all
select '李四','A002','市招商局' union all
select '王五','B001','市教育局' union all
select '张兵','B002','市教育局'
create table tbKh
(xh varchar(10),khnd varchar(10),khdc varchar(10))
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','合格'
select a.xm,a.xh,a.gzdw,b.khnd,b.khdc
from tbMain a
left join tbKh b on a.xh=b.xh and khnd='2013'
where a.gzdw='市招商局'
/*
xm xh gzdw khnd khdc
---------- ---------- ---------- ---------- ----------
张三 A001 市招商局 2013 优秀
李四 A002 市招商局 NULL NULL
(2 row(s) affected)
*/
---建临时表
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','合格'
----开始查询
---修改一下,汇总#tbKh表年度
;with cte(khnd) as(
select khnd from #tbKh group by khnd
)
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'
---建临时表
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 行受影响)