具有不同条件的SQL案例查询 [英] Sql case query with different condition

问题描述

我有2个项目，项目名称'主席'和'篮子'和数量......我需要在哪里数量> 20 AND ITEM ='CHAIR';并且为了项目='篮子'，数​​量> '09'应该输出..



我失败了，NULLS即将来临......



我需要那些DQTY DQTY主题演示> 20和桌面篮子DQTY应该是> 9，它应该在我的输出中显示..我的输出中我不想要其他的大量数量



使用SQL-server-2014



我尝试过：

I HAVE 2 ITEM ,ITEM NAME 'CHAIR' AND 'BASKET' AND A QUANTITY...I NEED THAT WHERE QUANTITY > 20 AND ITEM='CHAIR'; AND FOR ITEM='BASKET' ,QUANTITY >'09' SHOULD COME IN OUTPUT..

I FAILED ,NULLS ARE COMING..

I NEED THAT FOR ITEM CHAIR DQTY SHOULD SHOW >20 AND FOR ITEM BASKET DQTY SHOULD BE > 9,,IT SHOULD BE SHOWED IN MY OUTPUT..I DON'T WANT OTHER DQTY NUMBER IN MY OUTPUT

USING SQL-server-2014

What I have tried:

SELECT 
CASE WHEN op.dqty >  '20' AND ti.SubCategoryName ='CHAIR' THEN ti.SubCategoryName 
 WHEN op.dqty >  '9' AND ti.SubCategoryName ='BASKET' THEN ti.SubCategoryName  ELSE NULL END AS SubCategoryName  ,


TI.SubCategoryName,op.site,sasitename,symn,dqty,count(distinct(orna)) as orna
from ABCD

WHERE TI.SubCategoryName in ('CHAIR' ,'BASKET') 
GROUP BY TI.SubCategoryName,op.site,sasitename,symn,dqty;

推荐答案

如何将数量与20和9等字符串进行比较？

只需删除''并确保您的列数据类型为整数。如果数据类型不是整数，则在sql中将列数据转换为整数，并确保数据不是整数格式，o / p为0.然后显然0> 20和0> 9对于非整数值始终为false你会得到nulls.Please up vote如果这会帮助你。

How can you compare quantity with a string like '20' and '9'?
Just Remove '' and make sure your column data type is integer. if datatype is not integer then in sql convert your column data to integer and make sure for data which are not in integer format, o/p it as 0.Then obviously 0>20 and 0>9 will always false for non integer values and you will get nulls.Please up vote if this helps you out.

你使用op.dqty和ti.SubCategoryName，这意味着你有两个表。首先检查没有大小写条件，你的普通查询是否返回所有数据。另外明智地提供带有join子句的完整查询，以便清楚地理解表关系。

You use op.dqty AND ti.SubCategoryName that means u have two tables. First check without case condition that ur normal query return all data or not. other wise provide the full query with join clause so that it clearly understandable the table relation.

根据你的查询，我写下以下脚本。请检查一下。

As per ur query, i write down the below script. please check this.

CREATE TABLE item
(
	Id					int,
	SubCategoryName		nvarchar(100)
)
go
insert into item values (1,'CHAIR')
go
insert into item values (2,'BASKET')
go
insert into item values (3,'TOOL')
go
CREATE TABLE ABCD
(
	ID			INT,
	CategoryId	Int,
	[site]		nvarchar(100),
	dqty		nvarchar(100),
	sasitename	nvarchar(100),
	symn		nvarchar(100),
	orna		int
)
GO
insert into abcd values (1,1,'00',25,'ss','22',1)
go
insert into abcd values (1,1,'00',15,'ss1','221',1)
go
insert into abcd values (1,2,'00',25,'ss2','222',2)
go
insert into abcd values (1,2,'00',18,'ss3','223',2)
go
insert into abcd values (1,1,'00',45,'ss4','224',1)
go
insert into abcd values (1,3,'00',25,'ss5','225',0)
go
insert into abcd values (1,3,'00',25,'ss6','226',0)
go
SELECT 
	CASE	WHEN op.dqty > 20 AND ti.SubCategoryName ='CHAIR' THEN ti.SubCategoryName 
			WHEN op.dqty > 9 AND ti.SubCategoryName ='BASKET' THEN ti.SubCategoryName 
	ELSE NULL END AS SubCategoryName , 
	TI.SubCategoryName,
	op.[site],
	op.sasitename,
	op.symn,
	op.dqty,
	count(distinct(orna)) as orna 
from ABCD op 
inner join item ti on op.CategoryId=ti.id
WHERE TI.SubCategoryName in ('CHAIR' ,'BASKET') 
GROUP BY TI.SubCategoryName,op.[site],op.sasitename,op.symn,op.dqty;

希望这能解决你的问题。如果这有助于你，请投票。

Hope this will solve ur problem. Please up vote if this helps you out.

这篇关于具有不同条件的SQL案例查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！