sql - updateorcreate - 查詢來計算每列的相似值




model hasmany laravel (2)

對於更通用的解決方案,您可以參考以下查詢:

  SELECT "A", 
       Sum(N) N, 
       Sum(E) E, 
       Sum(M) "Negative" 
FROM   (SELECT CASE 
                 WHEN A = "N" THEN Count(A) 
                 ELSE 0 
               END N, 
               CASE 
                 WHEN A = "E" THEN Count(A) 
                 ELSE 0 
               END E, 
               CASE 
                 WHEN A LIKE "%-%" THEN Count(A) 
                 ELSE 0 
               END "M" 
        FROM   #TAB2 
        GROUP  BY A) aa 
UNION 
SELECT "B", 
       Sum(N) N, 
       Sum(E) E, 
       Sum(M) "Negative" 
FROM   (SELECT CASE 
                 WHEN B = "N" THEN Count(B) 
                 ELSE 0 
               END N, 
               CASE 
                 WHEN B = "E" THEN Count(B) 
                 ELSE 0 
               END E, 
               CASE 
                 WHEN B LIKE "%-%" THEN Count(B) 
                 ELSE 0 
               END "M" 
        FROM   #TAB2 
        GROUP  BY B) bb 
UNION 
SELECT "C", 
       Sum(N) N, 
       Sum(E) E, 
       Sum(M) "Negative" 
FROM   (SELECT CASE 
                 WHEN C = "N" THEN Count(A) 
                 ELSE 0 
               END N, 
               CASE 
                 WHEN C = "E" THEN Count(A) 
                 ELSE 0 
               END E, 
               CASE 
                 WHEN C LIKE "%-%" THEN Count(C) 
                 ELSE 0 
               END "M" 
        FROM   #TAB2 
        GROUP  BY C) cc 
UNION 
SELECT "D", 
       Sum(N) N, 
       Sum(E) E, 
       Sum(M) "Negative" 
FROM   (SELECT CASE 
                 WHEN D = "N" THEN Count(D) 
                 ELSE 0 
               END N, 
               CASE 
                 WHEN D = "E" THEN Count(D) 
                 ELSE 0 
               END E, 
               CASE 
                 WHEN D LIKE "%-%" THEN Count(D) 
                 ELSE 0 
               END "M" 
        FROM   #TAB2 
        GROUP  BY D) dd 

請你幫忙查詢下表

╔══════════════╗
 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”開頭


試試這個 -

DECLARE @temp TABLE (A CHAR(2), B CHAR(2), C CHAR(2), D CHAR(2))

INSERT INTO @temp (A, B, C, D)
VALUES 
    (NULL, 'E'  , '1', '-5'),
    (NULL, NULL  , '4', '-3'),
    (NULL, '-4' , '6', 'E'),
    ('3', 'E'  , 'E', '-8'),
    ('1', NULL  , NULL, NULL)

SELECT 
      Code
    , N = COUNT(CASE WHEN Value = '' THEN 1 END)
    , E = COUNT(CASE WHEN Value LIKE '%[A-Z]%' THEN 1 END)
    , NegativeValues = COUNT(CASE WHEN LEFT(Value, 1) = '-' THEN 1 END)
FROM (
    SELECT 
          A = ISNULL(A, '')
        , B = ISNULL(B, '')
        , C = ISNULL(C, '')
        , D = ISNULL(D, '')
    FROM @temp
) t
UNPIVOT 
(
    Value FOR Code IN (A, B, C, D)
) unpvt
GROUP BY Code






sql-server-2012