22,300
社区成员




select c.[month],c.product_name,c.target_name,
c.product_item_name ZXNAME,c.zx ZXVALUE,
'' CJNAME,0 CJVALUE,
'' YJNAME,0 YJVALUE
from (
select [month],product_name,target_name,
max(zx) zx,
max(cj) cj,
max(total) total
from
(select
datepart(mm,user_time) [month],product_name,target_name,product_item_name,
count(*) zx,
count(case when state='SUCCESS' then state end) cj,
sum(sum_money) total
from vi_sale_view
where datepart(yyyy,user_time)=2010
and project_pkid<>0
group by datepart(mm,user_time),product_name,target_name,product_item_name
) a
group by [month],product_name,target_name
) b
left join
(select
datepart(mm,user_time) [month],product_name,target_name,product_item_name,
count(*) zx,
count(case when state='SUCCESS' then state end) cj,
sum(sum_money) total
from vi_sale_view
where datepart(yyyy,user_time)=2010
and project_pkid<>0
group by datepart(mm,user_time),product_name,target_name,product_item_name
) c on b.[month]=c.[month] and b.product_name=c.product_name
and b.target_name=c.target_name and b.zx=c.zx
union all
select c.[month],c.product_name,c.target_name,
'' ZXNAME,0 ZXVALUE,
c.product_item_name CJNAME,c.cj CJVALUE,
'' YJNAME,0 YJVALUE
from (
select [month],product_name,target_name,
max(zx) zx,
max(cj) cj,
max(total) total
from
(select
datepart(mm,user_time) [month],product_name,target_name,product_item_name,
count(*) zx,
count(case when state='SUCCESS' then state end) cj,
sum(sum_money) total
from vi_sale_view
where datepart(yyyy,user_time)=2010
and project_pkid<>0
group by datepart(mm,user_time),product_name,target_name,product_item_name
) a
group by [month],product_name,target_name
) b
left join
(select
datepart(mm,user_time) [month],product_name,target_name,product_item_name,
count(*) zx,
count(case when state='SUCCESS' then state end) cj,
sum(sum_money) total
from vi_sale_view
where datepart(yyyy,user_time)=2010
and project_pkid<>0
group by datepart(mm,user_time),product_name,target_name,product_item_name
) c on b.[month]=c.[month] and b.product_name=c.product_name
and b.target_name=c.target_name and b.cj=c.cj
union all
select c.[month],c.product_name,c.target_name,
'' ZXNAME,0 ZXVALUE,
'' CJNAME,0 CJVALUE,
c.product_item_name YJNAME,c.total YJVALUE
from (
select [month],product_name,target_name,
max(zx) zx,
max(cj) cj,
max(total) total
from
(select
datepart(mm,user_time) [month],product_name,target_name,product_item_name,
count(*) zx,
count(case when state='SUCCESS' then state end) cj,
sum(sum_money) total
from vi_sale_view
where datepart(yyyy,user_time)=2010
and project_pkid<>0
group by datepart(mm,user_time),product_name,target_name,product_item_name
) a
group by [month],product_name,target_name
) b
left join
(select
datepart(mm,user_time) [month],product_name,target_name,product_item_name,
count(*) zx,
count(case when state='SUCCESS' then state end) cj,
sum(sum_money) total
from vi_sale_view
where datepart(yyyy,user_time)=2010
and project_pkid<>0
group by datepart(mm,user_time),product_name,target_name,product_item_name
) c on b.[month]=c.[month] and b.product_name=c.product_name
and b.target_name=c.target_name and b.total=c.total
order by [month],product_name,target_name
with base as
(
select a.id, b.name, count(*)cn from syscolumns a, systypes b where a.xtype=b.xtype group by a.id, b.name
),
cte as
(
select *, min(cn)over(partition by id)cnn, max(cn)over(partition by id)cnx from base
)
select object_name(id)object_name, id,
min_name = case min(cnn) when min(cn) then stuff((select ','+name from cte where id=t.id and cn=cnn for xml path('')),1,1,'') end,
min_cn = min(cnn),
max_name = case max(cnx) when max(cn) then stuff((select ','+name from cte where id=t.id and cn=cnx for xml path('')),1,1,'') end,
max_cn = max(cnx)
from cte t group by id order by id
with cte as
(
select id, xtype, count(*)cn from syscolumns group by id,xtype
),
abc as
(
select *, max(cn)over(partition by id)cnx from cte
)
select top 3 id,
typex = max(case cn when cnx then xtype else '' end),
max(cn)
from abc group by id
/*
id typex
----------- ----- -----------
3 56 8
5 56 6
7 127 5
*/
;with cte as
(
select
datepart(mm,user_time) [month],
product_name,
target_name,
product_item_name,
count(*) zx,
count(case when state='SUCCESS' then state end) cj,
sum(sum_money) total
from vi_sale_view
where datepart(yyyy,user_time)=2010 and project_pkid<>0
group by datepart(mm,user_time),product_name,target_name,product_item_name
)
select [month],product_name,target_name,
ZXNAME = max(case zx when max(t.zx) then stuff((select ','+product_item_name from cte where [month]=t.[month] and product_name=t.product_name and target_name=t.target_name and zx=max(t.zx) for xml path('')),1,1,'') else '' end),
ZXVALUE = max(t.zx),
CJNAME = max(case cj when max(t.cj) then stuff((select ','+product_item_name from cte where [month]=t.[month] and product_name=t.product_name and target_name=t.target_name and zx=max(t.cj) for xml path('')),1,1,'') else '' end),
CJVALUE = max(t.cj),
YJNAME = max(case total when max(t.total) then stuff((select ','+product_item_name from cte where [month]=t.[month] and product_name=t.product_name and target_name=t.target_name and zx=max(t.total) for xml path('')),1,1,'') else '' end),
YJVALUE = max(t.total)
from cte t group by [month],product_name,target_name
;with base as
(
select
datepart(mm,user_time) [month],
product_name,
target_name,
product_item_name,
count(*) zx,
count(case when state='SUCCESS' then state end) cj,
sum(sum_money) total
from vi_sale_view
where datepart(yyyy,user_time)=2010 and project_pkid<>0
group by datepart(mm,user_time),product_name,target_name,product_item_name
),
cte as
(
select *,
max(zx)over(partition by [month],product_name,target_name)zxx,
max(cj)over(partition by [month],product_name,target_name)cjx,
max(total)over(partition by [month],product_name,target_name)totalx
from base
)
select [month],product_name,target_name,
ZXNAME = max(case zx when zxx then stuff((select ','+product_item_name from cte where [month]=t.[month] and product_name=t.product_name and target_name=t.target_name and zx=zxx for xml path('')),1,1,'') else '' end),
ZXVALUE = max(zx),
CJNAME = max(case cj when cjx then stuff((select ','+product_item_name from cte where [month]=t.[month] and product_name=t.product_name and target_name=t.target_name and cj=cjx for xml path('')),1,1,'') else '' end),
CJVALUE = max(cj),
YJNAME = max(case total when totalx then stuff((select ','+product_item_name from cte where [month]=t.[month] and product_name=t.product_name and target_name=t.target_name and total=totalx for xml path('')),1,1,'') else '' end),
YJVALUE = max(total)
from cte t group by [month],product_name,target_name
;with cte as
(
select
datepart(mm,user_time) [month],
product_name,
target_name,
product_item_name,
count(*) zx,
count(case when state='SUCCESS' then state end) cj,
sum(sum_money) total
from vi_sale_view
where datepart(yyyy,user_time)=2010 and project_pkid<>0
group by datepart(mm,user_time),product_name,target_name,product_item_name
)
select [month],product_name,target_name,
ZXNAME = max(case zx when max(zx) then product_item_name else '' end),
ZXVALUE = max(zx),
CJNAME = max(case cj when max(cj) then product_item_name else '' end),
CJVALUE = max(cj),
YJNAME = max(case total when max(total) then product_item_name else '' end),
YJVALUE = max(total)
from cte group by [month],product_name,target_name
可以先用一个CTE得出2楼的结果 然后再结合下面的方法
合并列值
--*******************************************************************************************
表结构,数据如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加)
1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--=============================================================================
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + value FROM tb WHERE id=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_strUnite
go
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为 2 行)
*/
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb
/*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
*/
--SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
drop table tb
declare @table table
(
month int,product_name varchar(2),
target_name varchar(2),ZXNAME varchar(2),ZXVALUE int,
CJNAME varchar(2),CJVALUE int,YJNAME varchar(2),YJVALUE int
)
insert into @table
select 1,'AA','a1',null,0,'aa',2,null,0 union all
select 1,'AA','a1','bb',3,null,0,null,0 union all
select 1,'AA','a1',null,0,null,0,'cc',44 union all
select 1,'BB','b2','dd',3,null,0,null,0 union all
select 1,'BB','b2','de',3,null,0,null,0 union all
select 1,'BB','b2',null,0,null,0,'ee',5 union all
select 1,'BB','b2',null,0,'ff',3,null,0
select
max(month) as [month],
product_name,
max(target_name) as target_name,
min(ZXNAME) as ZXNAME,max(ZXVALUE) as ZXVALUE,
max(CJNAME) as CJNAME,max(CJVALUE) as CJVALUE,
max(YJNAME) as YJNAME,max(YJVALUE) as YJVALUE
from @table group by product_name
/*
month product_name target_name ZXNAME ZXVALUE CJNAME CJVALUE YJNAME YJVALUE
----------- ------------ ----------- ------ ----------- ------ ----------- ------ -----------
1 AA a1 bb 3 aa 2 cc 44
1 BB b2 dd 3 ff 3 ee 5
*/