主页 / dba / 问题 / 84297
Accepted
IT researcher
Asked: 2014-12-05 04:45:11 +0800 CST

在该 id 的另一列中选择具有相同 id 但为 null 和其他值的行

  • 772

我只想获取具有值NULL和其他值的行，而不是NULL特定用户名列。

如果该特定用户名的两行都为 null，或者两者都具有 null 以外的其他值，则它不应出现在输出中。如果相同的用户名有两行以上，并且具有 null 和其他一些值，那么它们应该出现。

下面是示例示例和输出。如何使用 sql 查询来完成？

+----------+-------+
| username | col2  |
+----------+-------+
| a        | abc   |
| a        | ef    |
| b        | null  |
| b        | null  |
| c        | der   |
| c        | null  |
+----------+-------+

输出

+----------+------+
| username | col2 |
+----------+------+
| c        | der  |
| c        | null |
+----------+------+
sql-server t-sql

6 个回答

  1. Best Answer

    您应该能够使用条件聚合来获取具有 incol2和null.

    我建议使用带有条件的 HAVING 子句。查询类似于：

    select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) = 1
  and sum(case when col2 is null then 1 else 0 end) = 1

    请参阅SQL Fiddle with Demo。此查询按每个用户名对您的数据进行分组，然后使用条件逻辑检查是否col2满足您想要的两个条件 - 其中col2不为空且 col2为空。

    然后，您可以在子查询等中使用它来获取username和col2值：

    select 
  t.username, 
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) = 1
    and sum(case when col2 is null then 1 else 0 end) = 1
) d
  on t.username = d.username

    请参阅SQL Fiddle with Demo。

    如果你有不止col2一行同时包含null另一个值，那么你只需要HAVING稍微改变这个子句：

    select 
  t.username, 
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) >= 1
    and sum(case when col2 is null then 1 else 0 end) >= 1
) d
  on t.username = d.username;

    请参阅带有演示的 SQL Fiddle

    • 12

  2. 另一种解决方案：

    SELECT Y1.*
FROM dbo.yourtable AS Y1
WHERE Y1.username = ANY
(
    SELECT Y2.username 
    FROM dbo.yourtable AS Y2
    WHERE Y2.col2 IS NULL
    INTERSECT
    SELECT Y3.username 
    FROM dbo.yourtable AS Y3
    WHERE Y3.col2 IS NOT NULL
);

    执行计划

    在类似的逻辑脉络中：

    SELECT Y.* 
FROM dbo.yourtable AS Y
WHERE EXISTS
    (
    SELECT * 
    FROM dbo.yourtable AS Y2 
    WHERE Y2.username = Y.username 
    AND Y2.col2 IS NULL
    )
AND EXISTS
    (
    SELECT * 
    FROM dbo.yourtable AS Y3 
    WHERE Y3.username = Y.username 
    AND Y3.col2 IS NOT NULL
    );

    执行计划

    完后还有：

    SELECT
    SQ1.username,
    SQ1.col2
FROM 
(
    SELECT
        Y.username, 
        Y.col2,
        MinCol2 = 
            MIN(CASE WHEN Y.col2 IS NULL THEN -1 ELSE 1 END) 
            OVER (PARTITION BY Y.username), 
        MaxCol2 = 
            MAX(CASE WHEN Y.col2 IS NULL THEN -1 ELSE 1 END) 
            OVER (PARTITION BY Y.username)
    FROM dbo.yourtable AS Y
) AS SQ1
WHERE 
    SQ1.MinCol2 = -SQ1.MaxCol2;

    执行计划

    • 8

  3. 只是另一种方法：

    ; WITH cte AS
  ( SELECT username, col2,
           cnt_all  = COUNT(*) OVER (PARTITION BY username),
           not_null = COUNT(col2) OVER (PARTITION BY username)
    FROM yourtable AS a
  )
SELECT username, col2
FROM cte
WHERE cnt_all > not_null 
  AND not_null > 0 ;
    • 5

  4. 这个也可以。 SQL Fiddle 演示

    我获得 C1 作为每个用户名的总行数，C2 作为每个用户名的总空行数，稍后我会比较这些值。

    SELECT username, col2 FROM
(
SELECT *,
(SELECT Count(*) FROM T Where username = T1.username) C1,
(SELECT Count(*) FROM T Where username = T1.username and col2 is null) C2
FROM T T1
) T2
WHERE C2 > 0 And C1 <> C2
    • 4

  5. 我会使用子查询来选择这些用户名，例如：

    select username
from   dbo.yourtable
group by username
having sum(distinct case when col2 is not null then 1 else 2 end) = 3;
    • 3

  6. 我试过这个...

    select a.username from  
(select username ,col2 
   from yourtable
where col2 is null) a,(select username ,col2 
                       from yourtable
                        where col2 is not null) b
where a.username=b.username;
    • -1

相关问题