sql - updateorcreate - 查詢來計算每列的相似值
model hasmany laravel (2)
請你幫忙查詢下表
╔══════════════╗
║ 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”開頭
對於更通用的解決方案,您可以參考以下查詢:
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
試試這個 -
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