34,593
社区成员
发帖
与我相关
我的任务
分享经典sql语句 作为页面呈现效果---挑战一下.
已知表tab 有已下数据 code1,code2,code3 3个为联合主键
code1 code2 code3 value
'A09',1,'01',0.0000,
'A09',1,'02',null,
'A09',1,'03',null,
'A09',1,'04',null,
'A09',1,'05',null,
'A09',1,'06',null,
'A09',1,'07',null,
'A09',1,'08',null,
'A09',1,'09',null,
'A09',1,'10',null,
'A09',1,'11',null,
'A09',1,'12',null,
'A0A',1,'01',1.9,
'A0A',1,'02',null,
'A0A',1,'03',null,
'A0A',1,'04',1.2,
'A0A',1,'05',null,
'A0A',1,'06',null,
'A0A',1,'07',null,
'A0A',1,'08',null,
'A0A',1,'09',null,
'A0A',1,'10',null,
'A0A',1,'11',null,
'A0A',1,'12',null,
select code1,code2,
max(case code3 when '01' then code3 else '' end ) as f1,
max(case code3 when '02' then code3 else '' end ) as f2,
max(case code3 when '03' then code3 else '' end ) as f3,
max(case code3 when '04' then code3 else '' end ) as f4,
max(case code3 when '05' then code3 else '' end ) as f5,
max(case code3 when '06' then code3 else '' end ) as f6,
max(case code3 when '07' then code3 else '' end ) as f7,
max(case code3 when '08' then code3 else '' end ) as f8,
max(case code3 when '09' then code3 else '' end ) as f9,
max(case code3 when '10' then code3 else '' end ) as f10,
max(case code3 when '11' then code3 else '' end ) as f11,
max(case code3 when '12' then code3 else '' end ) as f12
from tab group by code1,code2
通过以上SQl语句查询出以下结果
code1 code2 f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12
A09 1 01 02 03 04 05 06 07 08 09 10 11 12
A0A 1 01 02 03 04 05 06 07 08 09 10 11 12
现在要显示出达到以下效果:
code1 code2 f1 c1 f2 c2 f3 c3 f4 c4 .......f12 c12
A09 1 01 0.0 02 null 03 null 04 null .... 12 null
A0A 1 01 1.9 02 null 03 null 04 1.2 ..... 12 null
尽量不用存储过程.能够在以上的sql语句在修改一下达到最好..谢谢....
code1 code2 code3 value
'A09',1,'01',0.0000,
'A09',1,'02',null,
'A09',1,'03',null,
'A09',1,'04',null,
'A09',1,'05',null,
'A09',1,'06',null,
'A09',1,'07',null,
'A09',1,'08',null,
'A09',1,'09',null,
'A09',1,'10',null,
'A09',1,'11',null,
'A09',1,'12',null,
'A0A',1,'01',1.9,
'A0A',1,'02',null,
'A0A',1,'03',null,
'A0A',1,'04',1.2,
'A0A',1,'05',null,
'A0A',1,'06',null,
'A0A',1,'07',null,
'A0A',1,'08',null,
'A0A',1,'09',null,
'A0A',1,'10',null,
'A0A',1,'11',null,
'A0A',1,'12',null,
select code1,code2,
max(case code3 when '01' then code3 else '' end ) as f1,
max(case code3 when '02' then code3 else '' end ) as f2,
max(case code3 when '03' then code3 else '' end ) as f3,
max(case code3 when '04' then code3 else '' end ) as f4,
max(case code3 when '05' then code3 else '' end ) as f5,
max(case code3 when '06' then code3 else '' end ) as f6,
max(case code3 when '07' then code3 else '' end ) as f7,
max(case code3 when '08' then code3 else '' end ) as f8,
max(case code3 when '09' then code3 else '' end ) as f9,
max(case code3 when '10' then code3 else '' end ) as f10,
max(case code3 when '11' then code3 else '' end ) as f11,
max(case code3 when '12' then code3 else '' end ) as f12
from tab group by code1,code2
通过以上SQl语句查询出以下结果
code1 code2 f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12
A09 1 01 02 03 04 05 06 07 08 09 10 11 12
A0A 1 01 02 03 04 05 06 07 08 09 10 11 12
现在要显示出达到以下效果:
code1 code2 f1 c1 f2 c2 f3 c3 f4 c4 .......f12 c12
A09 1 01 0.0 02 null 03 null 04 null .... 12 null
A0A 1 01 1.9 02 null 03 null 04 1.2 ..... 12 null
尽量不用存储过程.能够在以上的sql语句在修改一下达到最好..谢谢....
...全文
请发表友善的回复…
发表回复
dawugui 2008-01-03
- 打赏
- 举报
create table tb(code1 varchar(10), code2 int, code3 varchar(10), value decimal(18,2))
insert into tb values('A09',1,'01',0.00)
insert into tb values('A09',1,'02',null)
insert into tb values('A09',1,'03',null)
insert into tb values('A09',1,'04',null)
insert into tb values('A09',1,'05',null)
insert into tb values('A09',1,'06',null)
insert into tb values('A09',1,'07',null)
insert into tb values('A09',1,'08',null)
insert into tb values('A09',1,'09',null)
insert into tb values('A09',1,'10',null)
insert into tb values('A09',1,'11',null)
insert into tb values('A09',1,'12',null)
insert into tb values('A0A',1,'01',1.9)
insert into tb values('A0A',1,'02',null)
insert into tb values('A0A',1,'03',null)
insert into tb values('A0A',1,'04',1.2)
insert into tb values('A0A',1,'05',null)
insert into tb values('A0A',1,'06',null)
insert into tb values('A0A',1,'07',null)
insert into tb values('A0A',1,'08',null)
insert into tb values('A0A',1,'09',null)
insert into tb values('A0A',1,'10',null)
insert into tb values('A0A',1,'11',null)
insert into tb values('A0A',1,'12',null)
go
--静态SQL，指CODE3的值固定为01-12
select code1,code2,
max(case code3 when '01' then code3 else '' end ) as f1,
max(case code3 when '01' then cast(value as varchar) end ) as c1,
max(case code3 when '02' then code3 else '' end ) as f2,
max(case code3 when '02' then cast(value as varchar) end ) as c2,
max(case code3 when '03' then code3 else '' end ) as f3,
max(case code3 when '03' then cast(value as varchar) end ) as c3,
max(case code3 when '04' then code3 else '' end ) as f4,
max(case code3 when '04' then cast(value as varchar) end ) as c4,
max(case code3 when '05' then code3 else '' end ) as f5,
max(case code3 when '05' then cast(value as varchar) end ) as c5,
max(case code3 when '06' then code3 else '' end ) as f6,
max(case code3 when '06' then cast(value as varchar) end ) as c6,
max(case code3 when '07' then code3 else '' end ) as f7,
max(case code3 when '07' then cast(value as varchar) end ) as c7,
max(case code3 when '08' then code3 else '' end ) as f8,
max(case code3 when '08' then cast(value as varchar) end ) as c8,
max(case code3 when '09' then code3 else '' end ) as f9,
max(case code3 when '09' then cast(value as varchar) end ) as c9,
max(case code3 when '10' then code3 else '' end ) as f10,
max(case code3 when '10' then cast(value as varchar) end ) as c10,
max(case code3 when '11' then code3 else '' end ) as f11,
max(case code3 when '11' then cast(value as varchar) end ) as c11,
max(case code3 when '12' then code3 else '' end ) as f12,
max(case code3 when '12' then cast(value as varchar) end ) as c12
from tb group by code1,code2
/*
code1 code2 f1 c1 f2 c2 f3 c3 f4 c4 f5 c5 f6 c6 f7 c7 f8 c8 f9 c9 f10 c10 f11 c11 f12 c12
---------- ----------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
A09 1 01 0.00 02 NULL 03 NULL 04 NULL 05 NULL 06 NULL 07 NULL 08 NULL 09 NULL 10 NULL 11 NULL 12 NULL
A0A 1 01 1.90 02 NULL 03 NULL 04 1.20 05 NULL 06 NULL 07 NULL 08 NULL 09 NULL 10 NULL 11 NULL 12 NULL
警告: 聚合或其他 SET 操作消除了空值。
(2 行受影响)
*/
--动态SQL，指CODE3的值不固定
declare @sql varchar(8000)
set @sql = 'select code1,code2'
select @sql = @sql + ' , max(case code3 when ''' + code3 + ''' then code3 else '' '' end) [f' + code3 + ']'
+ ' , max(case code3 when ''' + code3 + ''' then cast(value as varchar) end) [f' + code3 + ']'
from (select distinct code3 from tb) as a
set @sql = @sql + ' from tb group by code1,code2 '
exec(@sql)
/*
code1 code2 f01 f01 f02 f02 f03 f03 f04 f04 f05 f05 f06 f06 f07 f07 f08 f08 f09 f09 f10 f10 f11 f11 f12 f12
---------- ----------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
A09 1 01 0.00 02 NULL 03 NULL 04 NULL 05 NULL 06 NULL 07 NULL 08 NULL 09 NULL 10 NULL 11 NULL 12 NULL
A0A 1 01 1.90 02 NULL 03 NULL 04 1.20 05 NULL 06 NULL 07 NULL 08 NULL 09 NULL 10 NULL 11 NULL 12 NULL
警告: 聚合或其他 SET 操作消除了空值。
(2 行受影响)
*/
drop table tb
dawugui 2008-01-03
- 打赏
- 举报
create table tb(code1 varchar(10), code2 int, code3 varchar(10), value decimal(18,2))
insert into tb values('A09',1,'01',0.00)
insert into tb values('A09',1,'02',null)
insert into tb values('A09',1,'03',null)
insert into tb values('A09',1,'04',null)
insert into tb values('A09',1,'05',null)
insert into tb values('A09',1,'06',null)
insert into tb values('A09',1,'07',null)
insert into tb values('A09',1,'08',null)
insert into tb values('A09',1,'09',null)
insert into tb values('A09',1,'10',null)
insert into tb values('A09',1,'11',null)
insert into tb values('A09',1,'12',null)
insert into tb values('A0A',1,'01',1.9)
insert into tb values('A0A',1,'02',null)
insert into tb values('A0A',1,'03',null)
insert into tb values('A0A',1,'04',1.2)
insert into tb values('A0A',1,'05',null)
insert into tb values('A0A',1,'06',null)
insert into tb values('A0A',1,'07',null)
insert into tb values('A0A',1,'08',null)
insert into tb values('A0A',1,'09',null)
insert into tb values('A0A',1,'10',null)
insert into tb values('A0A',1,'11',null)
insert into tb values('A0A',1,'12',null)
go
select code1,code2,
max(case code3 when '01' then code3 else '' end ) as f1,
max(case code3 when '01' then cast(value as varchar) end ) as c1,
max(case code3 when '02' then code3 else '' end ) as f2,
max(case code3 when '02' then cast(value as varchar) end ) as c2,
max(case code3 when '03' then code3 else '' end ) as f3,
max(case code3 when '03' then cast(value as varchar) end ) as c3,
max(case code3 when '04' then code3 else '' end ) as f4,
max(case code3 when '04' then cast(value as varchar) end ) as c4,
max(case code3 when '05' then code3 else '' end ) as f5,
max(case code3 when '05' then cast(value as varchar) end ) as c5,
max(case code3 when '06' then code3 else '' end ) as f6,
max(case code3 when '06' then cast(value as varchar) end ) as c6,
max(case code3 when '07' then code3 else '' end ) as f7,
max(case code3 when '07' then cast(value as varchar) end ) as c7,
max(case code3 when '08' then code3 else '' end ) as f8,
max(case code3 when '08' then cast(value as varchar) end ) as c8,
max(case code3 when '09' then code3 else '' end ) as f9,
max(case code3 when '09' then cast(value as varchar) end ) as c9,
max(case code3 when '10' then code3 else '' end ) as f10,
max(case code3 when '10' then cast(value as varchar) end ) as c10,
max(case code3 when '11' then code3 else '' end ) as f11,
max(case code3 when '11' then cast(value as varchar) end ) as c11,
max(case code3 when '12' then code3 else '' end ) as f12,
max(case code3 when '12' then cast(value as varchar) end ) as c12
from tb group by code1,code2
drop table tb
/*
code1 code2 f1 c1 f2 c2 f3 c3 f4 c4 f5 c5 f6 c6 f7 c7 f8 c8 f9 c9 f10 c10 f11 c11 f12 c12
---------- ----------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
A09 1 01 0.00 02 NULL 03 NULL 04 NULL 05 NULL 06 NULL 07 NULL 08 NULL 09 NULL 10 NULL 11 NULL 12 NULL
A0A 1 01 1.90 02 NULL 03 NULL 04 1.20 05 NULL 06 NULL 07 NULL 08 NULL 09 NULL 10 NULL 11 NULL 12 NULL
警告: 聚合或其他 SET 操作消除了空值。
(2 行受影响)
*/dawugui 2008-01-03
- 打赏
- 举报
select code1,code2,
max(case code3 when '01' then code3 else '' end ) as f1,
max(case code3 when '01' then value end ) as c1,
max(case code3 when '02' then code3 else '' end ) as f2,
max(case code3 when '02' then value end ) as c2,
max(case code3 when '03' then code3 else '' end ) as f3,
max(case code3 when '03' then value end ) as c3,
max(case code3 when '04' then code3 else '' end ) as f4,
max(case code3 when '04' then value end ) as c4,
max(case code3 when '05' then code3 else '' end ) as f5,
max(case code3 when '05' then value end ) as c5,
max(case code3 when '06' then code3 else '' end ) as f6,
max(case code3 when '06' then value end ) as c6,
max(case code3 when '07' then code3 else '' end ) as f7,
max(case code3 when '07' then value end ) as c7,
max(case code3 when '08' then code3 else '' end ) as f8,
max(case code3 when '08' then value end ) as c8,
max(case code3 when '09' then code3 else '' end ) as f9,
max(case code3 when '09' then value end ) as c9,
max(case code3 when '10' then code3 else '' end ) as f10,
max(case code3 when '10' then value end ) as c10,
max(case code3 when '11' then code3 else '' end ) as f11,
max(case code3 when '11' then value end ) as c11,
max(case code3 when '12' then code3 else '' end ) as f12,
max(case code3 when '12' then value end ) as c12
from tab group by code1,code2
tim_spac 2008-01-03
- 打赏
- 举报
另一种思路。
将结果：
放到 "<table>" 和 "</table>"当中。
create table T(code1 nvarchar(5), code2 int, code3 nvarchar(2), value numeric(18,4) )
insert T select 'A09',1,'01',0.0000
insert T select 'A09',1,'02',null
insert T select 'A09',1,'03',null
insert T select 'A09',1,'04',null
insert T select 'A09',1,'05',null
insert T select 'A09',1,'06',null
insert T select 'A09',1,'07',null
insert T select 'A09',1,'08',null
insert T select 'A09',1,'09',null
insert T select 'A09',1,'10',null
insert T select 'A09',1,'11',null
insert T select 'A09',1,'12',null
insert T select 'A0A',1,'01',1.9
insert T select 'A0A',1,'02',null
insert T select 'A0A',1,'03',null
insert T select 'A0A',1,'04',1.2
insert T select 'A0A',1,'05',null
insert T select 'A0A',1,'06',null
insert T select 'A0A',1,'07',null
insert T select 'A0A',1,'08',null
insert T select 'A0A',1,'09',null
insert T select 'A0A',1,'10',null
insert T select 'A0A',1,'11',null
insert T select 'A0A',1,'12',null
go
declare @c3 table (code3 nvarchar(2))
insert into @c3 select distinct code3 from T order by code3
select
case when c3.code3='01' then '<tr><td>'+T.code1+'</td><td>'+cast(T.code2 as varchar)+'</td>' else '' end
+'<td>'+isnull(cast(T.value as varchar),' ')+'</td>'
+case when c3.code3='12' then '</tr>' else '' end
from @c3 c3
left join T on t.code3=c3.code3
order by code1, code2, c3.code3
go
drop table T
go
将结果：
<tr><td>A09</td><td>1</td><td>0.0000</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td></tr>
<tr><td>A0A</td><td>1</td><td>1.9000</td>
<td> </td>
<td> </td>
<td>1.2000</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td></tr>
放到 "<table>" 和 "</table>"当中。
中国风 2008-01-03
- 打赏
- 举报
create table T(code1 nvarchar(5), code2 int, code3 nvarchar(2), value numeric(18,4) )
insert T select 'A09',1,'01',0.0000
insert T select 'A09',1,'02',null
insert T select 'A09',1,'03',null
insert T select 'A09',1,'04',null
insert T select 'A09',1,'05',null
insert T select 'A09',1,'06',null
insert T select 'A09',1,'07',null
insert T select 'A09',1,'08',null
insert T select 'A09',1,'09',null
insert T select 'A09',1,'10',null
insert T select 'A09',1,'11',null
insert T select 'A09',1,'12',null
insert T select 'A0A',1,'01',1.9
insert T select 'A0A',1,'02',null
insert T select 'A0A',1,'03',null
insert T select 'A0A',1,'04',1.2
insert T select 'A0A',1,'05',null
insert T select 'A0A',1,'06',null
insert T select 'A0A',1,'07',null
insert T select 'A0A',1,'08',null
insert T select 'A0A',1,'09',null
insert T select 'A0A',1,'10',null
insert T select 'A0A',1,'11',null
insert T select 'A0A',1,'12',null
go
declare @s nvarchar(4000),@i int
select @s='',@i=1
while @i<=12
select @s=@s+',F'+rtrim(@i)+'=max(case when code3='''+right(100+@i,2)+''' then code3 else '''' end),C'
+rtrim(@i)+'=max(case when code3='''+right(100+@i,2)+''' then value end)',@i=@i+1
--print @s--显示字符串
exec( 'select code1,code2'+@s+' from T group by code1,code2')
code1 code2 F1 C1 F2 C2 F3 C3 F4 C4 F5 C5 F6 C6 F7 C7 F8 C8 F9 C9 F10 C10 F11 C11 F12 C12
----- ----------- ---- -------------------- ---- -------------------- ---- -------------------- ---- -------------------- ---- -------------------- ---- -------------------- ---- -------------------- ---- -------------------- ---- -------------------- ---- -------------------- ---- -------------------- ---- --------------------
A09 1 01 .0000 02 NULL 03 NULL 04 NULL 05 NULL 06 NULL 07 NULL 08 NULL 09 NULL 10 NULL 11 NULL 12 NULL
A0A 1 01 1.9000 02 NULL 03 NULL 04 1.2000 05 NULL 06 NULL 07 NULL 08 NULL 09 NULL 10 NULL 11 NULL 12 NULL
警告: 聚合或其它 SET 操作消除了空值。中国风 2008-01-03
- 打赏
- 举报
--Code3有大小关系时
max(case code3 when '01' then code3 else '' end ) as f1,
加一个C1..C2就行了
max(case code3 when '01' then value end ) as C1,
max(case code3 when '01' then code3 else '' end ) as f1,
加一个C1..C2就行了
max(case code3 when '01' then value end ) as C1,
