如何写这样的一条统计语句，急！在线等

[小小暴徒]

表table1
字段category，type，id
category中存放的如：(浙江,宁波,北仑),(浙江,杭州,下城区)
如表中有数据：
category type id
浙江,宁波,北仑 Sell 1
浙江,杭州,下城区 Sell 2
浙江,绍兴,上虞 Buy 3
浙江,宁波,余姚 Co_op 4
浙江,温州 Co_op 5
江苏,南京 Sell 6
江苏,苏州 Sell 7
江苏,镇江 Co_op 8
安徽,合肥 Sell 9
.
.
.
.
.
结果就是要统计每个省份的Sell，Buy，Co_op，以及总数
如结果：
省份 Sell Buy Co_op Total
浙江 2 1 2 5
江苏 2 0 1 3
安徽 1 0 0 1
.
.
.
.
.
.

[dobear_0922]

--> 测试数据: @table1
declare @table1 table (category varchar(32),type varchar(8),id int)
insert into @table1
select '浙江,宁波,北仑','Sell',1 union all
select '浙江,杭州,下城区','Sell',2 union all
select '浙江,绍兴,上虞','Buy',3 union all
select '浙江,宁波,余姚','Co_op',4 union all
select '浙江,温州','Co_op','5' union all
select '江苏,南京','Sell','6' union all
select '江苏,苏州','Sell','7' union all
select '江苏,镇江','Co_op','8' union all
select '安徽,合肥','Sell','9'

--select * from @table1

select left(category, charindex(',', category+',')-1) as 省份
		, Sell=sum(case type when 'Sell' then 1 else 0 end)
		, Buy=sum(case type when 'Buy' then 1 else 0 end)
		, Co_op=sum(case type when 'Co_op' then 1 else 0 end)
		, Total=count(1)
from @table1
group by left(category, charindex(',', category+',')-1)

/*
省份                               Sell        Buy         Co_op       Total
-------------------------------- ----------- ----------- ----------- -----------
安徽                               1           0           0           1
江苏                               2           0           1           3
浙江                               2           1           2           5

(3 row(s) affected)
*/

[nzperfect]

--> 测试数据: [table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1] (category varchar(400),type varchar(40),id varchar(6))
insert into [table1]
select '浙江,宁波,北仑','Sell',1 union all
select '浙江,杭州,下城区','Sell',2 union all
select '浙江,绍兴,上虞','Buy',3 union all
select '浙江,宁波,余姚','Co_op',4 union all
select '浙江,温州','Co_op','5' union all
select '江苏,南京','Sell','6' union all
select '江苏,苏州','Sell','7' union all
select '江苏,镇江','Co_op','8' union all
select '安徽,合肥','Sell','9'


