---建临时表 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 行受影响)
---建临时表 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'
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','合格'select a.xm,a.xh,a.gzdw,b.khnd,b.khdc from #tbMain a left join (select * from #tbKh where khnd='2013') b on a.xh=b.xh where gzdw='市招商局' /------------------------------------------------------------------------------------------------/ 张三 A001 市招商局 2013 优秀 李四 A002 市招商局 NULL NULL
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) */
where a.gzdw='市招商局'
select a.xm,a.xh,a.gzdw,b.khnd,b.khdc from tbMain a left join tbKh b on a.xh=b.xh
where a.gzdw='市招商局'
and b.khnd='2013'
这个帖子不行?就是你要做一个临时表和你的tbmain先做一个交叉连接,形成你每个人要考核年度的表,然后再和考核结果进行连接。否则的很难实现你要的功能
---建临时表
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 行受影响)
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'
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','合格'select a.xm,a.xh,a.gzdw,b.khnd,b.khdc from #tbMain a left join
(select * from #tbKh where khnd='2013') b
on a.xh=b.xh where gzdw='市招商局'
/------------------------------------------------------------------------------------------------/
张三 A001 市招商局 2013 优秀
李四 A002 市招商局 NULL NULL
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)
*/
没想到 on语句后边居然还可以增加条件。
仔细考察了下on语句,发现on后边不仅可以是=,可以是任何比较符号甚至表达式,只要返回逻辑值即可。