求助一SQL存储过程
id人名 所投项
2ddd 19, 36, 38, 43, 46, 49, 50
3aaa 36, 38
4ccc 43, 45
“所投项”里面存的都是项的ID（VARCHAR）
需要一个存储过程是得到所有所投顶（split(所投项, ", ")）的所有总和，就是总票数
然后还需要一个存储过程得到指定所投项的所有票数，比如指定要求出43投了多少，那么结果返回2
急哦。谢谢各位了！！
[解决办法]
--第一個可以不用存儲過程
Select
SUM(Len(所投项) - Len(Replace(所投项, ', ', ' ')) + 1) As 总票数
From
TEST
[解决办法]
2.
select sum(case when charindex( ',43, ', ', '+所投项+ ', ')> 0 then 1 else 0 end)
from T
[解决办法]
create table tb (id int, 人名 varchar(10),所投项 varchar(200))
insert into tb select 2, 'ddd ', '19,36,38,43,46,49,50 '
union all select 3, 'aaa ', '36,38 '
union all select 4, 'ccc ', '43,45 '
go
--方法一
DECLARE @S AS VARCHAR(8000)
SET @S= ' '
SELECT @S=@S+ 'UNION ALL SELECT ' ' '+REPLACE(所投项, ', ', ' ' ' AS _COLUMN UNION ALL SELECT ' ' ')+ ' ' ' AS _COLUMN '
FROM TB
SET @S= ' SELECT _COLUMN,COUNT(*) AS _COUNT FROM ( '+STUFF(@S,1,10, ' ')+ ' ) TB GROUP BY _COLUMN ORDER BY COUNT(*) DESC '
EXEC(@S)
go
--方法二
SELECT TOP 8000
id = IDENTITY(int, 1, 1)
INTO #
FROM syscolumns a, syscolumns b
SELECT
所投项=SUBSTRING(A.所投项, B.id, CHARINDEX( ', ', A.所投项 + ', ', B.id) - B.id),数量=COUNT(*)
FROM TB A, # B
WHERE SUBSTRING( ', ' + A.所投项, B.id, 1) = ', '
GROUP BY SUBSTRING(A.所投项, B.id, CHARINDEX( ', ', A.所投项 + ', ', B.id) - B.id)
ORDER BY COUNT(*) DESC
DROP TABLE #,tb
/*
_COLUMN _COUNT
------- -----------
36 2
38 2
43 2
45 1
46 1
49 1
50 1
19 1
(8 行受影响)
*/