--declare @sql varchar(max),@sql2 varchar(max)
declare @sql varchar(8000),@sql2 varchar(8000)
set @sql='select left(category,charindex('','',category)-1) as category, '
set @sql2=''
select @sql2=@sql2+'sum(case when type='''+type+''' then 1 else 0 end) as '''+ type+''','  
from( select distinct type from [table1])as a order by type 
set @sql=@sql+left(@sql2,len(@sql2)-1)+',count(*) as total from [table1] group by left(category,charindex('','',category)-1)'
print(@sql)
exec(@sql)
/*
安徽	0	0	1	1
江苏	0	1	2	3
浙江	1	2	2	5
*/

[dobear_0922]

--> 测试数据: @table1
declare @table1 table (category varchar(32),type varchar(8),id int)
insert into @table1
select '浙江,宁波,北仑','Sell',1 union all
select '浙江,杭州,下城区','Sell',2 union all
select '浙江,绍兴,上虞','Buy',3 union all
select '浙江,宁波,余姚','Co_op',4 union all
select '浙江,温州','Co_op','5' union all
select '江苏,南京','Sell','6' union all
select '江苏,苏州','Sell','7' union all
select '江苏,镇江','Co_op','8' union all
select '安徽,合肥','Sell','9'

--select * from @table1

select left(category, charindex(',', category+',')-1) as 省份
		, Sell=sum(case type when 'Sell' then 1 else 0 end)
		, Buy=sum(case type when 'Buy' then 1 else 0 end)
		, Co_op=sum(case type when 'Co_op' then 1 else 0 end)
		, Total=count(1)
from @table1
group by left(category, charindex(',', category+',')-1)
order by min(id)

/*
省份                               Sell        Buy         Co_op       Total
-------------------------------- ----------- ----------- ----------- -----------
浙江                               2           1           2           5
江苏                               2           0           1           3
安徽                               1           0           0           1

(3 row(s) affected)
*/

[-晴天]

select category = left(category,PATINDEX('%,%',category)),
  sell=(case when type=sell then 1 else 0 end),
  buy=(case when type=buy then 1 else 0 end),
  co_op=(case when type=buy then 1 else 0 end)
into #T
from table1
select category,sum(sell) as sell,sum(buy) as buy,sum(co_op) as co_op
  from #T group by category

[Herb2]

 
/******************************************************************************/
/*回复：20080613002总：00071                                                   */
/*主题：分类汇总后行转列                                                                  */
/*作者：二等草                                                                            */
/******************************************************************************/

set nocount on

--数据--------------------------------------------------------------------------
 
create table [t] ([category] varchar(16),[type] varchar(5),[id] int)
 insert into [t] select '浙江,宁波,北仑','Sell',1
 insert into [t] select '浙江,杭州,下城区','Sell',2
 insert into [t] select '浙江,绍兴,上虞','Buy',3
 insert into [t] select '浙江,宁波,余姚','Co_op',4
 insert into [t] select '浙江,温州','Co_op',5
 insert into [t] select '江苏,南京','Sell',6
 insert into [t] select '江苏,苏州','Sell',7
 insert into [t] select '江苏,镇江','Co_op',8
 insert into [t] select '安徽,合肥','Sell',9
go

--代码--------------------------------------------------------------------------
declare @sql varchar(8000)
select @sql =' select 省份=case when charindex('','',category) >0'
            +' then left(category,charindex('','',category)-1) else category end '
select @sql = @sql+',sum(case when type = '''+type+''' then 1 else 0 end) as ['+type+']' from 
       (select distinct type from t ) a
select @sql = @sql +',count(*) as total from t group by case when charindex('','',category) >0'
            +' then left(category,charindex('','',category)-1) else category end '

exec(@sql)
go

/*结果--------------------------------------------------------------------------
省份               Buy         Co_op       Sell        total       
---------------- ----------- ----------- ----------- ----------- 
安徽                         0           0           1           1 
江苏                         0           1           2           3 
浙江                         1           2           2           5 

--清除------------------------------------------------------------------------*/
drop table t

[Herb2]

 
/******************************************************************************/
/*回复：20080613002总：00071                                                   */
/*主题：分类汇总后行转列                                                                  */
/*作者：二等草                                                                            */
/******************************************************************************/

set nocount on

--数据--------------------------------------------------------------------------
 
create table [t] ([category] varchar(16),[type] varchar(5),[id] int)
 insert into [t] select '浙江,宁波,北仑','Sell',1
 insert into [t] select '浙江,杭州,下城区','Sell',2
 insert into [t] select '浙江,绍兴,上虞','Buy',3
 insert into [t] select '浙江,宁波,余姚','Co_op',4
 insert into [t] select '浙江,温州','Co_op',5
 insert into [t] select '江苏,南京','Sell',6
 insert into [t] select '江苏,苏州','Sell',7
 insert into [t] select '江苏,镇江','Co_op',8
 insert into [t] select '安徽,合肥','Sell',9
go

--代码--------------------------------------------------------------------------
declare @sql varchar(8000)
select @sql =' select 省份=case when charindex('','',category) >0'
            +' then left(category,charindex('','',category)-1) else category end '
select @sql = @sql+',sum(case when type = '''+type+''' then 1 else 0 end) as ['+type+']' from 
       (select id = min(id),type from t group by type  ) a order by id
select @sql = @sql +',count(*) as total from t group by case when charindex('','',category) >0'
            +' then left(category,charindex('','',category)-1) else category end order by min(id)'

exec(@sql)
go

/*结果--------------------------------------------------------------------------
省份               Sell        Buy         Co_op       total       
---------------- ----------- ----------- ----------- ----------- 
浙江                         2           1           2           5 
江苏                         2           0           1           3 
安徽                         1           0           0           1 
--清除------------------------------------------------------------------------*/
drop table t

[sweetweiwei]

--try
create table a
(
category nvarchar(50),
type varchar(10)
)
insert into a
select
N'浙江,宁波,北仑' ,'Sell' union all select
N'浙江,杭州,下城区','Sell' union all select
N'浙江,绍兴,上虞' ,'Buy' union all select
N'浙江,宁波,余姚' ,'Co_op' union all select
N'浙江,温州' ,'Co_op' union all select
N'江苏,南京' ,'Sell' union all select
N'江苏,苏州' ,'Sell' union all select
N'江苏,镇江' ,'Co_op' union all select
N'安徽,合肥' ,'Sell'

if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b

select c.Col2,
sum(case Col1 when 'Sell' then 1 ELSE 0 end) as 'Sell',
sum(case Col1 when 'Buy' then 1 ELSE 0 end) as 'Buy',
sum(case Col1 when 'Co_op' then 1 ELSE 0 end) as 'Co_op',
count(Col1) as 'count'
from
(Select a.type as 'Col1','Col2'=substring(a.category,b.ID,charindex(',',a.category+',',b.ID)-b.ID)
from a,#Num b
where charindex(',',','+a.category,b.ID)=b.ID) c
group by c.Col2
order by c.Col2
结果
Col2 Sell Buy Co_op count
------------------------------------------ ----------- ----------- ----------- -----------
上虞 0 1 0 1
下城区 1 0 0 1
余姚 0 0 1 1
北仑 1 0 0 1
南京 1 0 0 1
合肥 1 0 0 1
宁波 1 0 1 2
安徽 1 0 0 1
杭州 1 0 0 1
江苏 2 0 1 3
浙江 2 1 2 5
温州 0 0 1 1
绍兴 0 1 0 1
苏州 1 0 0 1
镇江 0 0 1 1

(15 row(s) affected)

[-晴天]

create table [table1] (category varchar(40),type varchar(10),id varchar(6))
insert into [table1]
select '浙江,宁波,北仑','Sell',1 union all
select '浙江,杭州,下城区','Sell',2 union all
select '浙江,绍兴,上虞','Buy',3 union all
select '浙江,宁波,余姚','Co_op',4 union all
select '浙江,温州','Co_op','5' union all
select '江苏,南京','Sell','6' union all
select '江苏,苏州','Sell','7' union all
select '江苏,镇江','Co_op','8' union all
select '安徽,合肥','Sell','9'
select category = left(category,PATINDEX('%,%',category)-1),
  sell=(case when type='sell' then 1 else 0 end),
  buy=(case when type='buy' then 1 else 0 end),
  co_op=(case when type='co_op' then 1 else 0 end)
into #T
from table1
select category,sum(sell) as sell,sum(buy) as buy,sum(co_op) as co_op
  from #T group by category
go
drop table table1,#T

/*category                                 sell        buy         co_op
---------------------------------------- ----------- ----------- -----------
安徽                                       1           0           0
江苏                                       2           0           1
浙江                                       2           1           2

(3 行受影响)

*/

[nzperfect]

declare @sql varchar(max),@sql2 varchar(max)
set @sql2=''
select @sql2=@sql2+'['+type+'],' from (select distinct type from [table1]) as a order by type desc
set @sql='
;with t as(
	select left(category,charindex('','',category)-1) as category,type from [table1]
)
select *
from t
pivot(
	count(type)
	for type 
	in('+left(@sql2,len(@sql2)-1)+')
) as p
'	
exec(@sql)
/*
category sell co_op Buy 
----------------------
安徽	1	0	0
江苏	2	1	0
浙江	2	2	1
*/

这个写法,谁来给补充下.

[nzperfect]

--> 测试数据: [table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1] (category varchar(400),type varchar(40),id varchar(6))
insert into [table1]
select '浙江,宁波,北仑','Sell',1 union all
select '浙江,杭州,下城区','Sell',2 union all
select '浙江,绍兴,上虞','Buy',3 union all
select '浙江,宁波,余姚','Co_op',4 union all
select '浙江,温州','Co_op','5' union all
select '江苏,南京','Sell','6' union all
select '江苏,苏州','Sell','7' union all
select '江苏,镇江','Co_op','8' union all
select '安徽,合肥','Sell','9'


--declare @sql varchar(max),@sql2 varchar(max)
declare @sql varchar(8000),@sql2 varchar(8000)
set @sql='select left(category,charindex('','',category)-1) as category, '
set @sql2=''
select @sql2=@sql2+'sum(case when type='''+type+''' then 1 else 0 end) as '''+ type+''','  
from( select distinct type from [table1])as a order by type 
set @sql=@sql+left(@sql2,len(@sql2)-1)+',count(*) as total from [table1] group by left(category,charindex('','',category)-1)'
print(@sql)
exec(@sql)
/*

category sell co_op Buy total
----------------------
安徽	1	0	0	1
江苏	2	1	0	3
浙江	2	2	1	5

*/

declare @sql varchar(max),@sql2 varchar(max)
set @sql2=''
select @sql2=@sql2+'['+type+'],' from (select distinct type from [table1]) as a order by type desc
set @sql='
;with a as(
	select left(category,charindex('','',category)-1) as category,type from [table1]
),b as
(
    select *
    from a
    pivot(
	    count(type)
	    for type 
	    in('+left(@sql2,len(@sql2)-1)+')
    ) as p
)
select e.*,f.total as total
from b as e inner join 
(
select category,count(*) as total from a group by category
) as f on e.category=f.category
'	
exec(@sql)
/*
category sell co_op Buy total
----------------------
安徽	1	0	0	1
江苏	2	1	0	3
浙江	2	2	1	5
*/

[dobear_0922]

--> 测试数据: table1
create table table1 (category varchar(32),type varchar(8),id int)
insert into table1
select '浙江,宁波,北仑','Sell',1 union all
select '浙江,杭州,下城区','Sell',2 union all
select '浙江,绍兴,上虞','Buy',3 union all
select '浙江,宁波,余姚','Co_op',4 union all
select '浙江,温州','Co_op','5' union all
select '江苏,南京','Sell','6' union all
select '江苏,苏州','Sell','7' union all
select '江苏,镇江','Co_op','8' union all
select '安徽,合肥','Sell','9'

--select * from @table1

--try 1 直接查询
select left(category, charindex(',', category+',')-1) as 省份
		, Sell=sum(case type when 'Sell' then 1 else 0 end)
		, Buy=sum(case type when 'Buy' then 1 else 0 end)
		, Co_op=sum(case type when 'Co_op' then 1 else 0 end)
		, Total=count(1)
from table1
group by left(category, charindex(',', category+',')-1)
order by min(id)

/*
省份                               Sell        Buy         Co_op       Total
-------------------------------- ----------- ----------- ----------- -----------
浙江                               2           1           2           5
江苏                               2           0           1           3
安徽                               1           0           0           1

(3 row(s) affected)
*/

--try 2 拼动态语句
declare @sql nvarchar(4000)
set @sql='select left(category, charindex('','', category+'','')-1) as 省份 '
select @sql=@sql+', '+quotename(type)+'=sum(case type when '''+type+''' then 1 else 0 end)' 
from table1 group by type
set @sql=@sql+', Total=count(1)
from table1
group by left(category, charindex('','', category+'','')-1)
order by min(id)'

exec(@sql)

/*
省份                               Sell        Buy         Co_op       Total
-------------------------------- ----------- ----------- ----------- -----------
浙江                               2           1           2           5
江苏                               2           0           1           3
安徽                               1           0           0           1

(3 row(s) affected)
*/


drop table table1

[utpcb]

select category = left(category,PATINDEX('%,%',category)-1),
sell=(case when type='sell' then 1 else 0 end),
buy=(case when type='buy' then 1 else 0 end),
co_op=(case when type='co_op' then 1 else 0 end)
into #T
from table1
select category,sum(sell) as sell,sum(buy) as buy,sum(co_op) as co_op
from #T group by category
go
drop table table1,#T

[lgxyz]

[Quote=引用 3 楼 dobear_0922 的回复:]
SQL code--> 测试数据: @table1
declare @table1 table (category varchar(32),type varchar(8),id int)
insert into @table1
select '浙江,宁波,北仑','Sell',1 union all
select '浙江,杭州,下城区','Sell',2 union all
select '浙江,绍兴,上虞','Buy',3 union all
select '浙江,宁波,余姚','Co_op',4 union all
select '浙江,温州','Co_op','5' union all
select '江苏,南京','Sell','6' union all
select '江苏,苏州…
[/Quote]
好强，向你学习

[lixueming3000]

我感觉你们的这些有些不对,为什么没对后面的统计出来呢?
7楼的是统计出来的,但那个type不灵活?希望你们改一下,向你们学习