sql中的逗号分隔

问题描述

员工级别
----------- -------
101 1,2,3
102 2,3
103 1,3,5
...
...


我的总水平1,2,3,4,5

现在我想在级别列中计数1的数量.
在级别列中计数2个数字.

employeeid level
----------- -------
101 1,2,3
102 2,3
103 1,3,5
...
...


total my levels 1,2,3,4,5

now i want count no.of 1''s in level column..
count no.of 2''s in level column

推荐答案

Create table empotest
(Employeeid int,
Level varchar(100))

--insert
insert into empotest values (101,'1,2,3')
insert into empotest values (102,'1,2,3')
insert into empotest values (103,'4,5,6')

--My  sql query

drop table #temptest


DECLARE @Count INT
Declare @totalcount int
Declare @recordcount int
declare @string varchar(100)
declare @EmpId int
SET @Count = 0
set @recordcount=1
create table #temptest
(Empid int,
Level int)
select @totalcount=count(*) from empotest where Level is not null
select @totalcount
While @recordcount<=@totalcount
Begin
SET @Count = 0;
	With CTE_test as
	(select employeeid,level,Row_Number() over (order by EmployeeId asc) as rn from  empotest)
	select @String=level,@EmpId=employeeid from cte_test where rn=@recordcount

	WHILE @Count <= LEN(@String)
	BEGIN
		IF SUBSTRING(@String,@Count,1) >= '0'
		AND SUBSTRING(@String,@Count,1) <= '9'
		BEGIN
			INSERT into #temptest values (@EmpId,SUBSTRING(@String,@Count,1))

		END
	SET @Count = @Count + 1
	END
	SET @recordcount = @recordcount + 1

END
select Level,count(Empid) from #temptest group by level
drop table #temptest

表格:
101 1,2,3
102 2,3
105 4,5,6

输出

1 1
2 2
3 2
4 1
5 1
6 1

Table:
101 1,2,3
102 2,3
105 4,5,6

Output

11
22
32
41
51
61

select count(*) from table
where level like('%1%')

或类似的方法应该可以解决问题

or something like that should do the trick

SELECT COUNT(*) AS [Count], '1' AS [Level] FROM [YourTableName] WHERE Level Like '%1%'
UNION
SELECT COUNT(*) AS [Count], '2' AS [Level] FROM [YourTableName] WHERE Level Like '%2%'
UNION
SELECT COUNT(*) AS [Count], '3' AS [Level] FROM [YourTableName] WHERE Level Like '%3%'
UNION
SELECT COUNT(*) AS [Count], '4' AS [Level] FROM [YourTableName] WHERE Level Like '%4%'
UNION
SELECT COUNT(*) AS [Count], '5' AS [Level] FROM [YourTableName] WHERE Level Like '%5%'
ORDER BY [Level]

这篇关于sql中的逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！