求sql语句，来拿分

[kevn]

脑袋晕了，帮忙看看有更好的办法吗
A表
id db
1 'sss'
2 'aaa'
B表
id kind avg max min
1 ir 2 4 1
1 mld 3 3.4 2.3
1 mcd 3 4 3
2 ir 1 3 1
2 mld 3 3 1
2 mcd 3 3 1

a.id = b.id
想要结果 id db　ir_avg ir_max ir_min mld_avg mld_max mld_min mcd_avg mcdmax mcdmin
1 sss　2 4 1 3 3.4 2.3 3 4 3
2 aaa 1 3 1 3 3 1 3 3 1
也就是把ir，mld，mcd的avg，max，min都作为列出现

我现在用的是先select * from b where b.id = a.id and kind = 'ir' 虚拟出一个表
以此类推，虚拟出mld，mcd的表，然后再a表和这3个联合查询，感觉很笨，很慢，
求更好方法

[永生天地]

好像只有笨方法了

[kevn]

[Linares]

--> 测试数据：#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(id int, db varchar(8))
insert into #a
select 1, 'sss' union all
select 2, 'aaa'
--> 测试数据：#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(id int, kind varchar(8), avg int, max numeric(2,1), min numeric(2,1))
insert into #b
select 1, 'ir', 2, 4, 1 union all
select 1, 'mld', 3, 3.4, 2.3 union all
select 1, 'mcd', 3, 4, 3 union all
select 2, 'ir', 1, 3, 1 union all
select 2, 'mld', 3, 3, 1 union all
select 2, 'mcd', 3, 3, 1

select * from
(
	select a.db, b.* from #a a,
	(
		select id,kind+'_avg' kind, avg val from #b union all
		select id,kind+'_min' kind, min val from #b union all
		select id,kind+'_max' kind, max val from #b
	) b
	where a.id=b.id
) t
pivot (max(val) for kind in (ir_avg,ir_max,ir_min,mld_avg,mld_max,mld_min,mcd_avg,mcd_max,mcd_min)) p

/*
db       id          ir_avg                                  ir_max                                  ir_min                                  mld_avg                                 mld_max                                 mld_min                                 mcd_avg                                 mcd_max                                 mcd_min
-------- ----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
sss      1           2.0                                     4.0                                     1.0                                     3.0                                     3.4                                     2.3                                     3.0                                     4.0                                     3.0
aaa      2           1.0                                     3.0                                     1.0                                     3.0                                     3.0                                     1.0                                     3.0                                     3.0                                     1.0
*/

[kevn]

楼上兄弟，我是2000

[Linares]

-- 2000 还不简单
select
	id,
	ir_avg = max(case kind when 'ir' then avg end),
	ir_max = max(case kind when 'ir' then max end),
	ir_min = max(case kind when 'ir' then min end),
	mld_avg = max(case kind when 'mld' then avg end),
	mld_max = max(case kind when 'mld' then max end),
	mld_min = max(case kind when 'mld' then min end)
from #b
group by id

[Linares]

mcd 没写，也没关联 #a，下面的问题你应该OK。

[活在岸上的魚]

[Quote=引用 3 楼 linares 的回复:]
SQL code
--> 测试数据：#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(id int, db varchar(8))
insert into #a
select 1, 'sss' union all
select 2, 'aaa'
--> 测试数据：#b
if obje……
[/Quote]
厉害

[kevn]

这招貌似用过，刚才想了想，没想出来，我实际情况是5行，4列，要写20个max。。不过好像也没其他方法了

[Linares]

有这需求那也得写啊，20个而已，200+个可以从syscolumns表build一个语句出来。