select SSSJ,'小型机XH'=XH ,'小型机YT'=YT,SPACE(40) AS PC服务器XH,SPACE(40) AS PC服务器YT, SPACE(40) AS 磁盘阵列XH,SPACE(40) AS 磁盘阵列YT FROM TABLE1 WHERE LB='小型机' UNION ALL select SSSJ,SPACE(40) AS 小型机XH ,SPACE(40) AS 小型机YT ,XH AS PC服务器XH,YT AS PC服务器YT, SPACE(40) AS 磁盘阵列XH,SPACE(40) AS 磁盘阵列YT FROM TABLE1 WHERE LB='PC服务器' UNION ALL select SSSJ,SPACE(40) AS 小型机XH ,SPACE(40) AS 小型机YT,SPACE(40) AS PC服务器XH,SPACE(40) AS PC服务器YT,XH AS 磁盘阵列XH,YT AS 磁盘阵列YT FROM TABLE1 WHERE LB='磁盘阵列'
select a.SSSJ,b.xh||' '||b.yt "(LB='小型机'的 XH YT)",c.xh||' '||c.yt "(LB='PC服务器'的 XH YT)",d.xh||' '||d.yt "(LB='磁盘阵列'的 XH YT)" from (select distinct sssj from table1) a, (select * from table1 where trim(lb)='小型机') b, (select * from table1 where trim(lb)='PC服务器') c, (select * from table1 where trim(lb)='磁盘阵列') d where a.sssj=b.sssj(+) and a.sssj=b.sssj(+) and a.sssj=c.sssj(+) and a.sssj=d.sssj;
--示例--测试数据 create table TABLE1(LB varchar(10),XH varchar(10),YT varchar(20),SSSJ varchar(10)) insert TABLE1 select '小型机' ,'HP5405' ,'数据库服务A','001' union all select '小型机' ,'HP5405' ,'数据库服务B','001' union all select '磁盘阵列','HPVA7100','数据库文件' ,'001' union all select '小型机' ,'HP5470' ,'数据库服务A','002' union all select 'PC服务器','DELL4600','WEB发布' ,'002' union all select 'PC服务器','DELL4600','数据交换' ,'002' union all select '磁盘阵列','HPDA7000','文件共享' ,'002' union all select 'PC服务器','DELL4600','备用' ,'002' go--处理的存储过程(动态的,根据你表中的LB决定结果中的列数) create proc p_qry as set nocount on declare @s varchar(8000) set @s='' select @s=@s+',['+LB+'型号]=max(case LB when ''' +LB+''' then XH else '''' end),['+LB +'用途]=max(case LB when ''' +LB+''' then YT else '''' end)' from TABLE1 group by LB exec(' select gid=0,LB,XH,YT,SSSJ into #t from TABLE1 order by SSSJ,LB,XH declare @a varchar(50),@b varchar(50),@i int update #t set @i=case when @a=SSSJ and @b=LB then @i+1 else 1 end ,gid=@i,@a=SSSJ,@b=LB select SSSJ'+@s+' from #t group by SSSJ,gid order by SSSJ,gid ') go--调用 exec p_qry go--删除测试 drop table TABLE1 drop proc p_qry/*--测试结果(太长了,自己看)--*/
--如果LB固定,则这样处理就行了select gid=0,LB,XH,YT,SSSJ into #t from TABLE1 order by SSSJ,LB,XH declare @a varchar(50),@b varchar(50),@i int update #t set @i=case when @a=SSSJ and @b=LB then @i+1 else 1 end ,gid=@i,@a=SSSJ,@b=LB select SSSJ ,[小型机型号]=max(case LB when '小型机' then XH else '' end) ,[小型机用途]=max(case LB when '小型机' then YT else '' end) ,[PC服务器型号]=max(case LB when 'PC服务器' then XH else '' end) ,[PC服务器用途]=max(case LB when 'PC服务器' then YT else '' end) ,[磁盘阵列型号]=max(case LB when '磁盘阵列' then XH else '' end) ,[磁盘阵列用途]=max(case LB when '磁盘阵列' then YT else '' end) from #t group by SSSJ,gid order by SSSJ,gid drop table #t
邹建,也在Oracle 中混了?? 呵呵...:)
--为书写简洁,先建立了一个视图vCREATE OR REPLACE VIEW V AS select a.sssj,a.lb,a.xh,a.yt,rownum+1-b.no no from table1 a, ( select id,min(no) no from ( select sssj||lb id,rownum no from (select sssj,lb,xh,yt from table1 group by sssj,lb,xh,yt) ) group by id ) b where a.sssj||a.lb=b.idselect a.sssj,b.xh 小型机型号,b.yt 小型机用途,c.xh PC服务器型号,c.yt PC服务器用途,d.xh 磁盘阵列型号,d.yt 阵列用途 from ( select distinct sssj,no,sssj||no sn from v ) a, ( select v.*,sssj||no sn from v where lb='小型机' ) b, ( select v.*,sssj||no sn from v where lb='PC服务器' ) c, ( select v.*,sssj||no sn from v where lb='磁盘阵列' ) d where a.sn=b.sn(+) and a.sn=c.sn(+) and a.sn=d.sn(+)
测试结果SQL> select a.sssj,b.xh 小型机型号,b.yt 小型机用途,c.xh PC服务器型号,c.yt PC服务器用途,d.xh 磁盘阵列 型号,d.yt 阵列用途 from 2 ( 3 select distinct sssj,no,sssj||no sn from v 4 ) a, 5 ( 6 select v.*,sssj||no sn from v where lb='小型机' 7 ) b, 8 ( 9 select v.*,sssj||no sn from v where lb='PC服务器' 10 ) c, 11 ( 12 select v.*,sssj||no sn from v where lb='磁盘阵列' 13 ) d 14 where a.sn=b.sn(+) and a.sn=c.sn(+) and a.sn=d.sn(+) 15 /SSSJ 小型机型号 小型机用途 PC服务器型 PC服务器用 磁盘阵列型 阵列用途 ---------- ---------- ---------- ---------- ---------- ---------- ---------- 001 HP5405 数据库服务 HPVA7100 数据库文件 001 HP5405 数据库服务 002 HP5470 数据库服务 DELL4600 WEB发布 HPDA7000 文件共享 002 DELL4600 备用 002 DELL4600 数据交换SQL>
CREATE OR REPLACE VIEW V AS select a.sssj,a.lb,a.xh,a.yt,rownum+1-b.no no from table1 a, ( select id,min(no) no from ( select sssj||lb id,rownum no from (select sssj,lb,xh,yt from table1 group by sssj,lb,xh,yt) ) group by id ) b where a.sssj||a.lb=b.id中的group by sssj,lb,xh,yt 在实际应用中改成了 order by sssj||lb 就全部正确了。 谢谢各位DX的帮忙 :)
SPACE(40) AS 磁盘阵列XH,SPACE(40) AS 磁盘阵列YT FROM TABLE1 WHERE LB='小型机'
UNION ALL
select SSSJ,SPACE(40) AS 小型机XH ,SPACE(40) AS 小型机YT ,XH AS PC服务器XH,YT AS PC服务器YT,
SPACE(40) AS 磁盘阵列XH,SPACE(40) AS 磁盘阵列YT FROM TABLE1 WHERE LB='PC服务器'
UNION ALL
select SSSJ,SPACE(40) AS 小型机XH ,SPACE(40) AS 小型机YT,SPACE(40) AS PC服务器XH,SPACE(40) AS PC服务器YT,XH AS 磁盘阵列XH,YT AS 磁盘阵列YT FROM TABLE1 WHERE LB='磁盘阵列'
(select distinct sssj from table1) a,
(select * from table1 where trim(lb)='小型机') b,
(select * from table1 where trim(lb)='PC服务器') c,
(select * from table1 where trim(lb)='磁盘阵列') d
where a.sssj=b.sssj(+) and a.sssj=b.sssj(+) and a.sssj=c.sssj(+) and a.sssj=d.sssj;
这样的SQL语句把字段间所有的组合都列出来了 ,现在需要数据只出来一次
---------
002 HP5470 数据库服务A DELL4600 WEB发布 磁盘阵列 HPDA7000 文件共享
002 DELL4600 数据交换
002 DELL4600 备用
---------
向上面这样,没有的就直接空出来。拜托了
:)
显示出来的设备清单应该如下
记录号 SSSJ 小型机型号 小型机用途 PC服务器型号 PC服务器用途 磁盘阵列型号 阵列用途
-------------------------------------------
1 001 HP5405 数据库服务A NULL NULL HPVA7100 数据库文件
2 001 HP5405 数据库服务B NULL NULL 磁盘阵列1 用途1
3 001 小型机1 小 用途1 NULL NULL 磁盘阵列2 用途2
4 001 小型机2 小 用途2 NULL NULL NULL NULL
--------------------------------------------------------------------------------------
就像报表一样 不过内容要横向列出来 记录条数应该 类别 中最多那一项 有点像把3条SELECT语句出来的结果横向拼起来 不够的补空白
说穿了还是行转列的问题
如果SSSJ有999
每个SSSJ有几十个甚至几百个XH,咋办?
create table TABLE1(LB varchar(10),XH varchar(10),YT varchar(20),SSSJ varchar(10))
insert TABLE1 select '小型机' ,'HP5405' ,'数据库服务A','001'
union all select '小型机' ,'HP5405' ,'数据库服务B','001'
union all select '磁盘阵列','HPVA7100','数据库文件' ,'001'
union all select '小型机' ,'HP5470' ,'数据库服务A','002'
union all select 'PC服务器','DELL4600','WEB发布' ,'002'
union all select 'PC服务器','DELL4600','数据交换' ,'002'
union all select '磁盘阵列','HPDA7000','文件共享' ,'002'
union all select 'PC服务器','DELL4600','备用' ,'002'
go--处理的存储过程(动态的,根据你表中的LB决定结果中的列数)
create proc p_qry
as
set nocount on
declare @s varchar(8000)
set @s=''
select @s=@s+',['+LB+'型号]=max(case LB when '''
+LB+''' then XH else '''' end),['+LB
+'用途]=max(case LB when '''
+LB+''' then YT else '''' end)'
from TABLE1 group by LB
exec('
select gid=0,LB,XH,YT,SSSJ
into #t from TABLE1
order by SSSJ,LB,XH
declare @a varchar(50),@b varchar(50),@i int
update #t set @i=case when @a=SSSJ and @b=LB then @i+1 else 1 end
,gid=@i,@a=SSSJ,@b=LB
select SSSJ'+@s+' from #t group by SSSJ,gid order by SSSJ,gid
')
go--调用
exec p_qry
go--删除测试
drop table TABLE1
drop proc p_qry/*--测试结果(太长了,自己看)--*/
declare @a varchar(50),@b varchar(50),@i int
update #t set @i=case when @a=SSSJ and @b=LB then @i+1 else 1 end
,gid=@i,@a=SSSJ,@b=LB
select SSSJ
,[小型机型号]=max(case LB when '小型机' then XH else '' end)
,[小型机用途]=max(case LB when '小型机' then YT else '' end)
,[PC服务器型号]=max(case LB when 'PC服务器' then XH else '' end)
,[PC服务器用途]=max(case LB when 'PC服务器' then YT else '' end)
,[磁盘阵列型号]=max(case LB when '磁盘阵列' then XH else '' end)
,[磁盘阵列用途]=max(case LB when '磁盘阵列' then YT else '' end)
from #t group by SSSJ,gid order by SSSJ,gid
drop table #t
select a.sssj,a.lb,a.xh,a.yt,rownum+1-b.no no
from table1 a,
(
select id,min(no) no
from (
select sssj||lb id,rownum no
from (select sssj,lb,xh,yt from table1 group by sssj,lb,xh,yt)
)
group by id
) b
where a.sssj||a.lb=b.idselect a.sssj,b.xh 小型机型号,b.yt 小型机用途,c.xh PC服务器型号,c.yt PC服务器用途,d.xh 磁盘阵列型号,d.yt 阵列用途 from
(
select distinct sssj,no,sssj||no sn from v
) a,
(
select v.*,sssj||no sn from v where lb='小型机'
) b,
(
select v.*,sssj||no sn from v where lb='PC服务器'
) c,
(
select v.*,sssj||no sn from v where lb='磁盘阵列'
) d
where a.sn=b.sn(+) and a.sn=c.sn(+) and a.sn=d.sn(+)
型号,d.yt 阵列用途 from
2 (
3 select distinct sssj,no,sssj||no sn from v
4 ) a,
5 (
6 select v.*,sssj||no sn from v where lb='小型机'
7 ) b,
8 (
9 select v.*,sssj||no sn from v where lb='PC服务器'
10 ) c,
11 (
12 select v.*,sssj||no sn from v where lb='磁盘阵列'
13 ) d
14 where a.sn=b.sn(+) and a.sn=c.sn(+) and a.sn=d.sn(+)
15 /SSSJ 小型机型号 小型机用途 PC服务器型 PC服务器用 磁盘阵列型 阵列用途
---------- ---------- ---------- ---------- ---------- ---------- ----------
001 HP5405 数据库服务 HPVA7100 数据库文件
001 HP5405 数据库服务
002 HP5470 数据库服务 DELL4600 WEB发布 HPDA7000 文件共享
002 DELL4600 备用
002 DELL4600 数据交换SQL>
select a.sssj,a.lb,a.xh,a.yt,rownum+1-b.no no
from table1 a,
(
select id,min(no) no
from (
select sssj||lb id,rownum no
from (select sssj,lb,xh,yt from table1 group by sssj,lb,xh,yt)
)
group by id
) b
where a.sssj||a.lb=b.id中的group by sssj,lb,xh,yt 在实际应用中改成了 order by sssj||lb 就全部正确了。
谢谢各位DX的帮忙 :)