表一:id eid subId
4 34 3
8 34 2
12 34 5
13 28 2
14 28 4表二:eid name su1 sub2 sub3 sub4 sub5
28 KKKK A B C D E
34 UUUU F G H I J我想查询表一时得到如下结果 将eid对应的name和sub的具体内容附上如下表
id eid subId name sub
4 34 3 KKKK H
8 34 2 KKKK G
12 34 5 KKKK J
13 28 2 UUUU B
14 28 4 UUUU DSQL 语句怎么写,谢谢大侠.....
4 34 3
8 34 2
12 34 5
13 28 2
14 28 4表二:eid name su1 sub2 sub3 sub4 sub5
28 KKKK A B C D E
34 UUUU F G H I J我想查询表一时得到如下结果 将eid对应的name和sub的具体内容附上如下表
id eid subId name sub
4 34 3 KKKK H
8 34 2 KKKK G
12 34 5 KKKK J
13 28 2 UUUU B
14 28 4 UUUU DSQL 语句怎么写,谢谢大侠.....
create table [TA]([id] int,[eid] int,[subId] int)
insert [TA]
select 4,34,3 union all
select 8,34,2 union all
select 12,34,5 union all
select 13,28,2 union all
select 14,28,4if object_id('[TB]') is not null drop table [TB]
create table [TB]([eid] int,[name] varchar(4),[sub1] varchar(1),[sub2] varchar(1),[sub3] varchar(1),[sub4] varchar(1),[sub5] varchar(1))
insert [TB]
select 28,'KKKK','A','B','C','D','E' union all
select 34,'UUUU','F','G','H','I','J'select A.id,A.eid,subId,name,sub from [TA] A ,
(select eid,name,sub=sub1,flag=1 from TB union all
select eid,name,sub=sub2,flag=2 from TB union all
select eid,name,sub=sub3,flag=3 from TB union all
select eid,name,sub=sub4,flag=4 from TB union all
select eid,name,sub=sub1,flag=5 from TB )B
where A.eid=B.eid and subId=flag
/*
id eid subId name sub
----------- ----------- ----------- ---- ----
4 34 3 UUUU H
8 34 2 UUUU G
12 34 5 UUUU F
13 28 2 KKKK B
14 28 4 KKKK D(所影响的行数为 5 行)*/drop table TA,[TB]
m.eid ,
m.subId ,
n.name ,
n.su1 sub
from tb1 m, tb2 n where m.eid = n.eid and m.subId = n.su1
union all
select m.id ,
m.eid ,
m.subId ,
n.name ,
n.su2 sub
from tb1 m, tb2 n where m.eid = n.eid and m.subId = n.su2
union all
select m.id ,
m.eid ,
m.subId ,
n.name ,
n.su3 sub
from tb1 m, tb2 n where m.eid = n.eid and m.subId = n.su3
union all
select m.id ,
m.eid ,
m.subId ,
n.name ,
n.su4 sub
from tb1 m, tb2 n where m.eid = n.eid and m.subId = n.su4
union all
select m.id ,
m.eid ,
m.subId ,
n.name ,
n.su5 sub
from tb1 m, tb2 n where m.eid = n.eid and m.subId = n.su5
order by id , eid , sub
declare @ta table(id int,eid int, subId int)insert into @ta(id ,eid ,subId)
select 4,34,3 union all
select 8, 34, 2 union all
select 12 ,34, 5 union all
select 13, 28, 2 union all
select 14, 28 ,4
declare @tb table(eid int,name varchar(10),sub1 varchar(5),sub2 varchar(5),sub3 varchar(5),sub4 varchar(5),sub5 varchar(5))insert into @tb(eid,name,sub1,sub2,sub3,sub4,sub5 )
select 28,'KKKK','A','B','C','D','E' union all
select 34,'UUUU','F','G','H','I','J' SELECT *,case when a.subid=2 then b.sub2
when a.subid=3 then b.sub3
when a.subid=4 then b.sub4
when a.subid=5 then b.sub5 end FROM @TA A LEFT JOIN @TB B ON a.eid=b.eid
insert [Tb1]
select 4,34,3 union all
select 8,34,2 union all
select 12,34,5 union all
select 13,28,2 union all
select 14,28,4create table [TB2]([eid] int,[name] varchar(4),[sub1] varchar(1),[sub2] varchar(1),[sub3] varchar(1),[sub4] varchar(1),[sub5] varchar(1))
insert [TB2]
select 28,'KKKK','A','B','C','D','E' union all
select 34,'UUUU','F','G','H','I','J'select m.id ,
m.eid ,
m.subId ,
n.name ,
case when m.subId = 1 then n.sub1
when m.subId = 2 then n.sub2
when m.subId = 3 then n.sub3
when m.subId = 4 then n.sub4
when m.subId = 5 then n.sub5
end sub
from tb1 m, tb2 n where m.eid = n.eid
order by m.id , m.eiddrop table tb1 , tb2/*
id eid subId name sub
----------- ----------- ----------- ---- ----
4 34 3 UUUU H
8 34 2 UUUU G
12 34 5 UUUU J
13 28 2 KKKK B
14 28 4 KKKK D(所影响的行数为 5 行)
*/
go
create table [ta]([id] int,[eid] int,[subId] int)
insert [ta]
select 4,34,3 union all
select 8,34,2 union all
select 12,34,5 union all
select 13,28,2 union all
select 14,28,4
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([eid] int,[name] varchar(4),[sub1] varchar(1),[sub2] varchar(1),
[sub3] varchar(1),[sub4] varchar(1),[sub5] varchar(1))
insert [tb]
select 28,'KKKK','A','B','C','D','E' union all
select 34,'UUUU','F','G','H','I','J'-->查询
select a.*,b.name,b.sub1 from ta a left join tb b on a.eid=b.eid where a.subid=1
union all
select a.*,b.name,b.sub2 from ta a left join tb b on a.eid=b.eid where a.subid=2
union all
select a.*,b.name,b.sub3 from ta a left join tb b on a.eid=b.eid where a.subid=3
union all
select a.*,b.name,b.sub4 from ta a left join tb b on a.eid=b.eid where a.subid=4
union all
select a.*,b.name,b.sub5 from ta a left join tb b on a.eid=b.eid where a.subid=5
order by id--测试结果:
/*
id eid subId name sub1
----------- ----------- ----------- ---- ----
4 34 3 UUUU H
8 34 2 UUUU G
12 34 5 UUUU J
13 28 2 KKKK B
14 28 4 KKKK D(所影响的行数为 5 行)*/
我用的是nalnait的方法,dawugui的方法类似,dawugui
你的分好高哦,都钻了,我连星都还没有混到呢