sql - 查询以计算每列的相似值

Question

请您帮忙查询下表

╔══════════════╗
║ A  B   C   D ║
╠══════════════╣
║ N  E   1  -5 ║
║ N  N   4  -3 ║
║ N  -4  6   E ║
║ 3  E   E  -8 ║
║ 1  N   N   N ║
╚══════════════╝

我需要如下输出

╔═════════════════════════════════╗
║    N    E     NegativeValues    ║
╠═════════════════════════════════╣
║ A  3    0     0                 ║
║ B  2    2     1                 ║
║ C  1    1     0                 ║
║ D  1    1     3                 ║
╚═════════════════════════════════╝

我需要获取 N 和 E 的计数以及所有列的负值。请帮忙。提前致谢

更新
这里 N 为 NULL，E 以字符串“-1E+70”开头

Answer 1

尝试取消旋转，然后有条件地计算行数。
像这样的东西:

SELECT ABC, 
       Sum(CASE 
             WHEN VALS = 'n' THEN 1 
             ELSE 0 
           END) n, 
       Sum(CASE 
             WHEN VALS = 'e' THEN 1 
             ELSE 0 
           END) E, 
       Sum(CASE 
             WHEN LEFT(VALS, 1) = '-' THEN 1 
             ELSE 0 
           END) NegativeValues 
FROM   TABLE1 
       UNPIVOT (VALS 
               FOR ABC IN ([A], 
                           [B], 
                           [C], 
                           [D]))t 
GROUP  BY ABC 

查看 SQL Fiddle 上的一个工作示例.
祝你好运!

考虑到“n”值实际上是 NULL 并且 UNPIVOT函数删除 NULL 值并且“e”值实际上是“-1e”，这是查询的更新版本:

SELECT ABC, 
       Sum(CASE 
             WHEN VALS = 'NULL' THEN 1 
             ELSE 0 
           END) n, 
       Sum(CASE 
             WHEN VALS = '-1e' THEN 1 
             ELSE 0 
           END) E, 
       Sum(CASE 
             WHEN LEFT(VALS, 1) = '-' 
                  AND Charindex('e', VALS) = 0 THEN 1 
             ELSE 0 
           END) NegativeValues 
FROM   (SELECT Isnull(A, 'NULL')A, 
               Isnull(B, 'NULL')B, 
               Isnull(C, 'NULL')C, 
               Isnull(D, 'NULL')D 
        FROM   TABLE1)T 
       UNPIVOT (VALS 
               FOR ABC IN ([A], 
                           [B], 
                           [C], 
                           [D]))t 
GROUP  BY ABC 

这是 SQL Fiddle 的更新链接.

编辑
有了 -1e 实际上是科学数字并且所有字段都是浮点类型的新信息，这是我更新的解决方案(希望是最终解决方案):

SELECT ABC, 
       Sum(CASE 
             WHEN VALS = 'NULL' THEN 1 
             ELSE 0 
           END) n, 
       Sum(CASE 
             WHEN VALS LIKE '-1e+070%' THEN 1 
             ELSE 0 
           END) E, 
       Sum(CASE 
             WHEN LEFT(VALS, 1) = '-' 
                  AND Charindex('e', VALS) = 0 THEN 1 
             ELSE 0 
           END) NegativeValues 
FROM   (SELECT Isnull(Cast(A AS VARCHAR(10)), 'NULL')A, 
               Isnull(Cast(B AS VARCHAR(10)), 'NULL')B, 
               Isnull(Cast(C AS VARCHAR(10)), 'NULL')C, 
               Isnull(Cast(D AS VARCHAR(10)), 'NULL')D 
        FROM   TABLE1)T 
       UNPIVOT (VALS 
               FOR ABC IN ([A], 
                           [B], 
                           [C], 
                           [D]))t 
GROUP  BY ABC 

当然还有指向 SQL Fiddle 的新链接.