您好我有以下表结构。 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 4SELECT 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
返回:
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)) = 0Otherwise 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 = 0returns:
1 2 4SELECT 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值为零
发布评论