多行合并为一行的SQL语句.docx
- 文档编号:11089166
- 上传时间:2023-05-29
- 格式:DOCX
- 页数:17
- 大小:21.99KB
多行合并为一行的SQL语句.docx
《多行合并为一行的SQL语句.docx》由会员分享,可在线阅读,更多相关《多行合并为一行的SQL语句.docx(17页珍藏版)》请在冰点文库上搜索。
多行合并为一行的SQL语句
多行合并为一行的SQL语句
目录
例一1
例二2
例三3
例四5
例五6
例六如何将多行数据合并成一行多列7
例七C#12
✧例一
表数据:
test
no q
------------
1 n1
1 n2
1 n3
1 n4
1 n5
3 t1
3 t2
3 t3
3 t4
3 t5
3 t6
2 m1
语句:
withtestas(
SELECT1ASNo,'N1'ASqFROMDualUNIONALL
SELECT1ASNo,'N2'ASqFROMDualUNIONALL
SELECT1ASNo,'N3'ASqFROMDualUNIONALL
SELECT1ASNo,'N4'ASqFROMDualUNIONALL
SELECT1ASNo,'N5'ASqFROMDualUNIONALL
SELECT3ASNo,'T1'ASqFROMDualUNIONALL
SELECT3ASNo,'T2'ASqFROMDualUNIONALL
SELECT3ASNo,'T3'ASqFROMDualUNIONALL
SELECT3ASNo,'T4'ASqFROMDualUNIONALL
SELECT3ASNo,'T5'ASqFROMDualUNIONALL
SELECT3ASNo,'T6'ASqFROMDualUNIONALL
SELECT2ASNo,'M1'ASqFROMDual
)
SELECTNo,Substr(Jg,2,Length(Jg))ASJg
FROM(SELECTNo,MAX(Sys_Connect_By_Path(q,','))ASJg
FROM(SELECTNo,
q,
Row_Number()Over(PARTITIONBYNoORDERBYNo,q)Rn
FROMTest)
STARTWITHRn=1
CONNECTBYRn-1=PRIORRn
ANDNo=No
GROUPBYNo)
谭工前面那个示例"CONNECTBYRn-1=PRIORRnANDNo=No"也应改成"CONNECTBYNo||'|'||(Rn-1)=PRIOR(NO||'|'||Rn)"结果才对。
另外这个SQL只适合在小数据集或索引结果中进行处理,不然要使用全表扫描效率不高。
输出:
1 ,N1,N2,N3,N4,N5
2 ,M1
3 ,T1,T2,T3,T4,T5,T6
✧例二
另解:
人员所属部门:
第一个部门为缺省部门
SELECT人员编号,
MIN(姓名)AS姓名,
Substr(MAX(To_Char(Length(部门),'99999999')||部门),11,2000)AS部门
FROM(SELECT人员编号,姓名,Sys_Connect_By_Path(部门,',')AS部门
FROM(SELECT姓名,
部门,
缺省,
人员编号,
Row_Number()Over(PARTITIONBY姓名ORDERBY人员编号,缺省DESC)Rn
FROM(SELECTc.姓名,
b.名称AS部门,
a.缺省,
c.编号AS人员编号,
b.编码AS部门编码
FROM部门人员a,部门表b,人员表c
WHEREa.部门id=b.Id
ANDa.人员id=c.Id
)t)
STARTWITHRn=1
CONNECTBY(人员编号||(Rn-1))=PRIOR(人员编号||Rn))
GROUPBY人员编号
ORDERBY部门
CONNECTBY(人员编号||(Rn-1))=PRIOR(人员编号||Rn)
这一句整很久才整出来,为了避免不等长的编号,下面的改进也许更合适:
CONNECTBY(人员编号||'|'||(Rn-1))=PRIOR(人员编号||'|'||Rn)
这样就不会出现错乱了.
✧例三
如何将一个表中的多行数据合并为一行新数据?
比如有表A
ID Field1 Field2 Field3
1 b 10.1 12.2
2 b 9.8 8.6
3 c 12.3 11.2
4 c 5.3 8.2
现在想生成下面这样的数据
ID Field1 Field2 Field3
1 b 10.1 12.2
2 b 9.8 8.6
3 c 12.3 11.2
4 c 5.3 8.2
5 b_all 19.9 20.8 注:
19.9=10.1+9.8 20.8=12.2+8.6
6 c_all 17.6 19.4
请问如何用SQL语句或存贮过程来实现?
Try:
Select * from 表A
Union all
Select 5,'b_all',sum(field2),sum(field3) from 表A where field1 = 'b'
group by 5,'b_all'
Union all
Select 6,'b_all',sum(field2),sum(field3) from 表A where field1 = 'c'
group by 5,'b_all'
――――
Select * from 表A
Union all
Select (select max(id) from 表A)+1 ,'b_all' ,sum(field2),sum(field3) from 表A where field1 = 'b'
Union all
Select (select max(id) from 表A)+2,'c_all',sum(field2),sum(field3) from 表A where field1 = 'c'
――――
select IDENTITY(INT,1,1) ID,T.* into #temp FROM
((select Field1,Field2,Field3 from 表A Union all
select Field1+'_all',sum(Field2),sum(Field3)
from 表A group by Field1)) T
select * from #temp
drop table #temp
―――――
测试:
create table 表A (id int,field1 char
(1),field2 numeric(10,1),field3 numeric(10,1))
insert 表A select 1 ,'b', 10.1, 12.2
union all select 2 ,'b', 9.8, 8.6
union all select 3 ,'c', 12.3, 11.2
union all select 4 ,'c', 5.3, 8.2
Select * from 表A
Union all
Select (select max(id) from 表A)+1 ,'b_all' ,sum(field2),sum(field3) from 表A where field1 = 'b'
Union all
Select (select max(id) from 表A)+2,'c_all',sum(field2),sum(field3) from 表A where field1 = 'c'
id field1 field2 field3
----------- ------ ---------------------------------------- ----------------------------------------
1 b 10.1 12.2
2 b 9.8 8.6
3 c 12.3 11.2
4 c 5.3 8.2
5 b_all 19.9 20.8
6 c_all 17.6 19.4
(所影响的行数为 6 行)
✧例四
如何把查询到的多行数据合并为一行?
楼主Chice_wxg(学)(习)2004-11-0713:
27:
22在MS-SQLServer/基础类提问
✧我需要类似以下功能:
update table1 set data=(select line from table2)
但是select如果返回多行显然无法直接进行这种操作,
请问如何解决?
谢谢~~~~~~~~~
data 和 line都是字符传。
比如:
table2中有很多记录(line)
id line
1 asdf
2 zxcv
3 lkjha
我希望最终例如:
update table1 where id=指定记录 set data=( .....不知道怎么写....)
最终:
table1中那条记录的data 字段的内容是类似"asdf zxcv lkjha"的形式。
也就是说要把table2 中的所有内容放到一个字符串中。
--这样的更新无法用一个语句实现
--你可以这样处理
declare @s varchar(8000)
set @s=''
select @s=@s+' '+line from table2
update table1 set data=stuff(@s,1,1,'') where id=指定记录Top
5楼zjcxc(邹建)回复于2004-11-0713:
48:
32得分30
--或者写个自定义函数
create function f_str()returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+' '+line from table2
return(stuff(@s,1,1,''))
end
go
--调用函数实现更新
update table1 set data=dbo.f_str() where id=指定记录
✧例五
[如何将多行记录合并为一行?
]
网友提问于2007年3月20日
有两张表A和B,A中一条记录对应B中多条记录,我想在查询结果中把B中多行的记录添加在A后面,比如:
原结构A.ID1,B.VALUE11
A.ID1,B.VALUE12
A.ID1,B.VALUE13
A.ID2,B.VALUE21
A.ID2,B.VALUE22
改变后的结构
A.ID1,B.VALUE11,B.VALUE12,B.VALUE13
A.ID2,B.VALUE21,B.VALUE22,null
--楼主在论坛里搜搜,很多的
createtabletb(IDvarchar(10),NAMEvarchar(10))
insertintotb
select'A.ID1','B.VALUE11'
unionallselect'A.ID1','B.VALUE12'
unionallselect'A.ID1','B.VALUE13'
unionallselect'A.ID2','B.VALUE21'
unionallselect'A.ID2','B.VALUE22'
go
createfunctiondbo.fc_str(@idvarchar(100))
returnsvarchar(100)
as
begin
declare@sqlvarchar(1000)
set@sql=''
select@sql=@sql+','+cast(nameasvarchar(100))fromtbwhereid=@id
returnstuff(@sql,1,1,'')
end
go
selectid,dbo.fc_str(id)asnamefromtbgroupbyid
droptabletb
dropfunctiondbo.fc_str
✧例六如何将多行数据合并成一行多列
楼主lamb218()2005-08-2911:
55:
28在MS-SQLServer/应用实例提问
数据:
编码 用户数
01 100
02 120
03 160
04 320
合并后的效果应如下:
100 120 160 320问题点数:
40、回复次数:
16Top
1楼wgsasd311(自强不息)回复于2005-08-2912:
26:
50得分15
declare @sql varchar(4000)
set @sql=''
select @sql=@sql+',['+ 编码+']=sum(case 编码 when '+ 编码+' then 用户数 else 0 end) from tbl'
set @sql='select '+right(@sql,len(@sql)-1)+ ' from tbl'
exec(@sql)Top
2楼jinjazz(近身剪)回复于2005-08-2912:
30:
18得分15
--建立测试环境
Create Table 表(编码 varchar(10),用户数 varchar(10))
--插入数据
insert into 表
select '01','100' union
select '02','120' union
select '03','160' union
select '04','320'
declare @str varchar(8000)
--测试语句
set @str=''
select @str=@str+用户数+' ' from 表
print @str
/*--输出
100 120 160 320
--*/
--删除测试环境
Drop Table 表
Top
3楼xueguang(xg)回复于2005-08-2912:
30:
27得分0
declare @a varchar(4000)
set @a=''
select @a=@a+' '+用户数 from 表
select stuff(@a,1,1,'')Top
4楼lamb218()回复于2005-08-2913:
04:
59得分0
你们实现的都是合并后一行一列,我要求把分成多列!
能实现吗?
Top
5楼wgsasd311(自强不息)回复于2005-08-2913:
21:
21得分0
楼主,你有没有看懂我代码,他实现的是一行多列,列数由编码个数动态确定。
Top
6楼jinjazz(近身剪)回复于2005-08-2913:
23:
19得分0
那你group byTop
7楼xueguang(xg)回复于2005-08-2913:
23:
40得分10
--分成多列一楼的就可以,如果编码有重复,可以这样写
declare @sql varchar(4000)
set @sql=''
select @sql=@sql+',['+ 编码+']=sum(case 编码 when '+ 编码+' then 用户数 else 0 end)' from (select distinct 编码 from 表) A
set @sql='select '+right(@sql,len(@sql)-1)+ ' from 表'
exec(@sql)
Top
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 合并 一行 SQL 语句