Sql DIsplaying值为零 [英] Sql DIsplaying values with zero
问题描述
您好我有以下表结构。
ingredientid nvalue
1 9
1 0
1 5
2 0
2 0
3 4
3 5
3 6
4 0
4 0
我有上表结构体。现在我想只在所有n值都为零时才显示成分s。例如,在我上面的表格中,当我写一个查询时,我应该得到输出为
INGREDIENTID
2
4
因为2和4所有的值都是零。如何执行此操作?
如果nvalue不能为负数,则可以使用此简单查询:SELECT ingredientid
FROM MyTable
GROUP BY ingredientid
HAVING SUM(Abs(nvalue) )= 0
否则我推荐Maciejs解决方案
<编辑>更新以反映CGs建议< / edit>
从[TableName]中选择不同的INGREDIENTID,其中nvalue =(从[TableName]中选择不同的nvalue,其中nvalue!= 1或nvalue!= 2或nvalue!= 3或nvalue!= 4或nvalue!= 5或nvalue!= 6或nvalue!= 7或nvalue!= 8或nvalue!= 9)
看看例子:
DECLARE @ tmp 表(ingredientid INT ,nvalue INT )
INSERT INTO @ tmp (ingredientid,nvalue)
VALUES ( 1 , 9 ),( 1 , 0 ),
( 1 , 5 ),( 2 , 0 ),
( 2 , 0 ),( 3 , 4 ),
( 3 , 5 ),( 3 , 6 ),
( 4 , 0 ),( 4 , 0 )
SELECT DISTINCT ingredientid
FROM @ tmp
WHERE nvalue = 0
返回:
1
2
4
SELECT T.ingredientid
FROM (
SELECT ingredientid,COUNT(ingredientid) AS CountOfIid
FROM @ tmp
GROUP BY ingredientid
) AS T INNER JOIN @tmp AS D ON T.ingredientid = D.ingredientid
< span class =code-keyword> WHERE D.nvalue = 0
GROUP BY T.ingredientid,T.CountOfIid
HAVING COUNT(T.ingredientid)= T.CountOfIid
返回:
2
4
Hi i have the following table structure.
ingredientid nvalue
1 9
1 0
1 5
2 0
2 0
3 4
3 5
3 6
4 0
4 0
I have the above table structure. Now I want to display the ingredient s only when all the n value are zero. For example in my above table when I write a query I should get the output as
INGREDIENTID
2
4
since 2 and 4 all the values are zero. How can I perform this operation?
If nvalue can't be negative,you can use this simple query:SELECT ingredientid FROM MyTable GROUP BY ingredientid HAVING SUM(Abs(nvalue)) = 0
Otherwise I'd recommend Maciejs solution
<Edit>Updated to reflect CGs suggestion</edit>
select distinct INGREDIENTID from [TableName] where nvalue = (select distinct nvalue from [TableName] where nvalue ! = 1 or nvalue ! = 2 or nvalue ! = 3 or nvalue ! = 4 or nvalue ! = 5 or nvalue ! = 6 or nvalue ! = 7 or nvalue ! = 8 or nvalue ! = 9)
Have a look at example:
DECLARE @tmp TABLE (ingredientid INT, nvalue INT) INSERT INTO @tmp (ingredientid, nvalue) VALUES (1, 9), (1, 0), (1, 5), (2, 0), (2, 0), (3, 4), (3, 5), (3, 6), (4, 0), (4, 0) SELECT DISTINCT ingredientid FROM @tmp WHERE nvalue = 0
returns:
1 2 4
SELECT T.ingredientid FROM ( SELECT ingredientid, COUNT(ingredientid) AS CountOfIid FROM @tmp GROUP BY ingredientid ) AS T INNER JOIN @tmp AS D ON T.ingredientid = D.ingredientid WHERE D.nvalue = 0 GROUP BY T.ingredientid, T.CountOfIid HAVING COUNT(T.ingredientid) = T.CountOfIid
returns:
2 4
这篇关于Sql DIsplaying值为零的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!