id 人名 所投项
2 ddd 19, 36, 38, 43, 46, 49, 50
3 aaa 36, 38
4 ccc 43, 45“所投项”里面存的都是项的ID(VARCHAR)
需要一个存储过程是得到所有所投顶(split(所投项,","))的所有总和,就是总票数
然后还需要一个存储过程得到指定所投项的所有票数,比如指定要求出43投了多少,那么结果返回2
急哦。谢谢各位了!!
2 ddd 19, 36, 38, 43, 46, 49, 50
3 aaa 36, 38
4 ccc 43, 45“所投项”里面存的都是项的ID(VARCHAR)
需要一个存储过程是得到所有所投顶(split(所投项,","))的所有总和,就是总票数
然后还需要一个存储过程得到指定所投项的所有票数,比如指定要求出43投了多少,那么结果返回2
急哦。谢谢各位了!!
Select
SUM(Len(所投项) - Len(Replace(所投项, ',', '')) + 1) As 总票数
From
TEST
from T
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(*) DESCDROP TABLE #,tb/*
_COLUMN _COUNT
------- -----------
36 2
38 2
43 2
45 1
46 1
49 1
50 1
19 1(8 行受影响)
*/
Declare @Count Int
Select @X = 43
Select Count(*) From TEST Where CharIndex(', ' + Rtrim(@X) + ', ', ', ' + 所投项 + ', ') > 0--2
insert @a select 2,'ddd','19,36,43,46,49,50'
union all select 3,'aaa','36,38'
union all select 4,'ccc','43,45'
select sum(case when charindex(',43,',','+所投项+',')>0 then 1 else 0 end) from @a
(id Int,
人名 Varchar(10),
所投项 Varchar(100))
Insert TEST 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
--第一個可以不用存儲過程
Select
SUM(Len(所投项) - Len(Replace(所投项, ',', '')) + 1) As 总票数
From
TEST
GO--得到指定所投项的所有票数的函數
Create Function F_TEST(@X Int)
Returns Int
As
Begin
Declare @Count Int
Select @Count = Count(*) From TEST Where CharIndex(', ' + Rtrim(@X) + ', ', ', ' + 所投项 + ', ') > 0
Return @Count
End
GO
Select dbo.F_TEST(43) As 票数
GO
Drop Table TEST
Drop Function F_TEST
--Result
/*
总票数
11票数
2
*/