前题:
有两个表ji1,ji2
ji1中有dh列与ji2中dh列是一对多的关系。
例如:
ji1 ji2
dh title dh title objno
010203 xxx 010203 www 0
010203 fff null
......
就是按order by ADDRESS,Amount desc ,
然后取前两条记录,后面的用others 代替,汇总金额成第三条记录,
也就是说不管原始记录有多少条,我只能取金额最高的两条和其它的汇总成一条记录,DRDL01变为others 即可例如记录为:
ADDRESS_NUMBER CUSTOMER DRDL01 IMSRP1 BEFORETWOYEAR
1 10003051 ALPHA START LIMITED PO ......
表结构及数据如下:
ADDRESS CUSTOMER DRDL01 IMSRP1 Amount
1 10003051 ALPHA START LIMITED POLY COVERED CORE PC 1425501.63
2 10003051 ALPHA START LIMITED SPUN POLYESTER SP 404370.41
3 10003051 ALPHA START LIMITED TRILOBAL AIR ENTANGLED POLY TL 9700
4 10003051 ALPHA START LIMITED SAK COTTON CS 38.3
......
1 m 2m 1
2 m 3m 2
3 n 33m 1
怎么写得到下面的结果
m 2m,3m
n 33m
SQL code:
select col2,
stuff((select ','+col3 from tb where col2=t.col2 for xml path('')),1,1,'')
from tb t
group by col2
SQL code:
-------------------------- ......
表A
字段a 字段b
01 a
02 b
03 c
04 d
表B
字段a 字段c 字段X
01 a001 1
01 a002 2
01 a003 3
02 b001 1
02 b002 2
02 b003 3
03 c001 1
03 c002 2
03 c003 3
04 d001 1
......
字段 A B
100 ff
101 dd
102 gg
我想把两列合并 变成
100:ff
101:ff
102:gg
SQL code:
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (A int,B varchar(2))
in ......