使用相同的值的SQL case语句时结果的不同行

 收藏

结果应该在已登录且未登录计数的单行中。为什么同一帐户将其分为两行?

SELECT distinct  Account
     , SUM(CASE Val WHEN status='logged in' THEN  1 Else 0  END) AS 'Total logged in' 
     , SUM(CASE Val WHEN status='not logged in' THEN  1 Else 0 END) AS 'total not logged in '
FROM TableAccount 
GROUP BY Account

account    logged in    not logged in
A            21             0
A            0              2
回复
  • zex 回复

    如果您的表结构相同,这似乎可以提供所需的结果。

    create table test1(col1 varchar(10), col2 varchar(10))
    
    insert into test1
    values('A','L'),
    ('A','NL'),
    ('A','L'),
    ('A','L')
    
    SELECT distinct  col1
         , SUM(CASE WHEN col2='L' THEN  1 Else 0  END) AS 'Total logged in' 
         , SUM(CASE WHEN col2='NL' THEN  1 Else 0 END) AS 'total not logged in '
    FROM test1 
    GROUP BY col1
    

  • zipsum 回复

    SELECT DISTINCTis almost never needed with GROUP BY. Also, you are correct, there should only be one row for 'A' -- assuming the account is exactly the same.

    您将获得两行的事实意味着它们并不相同。一个问题可能是空白。您可以检查以下内容:

    SELECT DISTINCT '|' +  Account + '|'
    FROM TableAccount ;
    

    其他问题可能是看起来相同的字符或隐藏的字符。发现问题可能需要进行一些调查。