数据检索

[yamsan]

有以下两个表及数据
表1：
id a_1 a_2 a_3 b_1 b_2 b_3
5 60 70 80 30 40 50
6 65 75 85 35 45 55
表2：
id num cname
5 1 语文
5 2 数学
5 3 英语
6 1 语文
6 2 数学
6 3 英语
连接：表2.id = 表1.id 表2的num字段的值对应表1的字段名的末尾数字，想查出以下结果：
id cname a b
5 语文 60 30
5 数学 70 40
5 英语 80 50
6 语文 65 35
6 数学 75 45
6 英语 85 55
请问，有什么比较好的方法写出这个sql查询语句？

[AcHerat]

with cte as
(
    select id,a_1 as a,b_1 as b from tb1
    union all
    select id,a_2,b_2 from tb1
    union all
    select id,a_3,b_3 from tb1
)

select a.id,a.cname,b.a,b.b
from tb2 a left join cte b on a.id = b.id

[AcHerat]

with cte as
(
    select id,a_1 as a,b_1 as b,1 as rn from tb1
    union all
    select id,a_2,b_2,2 from tb1
    union all
    select id,a_3,b_3,3 from tb1
)

select a.id,a.cname,b.a,b.b
from tb2 a left join cte b on a.id = b.id and a.num = b.rn

[AcHerat]

2楼的试试！

[快溜]

select a.id,a.cname,b.a_1 as a,b.b_1 as b
from (select * from 表2 where num=1) a,表2 b where a.id=b.id
union
select a.id,a.cname,b.a_2 as a,b.b_2 as b
from (select * from 表2 where num=2) a,表2 b where a.id=b.id
union
select a.id,a.cname,b.a_3 as a,b.b_3 as b
from (select * from 表2 where num=3) a,表2 b where a.id=b.id
order by id

[Shawn]

CREATE TABLE #temp1
(
	id INT,
	a_1 INT,
	a_2 INT,
	a_3 INT,
	b_1 INT,
	b_2 INT,
	b_3 INT
)
INSERT #temp1
select 5, 60, 70, 80, 30, 40, 50 union all
select 6, 65, 75, 85, 35, 45, 55

CREATE TABLE #temp2
(
	id INT,
	num INT,
	cname NVARCHAR(10)
)
INSERT #temp2
select 5, 1, N'语文' union all
select 5, 2, N'数学' union all
select 5, 3, N'英语' union all
select 6, 1, N'语文' union all
select 6, 2, N'数学' union all
select 6, 3, N'英语'
GO
--SQL:
;WITH cte AS
(
	SELECT id, fieldname1 = left(fieldname, charindex('_', fieldname)-1), fieldname2 = right(fieldname, charindex('_', reverse(fieldname))-1), fieldvalue
	FROM #TEMP1 a
	UNPIVOT
	(fieldvalue FOR fieldname IN([a_1], [a_2], [a_3], [b_1], [b_2], [b_3])) b
)
SELECT * from #temp2 a
outer apply
(select a = fieldvalue from cte where id = a.id and fieldname2 = a.num and fieldname1 = 'a') b
outer apply
(select b = fieldvalue from cte where id = a.id and fieldname2 = a.num and fieldname1 = 'b') c
/*
5	1	语文	60	30
5	2	数学	70	40
5	3	英语	80	50
6	1	语文	65	35
6	2	数学	75	45
6	3	英语	85	55
*/
--如果不固定,用动态SQL

[O爱咋咋地O]

create table tb1(id int,a_1 int,a_2 int,a_3 int,b_1 int,b_2 int,b_3 int)
create table tb2(id int,num int,cname nvarchar(10))
insert tb1 select 5,60,70,80,30,40,50 union all
select 6,65,75,85,35,45,55
insert tb2 select 5,1,N'语文' union all
select 5,2,N'数学' union all
select 5,3,N'英语' union all
select 6,1,N'语文' union all
select 6,2,N'数学' union all
select 6,3,N'英语' 
select a.id,b.cname,
case when b.num=1 then a.a_1  when b.num=2 then a.a_2  when b.num=3 then a.a_3 end as a,
case when b.num=1 then a.b_1  when b.num=2 then a.b_2  when b.num=3 then a.b_3 end as b
 from  tb1 a join tb2 b on b.id=a.id