create table UserInfo(userID varchar(10),UserName varchar(10),deparid nvarchar(10))
insert into UserInfo values('001' , N'赵雷' , N'A001')
insert into UserInfo values('002' , N'钱电' , N'A001')
insert into UserInfo values('003' , N'孙风' , N'A001')
insert into UserInfo values('004' , N'李云' , N'A002')
insert into UserInfo values('005' , N'周梅' , N'A002')
insert into UserInfo values('006' , N'吴兰' , N'A002')
insert into UserInfo values('007' , N'郑竹' , N'A002')
insert into UserInfo values('008' , N'王菊' , N'A003')create table Part(Dguid varchar(10),Dname varchar(10))
insert into Part values('A001' , N'部门1')
insert into Part values('A002' , N'部门2')
insert into Part values('A003' , N'部门3')create table Seq1(sequence varchar(10),userID varchar(10))
insert into Seq1 values('1' , '001')
insert into Seq1 values('1' , '002')
insert into Seq1 values('1' , '003')
insert into Seq1 values('1' , '004')
insert into Seq1 values('1' , '005')
insert into Seq1 values('1' , '006')
insert into Seq1 values('2' , '002')
insert into Seq1 values('2' , '002')create table Seq2(sequence varchar(10),userID varchar(10))
insert into Seq2 values('1' , '001')
insert into Seq2 values('1' , '002')
insert into Seq2 values('1' , '003')
insert into Seq2 values('1' , '004')
insert into Seq2 values('2' , '005')
insert into Seq2 values('2' , '006')
insert into Seq2 values('2' , '002')
insert into Seq2 values('2' , '002')条件 以sequence为1的数据查询
先获取表四和表三的数据 结果应该是
---------
sequence userid
1 001
1 002
1 003
1 004
1 005
1 006
----------然后匹配表1的userid
从而获取deparid的值
对应userid的deparid值
也有可能几个userid对应一个deparid 所以给的数据是 有一条重复的A001
最后通过获得deparid值 来匹配第2张表
一个或者多个deparid对应一个Gguid 然后查处所在的部门
最后的结果应该是
---
Dname
部门1
部门2--------------前面发帖子 刚下班有点急了 不好意思 有点错乱
FROM(
SELECT sequence,userid FROM seq1 WHERE sequence=1
UNIOIN
SELECT sequence,userid FROM seq2 WHERE sequence=1
) AS A
JOIN UserInfo AS B
ON A.userid=B.UserID
JOIN Part AS C
ON B.DepartID=C.Dguid
create table UserInfo(userID varchar(10),UserName varchar(10),deparid nvarchar(10))
insert into UserInfo values('001' , N'赵雷' , N'A001')
insert into UserInfo values('002' , N'钱电' , N'A001')
insert into UserInfo values('003' , N'孙风' , N'A001')
insert into UserInfo values('004' , N'李云' , N'A002')
insert into UserInfo values('005' , N'周梅' , N'A002')
insert into UserInfo values('006' , N'吴兰' , N'A002')
insert into UserInfo values('007' , N'郑竹' , N'A002')
insert into UserInfo values('008' , N'王菊' , N'A003')create table Part(Dguid varchar(10),Dname varchar(10))
insert into Part values('A001' , N'部门1')
insert into Part values('A002' , N'部门2')
insert into Part values('A002' , N'部门3')
select * from Partcreate table Seq1(sequence varchar(10),userID varchar(10))
insert into Seq1 values('1' , '001')
insert into Seq1 values('1' , '002')
insert into Seq1 values('1' , '003')
insert into Seq1 values('1' , '004')
insert into Seq1 values('1' , '005')
insert into Seq1 values('1' , '006')
insert into Seq1 values('2' , '002')
insert into Seq1 values('2' , '002')
create table Seq2(Prosequence varchar(10),userID varchar(10))
insert into Seq1 values('1' , '001')
insert into Seq1 values('1' , '002')
insert into Seq1 values('1' , '003')
insert into Seq1 values('1' , '004')
insert into Seq1 values('2' , '005')
insert into Seq1 values('2' , '006')
insert into Seq1 values('2' , '002')
insert into Seq1 values('2' , '002')select * from(
select b.sequence,b.userID,c.deparid,c.UserName from(
select * from(select * from Seq1 union
select * from Seq2)a where a.sequence=1)b
full join UserInfo c on b.userID=c.userID)d
full join Part e on d.deparid=e.Dguid/*
sequence userID deparid UserName Dguid Dname
1 001 A001 赵雷 A001 部门1
1 002 A001 钱电 A001 部门1
1 003 A001 孙风 A001 部门1
1 004 A002 李云 A002 部门2
1 004 A002 李云 A002 部门3
1 005 A002 周梅 A002 部门2
1 005 A002 周梅 A002 部门3
1 006 A002 吴兰 A002 部门2
1 006 A002 吴兰 A002 部门3
NULL NULL A002 郑竹 A002 部门2
NULL NULL A002 郑竹 A002 部门3
NULL NULL A003 王菊 NULL NULL
*/
insert into UserInfo values('001' , N'赵雷' , N'A001')
insert into UserInfo values('002' , N'钱电' , N'A001')
insert into UserInfo values('003' , N'孙风' , N'A001')
insert into UserInfo values('004' , N'李云' , N'A002')
insert into UserInfo values('005' , N'周梅' , N'A002')
insert into UserInfo values('006' , N'吴兰' , N'A002')
insert into UserInfo values('007' , N'郑竹' , N'A002')
insert into UserInfo values('008' , N'王菊' , N'A003')
create table Part(Dguid varchar(10),Dname varchar(10))
insert into Part values('A001' , N'部门1')
insert into Part values('A002' , N'部门2')
create table Seq1(sequence varchar(10),userID varchar(10))
insert into Seq1 values('1' , '001')
insert into Seq1 values('1' , '002')
insert into Seq1 values('1' , '003')
insert into Seq1 values('1' , '004')
insert into Seq1 values('1' , '005')
insert into Seq1 values('1' , '006')
insert into Seq1 values('2' , '002')
insert into Seq1 values('2' , '002')
create table Seq2(Prosequence varchar(10),userID varchar(10))
insert into Seq2 values('1' , '001')
insert into Seq2 values('1' , '002')
insert into Seq2 values('1' , '003')
insert into Seq2 values('1' , '004')
insert into Seq2 values('2' , '005')
insert into Seq2 values('2' , '006')
insert into Seq2 values('2' , '002')
insert into Seq2 values('2' , '002')select m.*,
[UserInfo_cnt] = isnull((select count(1) from UserInfo n where n.deparid = m.dguid),0),
[Seq1_cnt_按照实际匹配数量] = isnull((select count(1) from UserInfo n , Seq1 t where n.deparid = m.dguid and n.userID = t.userID),0),
[Seq2_cnt_按照实际匹配数量] = isnull((select count(1) from UserInfo n , Seq2 t where n.deparid = m.dguid and n.userID = t.userID),0),
[Seq1_cnt_去重复后匹配数量] = isnull((select count(1) from UserInfo n , (select distinct * from seq1) t where n.deparid = m.dguid and n.userID = t.userID),0),
[Seq2_cnt_去重复后匹配数量] = isnull((select count(1) from UserInfo n , (select distinct * from seq2) t where n.deparid = m.dguid and n.userID = t.userID),0)
from Part m
drop table UserInfo,Part,Seq1 ,Seq2/*
Dguid Dname UserInfo_cnt Seq1_cnt_按照实际匹配数量 Seq2_cnt_按照实际匹配数量 Seq1_cnt_去重复后匹配数量 Seq2_cnt_去重复后匹配数量
---------- ---------- ------------ ----------------- ----------------- ----------------- -----------------
A001 部门1 3 5 5 4 4
A002 部门2 4 3 3 3 3(所影响的行数为 2 行)
*/
insert into UserInfo values('001' , N'赵雷' , N'A001')
insert into UserInfo values('002' , N'钱电' , N'A001')
insert into UserInfo values('003' , N'孙风' , N'A001')
insert into UserInfo values('004' , N'李云' , N'A002')
insert into UserInfo values('005' , N'周梅' , N'A002')
insert into UserInfo values('006' , N'吴兰' , N'A002')
insert into UserInfo values('007' , N'郑竹' , N'A002')
insert into UserInfo values('008' , N'王菊' , N'A003')create table Part(Dguid varchar(10),Dname varchar(10))
insert into Part values('A001' , N'部门1')
insert into Part values('A002' , N'部门2')
insert into Part values('A003' , N'部门3')create table Seq1(sequence varchar(10),userID varchar(10))
insert into Seq1 values('1' , '001')
insert into Seq1 values('1' , '002')
insert into Seq1 values('1' , '003')
insert into Seq1 values('1' , '004')
insert into Seq1 values('1' , '005')
insert into Seq1 values('1' , '006')
insert into Seq1 values('2' , '002')
insert into Seq1 values('2' , '002')create table Seq2(sequence varchar(10),userID varchar(10))
insert into Seq2 values('1' , '001')
insert into Seq2 values('1' , '002')
insert into Seq2 values('1' , '003')
insert into Seq2 values('1' , '004')
insert into Seq2 values('2' , '005')
insert into Seq2 values('2' , '006')
insert into Seq2 values('2' , '002')
insert into Seq2 values('2' , '002')
go
--1.方法一
select p.* from Part p where exists(select 1 from UserInfo u,
(
select sequence ,userID from seq1 where sequence = 1
union
select sequence ,userID from seq2 where sequence = 1
) s
where p.Dguid = u.deparid and u.userID = s.userID)
/*
Dguid Dname
---------- ----------
A001 部门1
A002 部门2(所影响的行数为 2 行)
*/--2.方法二
select distinct p.* from Part p , UserInfo u,
(
select sequence ,userID from seq1 where sequence = 1
union
select sequence ,userID from seq2 where sequence = 1
) s
where p.Dguid = u.deparid and u.userID = s.userID
/*
Dguid Dname
---------- ----------
A001 部门1
A002 部门2(所影响的行数为 2 行)
*/drop table UserInfo,Part,Seq1,Seq2