统计SQL的完善问题

[wing7742]

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

如上SQL得到结果
month product_name target_name ZXNAME ZXVALUE CJNAME CJVALUE YJNAME YJVALUE
----- ------------ ---------- --------- ---- ------ ------- ------ --------
1 AA a1 null 0 aa 2 null 0
1 AA a1 bb 3 null 0 null 0
1 AA a1 null 0 null 0 cc 44
1 BB b2 dd 3 null 0 null 0
1 BB b2 de 3 null 0 null 0
1 BB b2 null 0 null 0 ee 5
1 BB b2 null 0 ff 3 null 0
......
最终希望统计结果为
1 AA a1 bb 3 aa 2 cc 44
1 BB b2 dd,de 3 ff 3 ee 5

请问应该怎么对查询的数据统计

[叶子]

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
*/

[SQLCenter]

很长，慢慢看。

[wing7742]

TO: maco_wang

感谢你的帮助给了我一定的提示，不过你可能疏忽了不论MAX() MIN() 都只能取一个值，还有合并的问题

[--小F--]

可以先用一个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 

[wing7742]

TO: fredrickhu
已看过你提供的行合并解答
正在纠结中.........

[SQLCenter]

这个是没有合并的，取最大的product_item_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

[SQLCenter]

合并1

;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

[SQLCenter]

这样合并不知道行不行，你测试一下

;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

[wing7742]

TO: #14
消息 130，级别 15，状态 1，第 1 行
不能对包含聚合或子查询的表达式执行聚合函数。
????

[SQLCenter]

那只能#15了

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
*/

[SQLCenter]

#15也是错的，这里做了个例子，统计表/视图使用得最多和最少的数据类型，返回名称列表，测试通过，你照着改改#15的就可以了。

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

[wing7742]

TO：#19
嗯
在合并时#14、#15、#16都是同样错误
我想了个解决方案是把SQL分开成为4个表，再一一对比生成新表

现在尝试#19

[wing7742]

TO：#19
嗯
根据你给的方法和思路得到了理想结果，查询速度从50m缩至25m
thx

[wing7742]

采用5楼方案后效率过低

继续求解

[dawugui]

[Quote=引用 7 楼 wing7742 的回复:]
采用5楼方案后效率过低

继续求解
[/Quote]
如果是合并字符串的数据，没有效率高的方法，需求如此，别无他法。

[wing7742]

[Quote=引用 8 楼 dawugui 的回复:]
引用 7 楼 wing7742 的回复:
采用5楼方案后效率过低

继续求解

如果是合并字符串的数据，没有效率高的方法，需求如此，别无他法。
[/Quote]
嗯
那么能否在查询的时候进行处理呢？感觉SQL重复的很多？
这样想是不是很贪心了 - -#

[SQLCenter]

to #9楼

当然可以，有时间我看看，你自己也思考一下，不要听风听雨。

[wing7742]

[Quote=引用 10 楼 sqlcenter 的回复:]
to #9楼

当然可以，有时间我看看，你自己也思考一下，不要听风听雨。
[/Quote]
嗯 先谢了
对SQL不是很了解 一边看帮助一边修改 再尝试尝试吧

[SQLCenter]

你用的SQL是什么版本

[wing7742]

[Quote=引用 12 楼 sqlcenter 的回复:]
你用的SQL是什么版本
[/Quote]
2005