现在有client_group_relation表：client_id group_id client_relation_type
c001 g001 primary
c002 goo1 sec
c003 g002 primary
c004 g002 sec
c005 g002 thir
现在要查询出下面的结果,其实client_id 是该组下的所有用户用|分开，并且要先primary,然后sec,最后thirgroup_id client_id
g001 c001 | c002
g002 c003 | c004 |c005
c001 g001 primary
c002 goo1 sec
c003 g002 primary
c004 g002 sec
c005 g002 thir
现在要查询出下面的结果,其实client_id 是该组下的所有用户用|分开，并且要先primary,然后sec,最后thirgroup_id client_id
g001 c001 | c002
g002 c003 | c004 |c005
from (with a as (select client_id, group_id, client_relation_type
from client_group_relation
group by group_id, client_id, client_relation_type
order by client_relation_type)
select group_id, wmsys.wm_concat(client_id) client_id
from a
group by group_id)
select group_id,
max(sys_connect_by_path(client_id,'|'))
from (select client_id,
group_id,
row_number() over(partition by group_id order by decode(client_relation_type,'primary',1,'sec',2,'thir',3,4)) rn
from tb)
start with rn=1
connect by prior rn=rn-1 and prior group_id=group_id
group by group_idGROU CLIENT_ID
---- --------------------
g002 |c003|c004|c005
g001 |c001|c002
有表tb, 如下：id value----- ------1 aa1 bb2 aaa2 bbb2 ccc需要得到结果：id values------ -----------1 aa,bb2 aaa,bbb,ccc即， group by id, 求 value 的和（字符串相加）1. 旧的解决方法-- 1. 创建处理函数CREATE FUNCTION dbo.f_str(@id int)RETURNS varchar(8000)ASBEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = @r + ',' + value FROM tb WHERE id=@id RETURN STUFF(@r, 1, 1, '')ENDGO-- 调用函数SELECt id, values=dbo.f_str(id) FROM tb GROUP BY id-- 2. 新的解决方法 -- 示例数据DECLARE @t TABLE(id int, value varchar(10))INSERT @t SELECT 1, 'aa'UNION ALL SELECT 1, 'bb'UNION ALL SELECT 2, 'aaa'UNION ALL SELECT 2, 'bbb'UNION ALL SELECT 2, 'ccc'-- 查询处理SELECT *FROM( SELECT DISTINCT id FROM @t)AOUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM @t N WHERE id = A.id FOR XML AUTO ), '<N value="', ','), '"/>', ''), 1, 1, ''))N/*--结果id values----------- ----------------1 aa,bb2 aaa,bbb,ccc(2 行受影响)--*/--各种字符串分函数--3.3.1 使用游标法进行字符串合并处理的示例。--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理--定义结果集表变量DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))--定义游标并进行合并处理DECLARE tb CURSOR LOCALFORSELECT col1,col2 FROM tb ORDER BY col1,col2DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)OPEN tbFETCH tb INTO @col1,@col2SELECT @col1_old=@col1,@s=''WHILE @@FETCH_STATUS=0BEGIN IF @col1=@col1_old SELECT @s=@s+','+CAST(@col2 as varchar) ELSE BEGIN INSERT @t VALUES(@col1_old,STUFF(@s,1,1,'')) SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1 END FETCH tb INTO @col1,@col2ENDINSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))CLOSE tbDEALLOCATE tb--显示结果并删除测试数据SELECT * FROM @tDROP TABLE tb/*--结果col1 col2---------- -----------a 1,2b 1,2,3--*/GO/*==============================================*/--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3GO--合并处理函数CREATE FUNCTION dbo.f_str(@col1 varchar(10))RETURNS varchar(100)ASBEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+','+CAST(col2 as varchar) FROM tb WHERE col1=@col1 RETURN(STUFF(@re,1,1,''))ENDGO--调用函数SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1--删除测试DROP TABLE tbDROP FUNCTION f_str/*--结果col1 col2---------- -----------a 1,2b 1,2,3--*/GO/*==============================================*/--3.3.3 使用临时表实现字符串合并处理的示例--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3--合并处理SELECT col1,col2=CAST(col2 as varchar(100)) INTO #t FROM tbORDER BY col1,col2DECLARE @col1 varchar(10),@col2 varchar(100)UPDATE #t SET @col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END, @col1=col1, col2=@col2SELECT * FROM #t/*--更新处理后的临时表col1 col2---------- -------------a 1a 1,2b 1b 1,2b 1,2,3--*/--得到最终结果SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1/*--结果col1 col2---------- -----------a 1,2b 1,2,3--*/--删除测试DROP TABLE tb,#tGO/*==============================================*/--3.3.4.1 每组 <=2 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'c',3--合并处理SELECT col1, col2=CAST(MIN(col2) as varchar) +CASE WHEN COUNT(*)=1 THEN '' ELSE ','+CAST(MAX(col2) as varchar) ENDFROM tbGROUP BY col1DROP TABLE tb/*--结果col1 col2 ---------- ----------a 1,2b 1,2c 3--*/--3.3.4.2 每组 <=3 条记录的合并--处理的数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',1UNION ALL SELECT 'a',2UNION ALL SELECT 'b',1UNION ALL SELECT 'b',2UNION ALL SELECT 'b',3UNION ALL SELECT 'c',3--合并处理SELECT col1, col2=CAST(MIN(col2) as varchar) +CASE WHEN COUNT(*)=3 THEN ',' +CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar) ELSE '' END +CASE WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar) ELSE '' ENDFROM tb aGROUP BY col1DROP TABLE tb/*--结果col1 col2---------- ------------a 1,2b 1,2,3c 3--*/GOif not object_id('A') is null drop table AGoCreate table A([id] int,[cname] nvarchar(2))Insert Aselect 1,N'张三' union allselect 2,N'李四' union allselect 3,N'王五' union allselect 4,N'蔡六'Go--> --> if not object_id('B') is null drop table BGoCreate table B([id] int,[cname] nvarchar(5))Insert Bselect 1,N'1,2,3' union allselect 2,N'3,4'Gocreate function F_str(@cname nvarchar(100))returns nvarchar(100)asbegin select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',','+@cname+',')>0return @cnameendgoselect [id],dbo.F_str([cname])[cname] from Bid cname----------- ----------------------------------------------------------------------------------------------------1 张三,李四,王五2 王五,蔡六(2 個資料列受到影響)
spbm afield jg shrq
0100135 零售价格 7.0000 2007-04-16 15:05:56.250
0100135 会员价 7.0000 2007-04-16 15:05:56.250
0100135 末批进价 5.5000 2007-04-16 15:05:56.250
0100135 零售价格 7.5000 2007-12-19 09:07:55.903
0100135 末批进价 6.5000 2007-12-19 09:07:55.903
还有很多spbm不一样的记录，但每个spbm都按上面那样处理 在建立新表，存放一条记录时，不同的afield，要增加相应的字段
最终形成的记录:spbm,(不同的afield),（相同afield，shrq最大的,yjg的值填到新增的相应字段）,相同afield最大的shrq 如上表，形成的记录应该为
spbm 零售价格 会员价 末批进价 shrq
0100135 7.5 7 6.5 2007-12-19 09:07:55.903 这个比较复杂，这样的sql语句应该怎么写呀，我折腾不出来，请各位高手帮我想想办法？ //------------------------------------------------------------------------------
例子:我现在有两个表第一个表
ID NAME BOOK DATA
1 JOKE SHUXUE 2004-12-12
...第二个表S_ID ID S_LIKEMAN picre 0001 1 小张 12
0002 1 小李 13
我现在要怎么写SQL语句.才出现
BOOK S_LIKEMAN picre S_LIKEMAN picre
shuxue 小张 12 小李 13
意思就是书只能显示一次,他们的联系人都要显示.create table tb1(id int,name varchar(20),book varchar(20),date datetime)
insert tb1 values(1,'JOKE','SHUXUE','2004-12-12')create table tb2(s_id varchar(10),id int,s_likeman varchar(20),pricre int)
insert tb2 values('0001',1,'小张',12)
insert tb2 values('0002',1,'小李',13)declare @sql varchar(8000)
set @sql='select a.book'
select @sql=@sql+',max(case when b.s_likeman='''+s_likeman+''' then b.s_likeman end) s_likeman,
max(case when b.pricre='+cast(pricre as varchar)+' then b.pricre end) pricre'
from (select distinct s_likeman,pricre from tb2) aexec (@sql+' from tb1 a,tb2 b where a.id=b.id group by a.book')drop table tb1,tb2/*
book s_likeman pricre s_likeman pricre
-------------------- -------------------- ----------- -------------------- -----------
SHUXUE 小李 13 小张 12
*/ //---------------------------------------------------------------------------------------------create table a(spbm varchar(10),afield varchar(10),jg decimal(20,4),shrq datetime)
insert a select '0100135','零售价格', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'会员价', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'末批进价', 5.5000 ,'2007-04-16 15:05:56.250'
union all select '0100135', '零售价格', 7.5000 ,'2007-12-19 09:07:55.903'
union all select '0100135', '末批进价', 6.5000 ,'2007-12-19 09:07:55.903' declare @sql varchar(8000)
set @sql='select spbm,'
select @sql=@sql+'max(case when afield='''+afield+''' then jg end) ['+afield+'],' from a group by afieldset @sql=@sql+'max(shrq) shrq from a group by spbm'
exec(@sql)--result
/*spbm 会员价 零售价格 末批进价 shrq
---------- ---------------------- ---------------------- ---------------------- ------------------------------------------------------
0100135 7.0000 7.5000 6.5000 2007-12-19 09:07:55.903
*/ //---------------------------------------------------------------------------------------create table tb(spbm varchar(10),afield varchar(10),jg decimal(20,4),shrq datetime)
insert tb select '0100135','零售价格', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'会员价', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'末批进价', 5.5000 ,'2007-04-16 15:05:56.250'
union all select '0100135', '零售价格', 7.5000 ,'2007-12-19 09:07:55.903'
union all select '0100135', '末批进价', 6.5000 ,'2007-12-19 09:07:55.903'
go
select spbm ,
max(case afield when '零售价格' then jg else 0 end) '零售价格',
max(case afield when '会员价' then jg else 0 end) '会员价',
max(case afield when '末批进价' then jg else 0 end) '末批进价',
max(shrq) shrq
from tb
group by spbmdrop table tb/*
spbm 零售价格 会员价 末批进价 shrq
---------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
0100135 7.5000 7.0000 6.5000 2007-12-19 09:07:55.903(1 行受影响)
*/
//-----------------------------------------------------------------------------------------create table tb(spbm varchar(10),afield varchar(10),jg decimal(20,4),shrq datetime)
insert tb select '0100135','零售价格', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'会员价', 7.0000 ,'2007-04-16 15:05:56.250'
union all select '0100135' ,'末批进价', 5.5000 ,'2007-04-16 15:05:56.250'
union all select '0100135', '零售价格', 7.5000 ,'2007-12-19 09:07:55.903'
union all select '0100135', '末批进价', 6.5000 ,'2007-12-19 09:07:55.903'
go--静态SQL,指afield值固定为这三种。
select spbm ,
max(case afield when '零售价格' then jg else 0 end) '零售价格',
max(case afield when '会员价' then jg else 0 end) '会员价',
max(case afield when '末批进价' then jg else 0 end) '末批进价',
max(shrq) shrq
from tb
group by spbm
/*
spbm 零售价格 会员价 末批进价 shrq
---------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
0100135 7.5000 7.0000 6.5000 2007-12-19 09:07:55.903(1 行受影响)
*/--动态SQL，指afield值不固定。
declare @sql varchar(8000)
set @sql = 'select spbm'
select @sql = @sql + ' , max(case afield when ''' + afield + ''' then jg else 0 end) [' + afield + ']'
from (select distinct afield from tb) as a
set @sql = @sql + ',max(shrq) shrq from tb group by spbm'
exec(@sql)
/*
spbm 会员价 零售价格 末批进价 shrq
---------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
0100135 7.0000 7.5000 6.5000 2007-12-19 09:07:55.903(1 行受影响)
*/drop table tb
//---------------------------------------------------------------------select spbm, max(零售价格), max(会员价), max(末批进价), max(shrq)
from
(
select spbm,
case when afield = '零售价格' then max(jg) as 零售价格,
case when afield = '会员价' then max(jg) as 会员价,
case when afield = '末批进价' then max(jg) as 末批进价,
max(shrq)
from
table_1 group by spbm, afield
) a
group by spbm//----------------------------------------------------------declare @sql varchar(6000)
set @sql='select spbm'
select @sql=@sql+',max(case afield when '''+afield+''' then jg else 0 end)['+afield+']'
from (select distinct afield from ta) as t
select @sql=@sql+',max(shrq) from ta group by spbm'
exec(@sql)
create table client_group_relation
(
client_id varchar2(20),
group_id varchar2(20),
client_relation_type varchar2(20)
);insert into client_group_relation values('c001', 'g001', 'primary');
insert into client_group_relation values('c002', 'g001', 'sec');
insert into client_group_relation values('c003', 'g002', 'primary');
insert into client_group_relation values('c004', 'g002', 'sec');
insert into client_group_relation values('c005', 'g002', 'thir');
commit;select group_id, max(client_id) as client_id
from (select group_id,
replace(wmsys.wm_concat(client_id) over (partition by group_id order by client_relation_type) ,
',',
'|') as client_id
from client_group_relation)
group by group_id;