拼写查询问题 [英] spilting query problem

问题描述

在我的数据库中，一列存储的值为1,2,3,4,5.现在我想在条件中一个接一个地发送这些值

例如:从级别= 1
的员工中选择count(employeeid) 然后下一次我想发送2则3的条件...

在这里，我要计算级别为1且级别为2的员工的数量..

in my database one column storing values as 1,2,3,4,5 . now i want to send these values one by one in where condition

ex: select count(employeeid) from employee where level=1
then next time where comdition i want to send 2 then 3 ...

here i want count of employee who''s level is 1 and whose level is 2 ....

推荐答案

尝试以下方法

Try the below approach

CREATE TABLE #Temp
(
	EmployeeID INT,
	[Level] VARCHAR(50)
)


INSERT INTO #Temp
SELECT 101, ''1,2,3''
 UNION
SELECT 102, ''2,3''
 UNION
SELECT 103, ''1,3''


CREATE TABLE #Temp2
(
	[Level] INT
)

DECLARE @ColumnVal VARCHAR(100)
DECLARE @Level INT

SELECT @ColumnVal = isnull(@ColumnVal,'''') + [Level]  + '',''  FROM #Temp



WHILE CHARINDEX('','',@ColumnVal) != 0
BEGIN

SELECT @Level = SUBSTRING(@ColumnVal, 0,CHARINDEX('','',@ColumnVal))
SET @ColumnVal = SUBSTRING(@ColumnVal,CHARINDEX('','',@ColumnVal)+1,LEN(@ColumnVal))

	IF NOT EXISTS (SELECT 1 FROM #Temp2 WHERE Level = @Level)
	BEGIN

		INSERT INTO #Temp2
		SELECT @Level

	END

END


--SELECT * FROM #Temp2
--SELECT * FROM #Temp

SELECT COUNT(EmployeeID) AS EmployeeCount, Level 
FROM
(
	SELECT EmployeeID, T2.Level FROM #temp T1
	INNER JOIN #temp2 T2 ON 1 = 1
	WHERE T1.Level LIKE ''%'' + CAST(T2.Level AS VARCHAR) + ''%''
) T
GROUP BY Level

DROP TABLE #Temp
DROP TABLE #Temp2

尝试一下.

如果您将级别coulmn数据类型设置为int，请遵循此操作.

try this.

If you have level coulmn datatype as int, then follow this.

Select  count(employeeid) from employee group by level

否则请参考解决方案3

Else refer solution 3

尝试一下..

-我的桌子

try this..

--My table

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

--insert

--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')

-我的SQL查询

--My sql query

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

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

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

这篇关于拼写查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！