如何在SQL Server 2008中使用拆分功能在条件之间编写 [英] how to write between condition by using split function in sql server 2008

问题描述

我有一个包含两列minexperience和maxexperience
的表
但是我通过使用split函数在值之间传递来传递唯一的一个参数@experience.

我正在编写查询，如下所示

ihave a table with two columns minexperience and maxexperience

but iam passing the only one parameter that is @experience by using split function to get between values.

I am writing the query as follows

select e.jobid,e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where minexperience and maxexperience between (select items from dbo.Split(@experience,','))

它显示以下错误

it shows the following error

An expression of non-boolean type specified in a context where a condition is expected, near 'and'.

或
如果我将查询写为

or
if i write the query as

select e.jobid,e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where minexperience >= (select items from dbo.Split('4,9',','))  and maxexperience <= (select items from dbo.Split(15,','))

然后将错误显示为

then it shows the error as

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

如何编写查询

how can i write the query

推荐答案

根据我对您的问题的理解，用户可以随机选择多个复选框，而您正在传递一个参数@experience.在这种情况下，您可以创建并执行动态sql查询并获得所需的结果.

给定以下解决方案，我还使用了这种格式的单个参数@experience.

Hi,

As per my understanding for your question, a user can select multiple check-boxes randomly and you are passing a single parameter @experience. In this case, you can create and execute a dynamic sql query and get desired result.

Given below solution, I have also used a single parameter @experience in this format.

@experience = 
	minimum_experience_of_first_checkbox + "," + maximum_experience_of_first_checkbox + ";" + 
	minimum_experience_of_second_checkbox + "," + maximum_experience_of_second_checkbox + ";" + 
	..
	..
	..
	minimum_experience_of_n_th_checkbox + "," + maximum_experience_of_n_th_checkbox + ";"

在这里，您可以看到我使用了两个分隔符.
，"用于最小和最大年份，而;"用于连接复选框.

此后，我们将只需要循环并准备我们的动态sql&执行它.

Here, you can see that I have used two separators.
"," is used for minimum and maximum years and ";" is used to join checkboxes.

After this, We will just have to put a loop and prepare our dynamic sql & execute it.

DECLARE @MainSQLQuery VARCHAR(MAX)
SET @MainSQLQuery = 'select e.jobid,e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where '

DECLARE @experience VARCHAR(MAX)
SET @experience = '1,2;4,5;8,10;'

DECLARE
	@whereQuery varchar(2000),
	@OrCondition varchar(5),
	@separatorCheckBox varchar(1),
	@separatorYear varchar(1),
	@remainingCheckBoxes varchar(2000),
	@splittedCheckBoxVal varchar(500),
	@maxYear varchar(200),
	@minYear varchar(50)
	
SELECT
	@whereQuery = '',
	@OrCondition = ' OR ',
	@separatorCheckBox = ';',
	@separatorYear = ','


IF CHARINDEX(@separatorCheckBox,@experience) <= 0
BEGIN
	SET @experience = @experience + @separatorCheckBox
END


SET @remainingCheckBoxes = @experience

--Create Dynamic SQL Where Query
WHILE ( LEN(@remainingCheckBoxes) > 0 )
BEGIN
	--Split Checkboxes
	SET @splittedCheckBoxVal = SUBSTRING(@remainingCheckBoxes,1,CHARINDEX(@separatorCheckBox,@remainingCheckBoxes)-1)
	SET @remainingCheckBoxes = SUBSTRING(@remainingCheckBoxes,CHARINDEX(@separatorCheckBox,@remainingCheckBoxes)+1,LEN(@remainingCheckBoxes)-1)
	
	--Spit to get minimum and maximum year
	SET @minYear = SUBSTRING(@splittedCheckBoxVal,1,CHARINDEX(@separatorYear,@splittedCheckBoxVal)-1)
	SET @maxYear = SUBSTRING(@splittedCheckBoxVal,CHARINDEX(@separatorYear,@splittedCheckBoxVal)+1,LEN(@splittedCheckBoxVal)-1)

	--Append OR condition
	SET @whereQuery = @whereQuery + @OrCondition + '(minexperience >= ' + @minYear + ' and maxexperience <= ' + @maxYear + ')'
END

--Remove Extra OR condition appended at first position due to while loop
SET @whereQuery = SUBSTRING(@whereQuery,LEN(@OrCondition)+1,LEN(@whereQuery))

--Append this with main query
SET @MainSQLQuery = @MainSQLQuery + @whereQuery

--Finally execute this dynamic sql to get value
EXEC(@MainSQLQuery)

希望这将有助于解决您的问题.

问候，
Ambicaprasad Maurya

Hope this will help to solve your problem.

Regards,
Ambicaprasad Maurya

更改查询并传递两个值@minexp和@maxexp，这样您的查询将更加简单

Change your query and pass two values @minexp and @maxexp so your queries will be much simpler

select e.jobid,e.jobtitle,e.jobposteddate,e.joblocation,e.minexperience,e.maxexperience,e.jobdescription, d.companyname from emppostingjobs e Inner join admregforemp d on e.comptypeid=d.comptypeid where minexperience >= @minexp and maxexperience <= @maxexp

这篇关于如何在SQL Server 2008中使用拆分功能在条件之间编写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！