SQL Query用于为一列中值为0的行和另一列中的唯一ID选择记录

ID       amount account number(varchar)
5105     0.70   23423423
5105     0.0    null
5105     0.0    null
5104     0.0    null
5104     0.0    null
5104     0.0    null
5106     0.0    32132111
5106     0.0    null

I want to get the distinct id for which all of its amount value is 0.0 and account number is null . basicly from this table i want the result as 5104. Can anyone please help i am new to SQL.

我想获得其所有金额值均为0.0且帐号为空的独特ID。基本上从这个表我想要的结果为5104.任何人都可以请帮助我是SQL的新手。

4 个解决方案

#1

Select DISTINCT ID FROM TableName 
    GROUP BY ID
      HAVING SUM(amount)=0.0

Update for another condition( another column which is varchar. It should be null)

更新另一个条件（另一个列是varchar。它应该为null）

  Select DISTINCT ID FROM TableName 
      WHERE AnotherColumn IS NULL
        GROUP BY ID
          HAVING SUM(amount)=0.0

SQL Fiddle: http://sqlfiddle.com/#!2/145504/3

SQL小提琴：http：//sqlfiddle.com/#！2/145504/3

#2

Select DISTINCT ID FROM TableName 
GROUP BY ID
HAVING min(amount)=0.0 and max(amount)=0.0

#3

This works:

这有效：

SELECT ID, MAX(amount) FROM table_name GROUP BY ID HAVING MAX(amount)=0

Here's a fiddle

这是一个小提琴

#4

You can use

您可以使用

SELECT id,
       MIN(amount) AS minumum,
       MAX(amount) AS maximum
FROM your_table
GROUP BY id HAVING minimum = 0.0
AND maximum = 0.0

a simple sum wouldn't work in my opinion as you could have an amount of -1 and one of 1, with has a sum of 0.

在我看来，一个简单的总和不会起作用，因为你可以得到-1和1中的一个，总和为0。

Since you didn't write if you can have negative values, its necessary to check for the minimum too.

如果您没有写，如果您可以有负值，则必须检查最小值。

Addition for the new limitation:

增加新限制：

SELECT id,
       MIN(amount) AS min_value,
       MAX(amount) AS max_value,
       MAX(account) AS max_account
FROM your_table
GROUP BY id 
HAVING min_value = 0.0
AND max_value = 0.0
AND max_account IS null

#1

Select DISTINCT ID FROM TableName 
    GROUP BY ID
      HAVING SUM(amount)=0.0