选择没有NULL的不同的多个字段

I have a table with Value ID and Value

我有一个具有值ID和值的表

--------------
| id | value |
--------------
|  1 |  NULL |
--------------
|  1 |     A |
--------------
|  2 |  NULL |
--------------
|  2 |  NULL |
--------------
|  3 |     B |
--------------
|  3 |     B |
--------------
|  3 |     B |
--------------

I need to select distinct id and corresponding value from the table. When selecting the Id should be unique and if it is having multiple values in the value field it should retrieve only not NULL values

我需要从表中选择不同的id和相应的值。当选择Id时，它应该是唯一的，如果值字段中有多个值，那么它应该只检索不为空的值

So the result should be like below.

所以结果应该如下所示。

--------------
| id | value |    
--------------
|  1 |     A |
--------------
|  2 |  NULL |
--------------    
|  3 |     B |
--------------

How to achieve this? using SQL server 2005

如何实现呢?使用SQL server 2005

2 个解决方案

#1

You can use a regular GROUP BY.

你可以使用常规组。

The GROUP BY will

的组会

• eliminate the NULL value from 1 because other values are present.
• 从1中删除空值，因为存在其他值。
• retain the NULL value for 2 because it only has NULL values.
• 保留2的空值，因为它只有空值。

SQL Statement

SELECT  id
        , MIN(value)
FROM    YourTable
GROUP BY
        id

Test script

;WITH q (id, value) AS (
    SELECT 1, NULL
    UNION ALL SELECT 1, 'A'
    UNION ALL SELECT 2, NULL
    UNION ALL SELECT 2, NULL
    UNION ALL SELECT 3, 'B'
    UNION ALL SELECT 3, 'B'
    UNION ALL SELECT 3, 'B'
)
SELECT  id
        , MIN(value)
FROM    q       
GROUP BY
        id

#2

It's a bit convoluted, but it should do the trick:

这有点复杂，但它应该是这样的:

select distinct x.id, x.value
  from table x
 where x.value is not null 
    or not exists 
       (select y.id 
          from table y 
         where y.id = x.id 
           and y.value is not null)