sql分类统计问题

[xulibing1016]

分类统计字段

zerendanwei

两新办|长丰村|派出所
南宙村|派出所
长丰村
长丰村

现在需要将 zerendanwei为多个的按"|"拆分后再统计各自的百分比
结果显示：
zerendanwei 百分比
两新办 x%
长丰村 x%
派出所 x%
南宙村 x%

[xuam]

用cte

[pengxuan]

--先拆分，后统计
if object_id('tb') is not null
   drop table tb
go
create table tb
(
 zerendanwei varchar(100)
)
go
insert into tb
select '两新办|长丰村|派出所' union all 
select '南宙村|派出所' union all 
select '长丰村' union all 
select '长丰村'
go
;with cte as(
select zerendanwei=substring(zerendanwei,number,charindex('|',zerendanwei+'|',number)-number) from tb a cross join master..spt_values where type='P' and number between 1 and len(zerendanwei) and substring('|'+zerendanwei,number,1)='|'
)
select zerendanwei,百分比=cast(cast(count(1)*100.0/(select count(1) from cte) as numeric(9,2)) as varchar)+'%' from cte group by zerendanwei
go
/*
zerendanwei                                                                                          百分比
---------------------------------------------------------------------------------------------------- -------------------------------
长丰村                                                                                                  42.86%
两新办                                                                                                  14.29%
南宙村                                                                                                  14.29%
派出所                                                                                                  28.57%

(4 行受影响)
*/

[AcHerat]

;with cte as
(
  select substring(a.zerendanwei,b.number,charindex('|',a.zerendanwei+'|',b.number)-b.number) as zerendanwei
  from tb a,master..spt_values b 
  where b.[type] = 'p' and b.number between 1 and len(a.zerendanwei)
     and substring('|'+a.zerendanwei,b.number,1) = '|'
)

select zerendanwei,ltrim(cast(count(*)*1./(select count(*) from cte) as decimal(12,2))) + '%' as per
from cte
group by zerendanwei

[快溜]

create table tb(zerendanwei varchar(30))
insert into tb
select '两新办|长丰村|派出所' union all
select '南宙村|派出所' union all
select '长丰村' union all
select '长丰村' 

with cte as
(
select zerendanwei=substring(a.zerendanwei,b.number,charindex('|',a.zerendanwei+'|',b.number)-b.number)
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.zerendanwei)
    and substring('|'+a.zerendanwei,b.number,1) = '|')

select  zerendanwei,rtrim(cast(COUNT(*)*100./(select COUNT(*) from cte) as numeric(18,2)))+'%' 
from cte group by zerendanwei

/*
zerendanwei                    
------------------------------ ------------------------------------------
长丰村                            42.86%
两新办                            14.29%
南宙村                            14.29%
派出所                            28.57%

(4 行受影响)

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-11-28 16:03:39
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([zerendanwei] varchar(20))
insert [tb]
select '两新办|长丰村|派出所' union all
select '南宙村|派出所' union all
select '长丰村' union all
select '长丰村'
--------------开始查询--------------------------
;with f as
(
Select
    zerendanwei=substring(a.zerendanwei,b.number,charindex('|',a.zerendanwei+'|',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.zerendanwei)
where
     substring('|'+a.zerendanwei,b.number,1)='|'
)
select zerendanwei,ltrim(cast(COUNT(1)*100.0/(select  COUNT(1) from tb) as DEC(18,2)))+'%' from f group by zerendanwei
----------------结果----------------------------
/* zerendanwei          
-------------------- ------------------------------------------
长丰村                  75.00%
两新办                  25.00%
南宙村                  25.00%
派出所                  50.00%

(4 行受影响)

*/

[勿勿]

没理解LZ的意思。看楼下的

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-11-28 16:03:39
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([zerendanwei] varchar(20))
insert [tb]
select '两新办|长丰村|派出所' union all
select '南宙村|派出所' union all
select '长丰村' union all
select '长丰村'
--------------开始查询--------------------------
;with f as
(
Select
    zerendanwei=substring(a.zerendanwei,b.number,charindex('|',a.zerendanwei+'|',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.zerendanwei)
where
     substring('|'+a.zerendanwei,b.number,1)='|'
)
select zerendanwei,ltrim(cast(COUNT(1)*100.0/(select  COUNT(1) from f) as DEC(18,2)))+'%' from f group by zerendanwei
----------------结果----------------------------
/*zerendanwei          
-------------------- ------------------------------------------
长丰村                  42.86%
两新办                  14.29%
南宙村                  14.29%
派出所                  28.57%

(4 行受影响)

*/

[xulibing1016]

SQL2000数据库

[zhangweitc123]

cte是什么

[pengxuan]

--SQL Server 2000就用临时表吧
if object_id('tb') is not null
   drop table tb
go
create table tb
(
 zerendanwei varchar(100)
)
go
insert into tb
select '两新办|长丰村|派出所' union all 
select '南宙村|派出所' union all 
select '长丰村' union all 
select '长丰村'
go
if object_id('tempdb..#t')is not null
   drop table #t
go
select zerendanwei=substring(zerendanwei,number,charindex('|',zerendanwei+'|',number)-number) into #t from tb a cross join master..spt_values where type='P' and number between 1 and len(zerendanwei) and substring('|'+zerendanwei,number,1)='|'
select zerendanwei,百分比=cast(cast(count(1)*100.0/(select count(1) from #t) as numeric(9,2)) as varchar)+'%' from #t group by zerendanwei
go
/*
zerendanwei                                                                                          百分比
---------------------------------------------------------------------------------------------------- -------------------------------
长丰村                                                                                                  42.86%
两新办                                                                                                  14.29%
南宙村                                                                                                  14.29%
派出所                                                                                                  28.57%

(4 行受影响)
*/

[pengxuan]

[Quote=引用 9 楼 zhangweitc123 的回复:]
cte是什么
[/Quote]
CTE 就是Common Table Expression ,公用表表达式
http://msdn.microsoft.com/zh-cn/library/ms190766(v=sql.90).aspx

[q465897859]

[中国风]

[Quote=引用 8 楼 xulibing1016 的回复:]

SQL2000数据库
[/Quote]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([zerendanwei] nvarchar(100))
Insert #T
select N'两新办|长丰村|派出所' union all
select N'南宙村|派出所' union all
select N'长丰村' union all
select N'长丰村'
Go


--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b



SELECT 
	[zerendanwei],STR(COUNT(1)*100.0/b.con,5,2)+'%' AS 百分比
FROM 
(Select zerendanwei=substring(a.[zerendanwei],b.ID,charindex('|',a.[zerendanwei]+'|',b.ID)-b.ID)
from   #T a,#Num b where  charindex('|','|'+a.[zerendanwei],b.ID)=b.ID)a --也可用 substring('|'+a.[zerendanwei],b.ID,1)='|'
CROSS JOIN (SELECT  SUM(LEN([zerendanwei])-LEN(REPLACE([zerendanwei],'|',''))+1) AS con FROM #T) AS b
GROUP BY a.[zerendanwei],b.con

/*
zerendanwei	百分比
南宙村	14.29%
派出所	28.57%
两新办	14.29%
长丰村	42.86%
*/

[xulibing1016]

非常感谢各位，问题解决了。答者有份