在案例查询中需要三列 [英] Need three column in case query

问题描述

这是我的SQL服务器查询，

如果使用三列我有3个条件1.不到2. between和3.大于

所以我应该如何将它们用于三列，如我所说的那样我放三个条件但是在括号之后就有了然后那么如何使用3列



我需要3参赛作品在状态栏如何做到这一点，这个查询只有两个条目将来

This is my SQL server query,
In case how to use three column i have 3 condition 1.less than 2.between and 3. greater than
So how Should i use them for three columns as see in WHen i put three condition but after when bracket there is Then and else so how to use 3 columns

I need 3 Entries IN STATUS COLUMN How to do that ,With this quer only two entries will come

select inum,supervisor,category,symn,belowthen2,between2and5,above5,dqty, (CASE 
 
     WHEN (belowthen2 < 2 and   between2and5 between 2 and 5 and above5 >5 ) THEN 'Above'  and ELSE 'Below' 

 END) as STATUS

from (

select inum,supervisor,category,symn,sum(belowthen2) as belowthen2,sum(between2and5) as between2and5,sum(above5) as above5,dqty from (
select inum,supervisor,category,symn,dqty,
CASE WHEN sum(dqty)  < 2 THEN dqty
ELSE 0 end as belowthen2 ,
CASE WHEN sum(dqty)  between 2 and 5 THEN dqty
ELSE 0 end as between2and5 ,
CASE WHEN sum(dqty)  > 5 THEN dqty
ELSE 0 end as above5 
 from (
 
 ...)

我尝试过的事情：



不知道，我只使用过2个条件但现在需要状态3个条件列

What I have tried:

No idea, I have used case with only 2 conditions yet but now need 3 condition in STATUS Column

推荐答案

您的代码中有一个错误

WHEN (belowthen2 < 2 and   between2and5 between 2 and 5 and above5 >5 ) THEN 'Above'  and ELSE 'Below' 

下移除和我已经突出显示了



有一些关于如何在 CASE 语句中有多个条件的好例子文档站点 - CASE（ Transact-SQL）| Microsoft Docs [ ^ ]



以下是使用此示例数据的一个简单示例：

Remove the and that I've highlighted

There are some good examples on how to have multiple conditions in a CASE statement on the documentation site - CASE (Transact-SQL) | Microsoft Docs[^]

Here is a trivial example using this sample data:

create table #example (a int)
insert into #example (a) values (0),(25), (50), (100)

此查询有4个条件

select CASE WHEN a < 25 THEN '1st Quartile'
			WHEN a < 50 THEN '2nd Quartile'
			WHEN a < 75 THEN '3rd Quartile'
			ELSE '4th Quartile' 
			END,a 
FROM #example

给出结果

1st Quartile	0
2nd Quartile	25
3rd Quartile	50
4th Quartile	100

这是一个稍微复杂的例子

Here is a slightly more complex example

create table #example2 (a int, b int, c int, d int)
insert into #example2 (a,b,c,d) values
(1,0,0,0), (2,1,0,0), (1,1,0,0), (1,0,0,1)

select CASE WHEN a = 1 and d = 0 THEN 'Condition 1'
			WHEN a = 1 and b = 1 and c = 1 and d = 1 THEN 'Condition 2'
			ELSE
				'No conditions met'
			END
from #example2

很遗憾，您还没有向我们提供完整的查询，也没有任何样本数据或预期结果，所以我无法帮助您解决具体问题

Unfortunately you haven't given us your complete query, nor any sample data or expected results so I can't help you with your specific problem

----sp_help QWERTY

select inum,supervisor,category,symn,belowthen2,between2and5,above5,dqty,


(CASE  WHEN (belowthen2 < 2 and between2and5 =0 and above5 =0 )  THEN 'belowthen2' else 
     case  when (between2and5 between 2 and 5 and belowthen2 =0 and above5=0) Then 'between2and5' else
	  case when  ( between2and5 =0 and belowthen2 =0 and above5>5)  then   'above5'
	  end 
	   end 
	   end 
	   ) as STATUS
from (
----
)o ;

这篇关于在案例查询中需要三列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！