SQL计算在特定用户中有多少列具有特定值-编程知识-白鹭情

我在数据库中有如下表：

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