我在資料庫中有如下表：
| ID | 可樂 | ColB | 科爾C | 寒冷的 | 油菜 | ColF | 科爾G | 用戶身份 |
|---|---|---|---|---|---|---|---|---|
| 1 | AA | AB | AA | AA | AA | AB | AA | 用戶ABC |
我想計算 userABC 得到了多少個“AB”。
所以我想要的結果如下
| 計數 AB |
|---|
| 2 |
有人知道這種情況的查詢或關鍵字嗎？先感謝您！！
uj5u.com熱心網友回復：
你可以用sumandcase陳述句嘗試這樣的事情：
select sum(case when ColA = 'AB' then 1 else 0 end
case when ColB = 'AB' then 1 else 0 end
case when ColC = 'AB' then 1 else 0 end
case when ColD = 'AB' then 1 else 0 end
case when ColE = 'AB' then 1 else 0 end
case when ColF = 'AB' then 1 else 0 end
case when ColG = 'AB' then 1 else 0 end) from table_name
小提琴
uj5u.com熱心網友回復：
使用VALUES表值建構式取消透視表并計算AB's. 如果要獲取每一行的計數，可以選擇以下陳述句：
-- Sample data
SELECT *
INTO TestTable
FROM (VALUES
(1, 'AA', 'AB', 'AA', 'AA', 'AA', 'AB', 'AA', 'userABC')
) t (id, ColA, ColB, ColC, ColD, ColE, ColF, ColG, USER_ID)
-- T-SQL
SELECT
t.USER_ID,
(
SELECT COUNT(*)
FROM (VALUES (t.ColA), (t.ColB), (t.ColC), (t.ColD), (t.ColE), (t.ColF), (t.ColG)) v (Col)
WHERE Col = 'AB'
) AS CountAB
FROM TestTable t
WHERE (t.USER_ID = 'userABC')
如果表中的 1 有不止一行USER_ID，則需要對行進行分組：
-- Sample data
SELECT *
INTO TestTable
FROM (VALUES
(1, 'AA', 'AB', 'AA', 'AA', 'AA', 'AB', 'AA', 'userABC'),
(2, 'AA', 'AB', 'AA', 'AA', 'AA', 'AB', 'AA', 'userABC'),
(3, 'AA', 'AB', 'AA', 'AA', 'AA', 'AB', 'AA', 'userABC')
) t (id, ColA, ColB, ColC, ColD, ColE, ColF, ColG, USER_ID)
-- T-SQL
SELECT t.USER_ID, COUNT(a.Col) AS CountAB
FROM TestTable t
OUTER APPLY (VALUES
(t.ColA), (t.ColB), (t.ColC), (t.ColD), (t.ColE), (t.ColF), (t.ColG)
) a (Col)
WHERE (t.USER_ID = 'userABC') AND (a.Col = 'AB')
GROUP BY t.USER_ID
uj5u.com熱心網友回復：
您可以從寬格式到高格式，然后按用戶聚合：
WITH cte AS (
SELECT USER_ID, ColA AS val FROM yourTable UNION ALL
SELECT USER_ID, ColB FROM yourTable UNION ALL
SELECT USER_ID, ColC FROM yourTable UNION ALL
SELECT USER_ID, ColD FROM yourTable UNION ALL
SELECT USER_ID, ColE FROM yourTable UNION ALL
SELECT USER_ID, ColF FROM yourTable UNION ALL
SELECT USER_ID, ColG FROM yourTable
)
SELECT USER_ID, COUNT(*) AS [count AB]
FROM cte
WHERE val = 'AB'
GROUP BY USER_ID;
uj5u.com熱心網友回復：
你可以試試這樣的
Select Id, User_Id, ((Select Isnull(count(Id),0) from [Table] where ColA
like'AB' and Id = A.Id)
(Select Isnull(count(Id),0) from [Table] where ColB = 'AB' and Id = A.Id)
(Select Isnull(count(Id),0) from [Table] where ColC = 'AB' and Id = A.Id)
(Select Isnull(count(Id),0) from [Table] where ColD = 'AB' and Id = A.Id)
(Select Isnull(count(Id),0) from [Table] where ColE = 'AB' and Id = A.Id)
(Select Isnull(count(Id),0) from [Table] where ColF = 'AB' and Id = A.Id)
(Select Isnull(count(Id),0) from [Table] where ColG = 'AB' and Id = A.Id)
) as Result
from [Table] A
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/406362.html
標籤：
上一篇：SQLPIVOT不像懷疑的那樣