构建具有成本效益的查询

Question

你能帮我建立一个查询吗。我有一张桌子如下

Id            Info_Id                 Type
1              2                              2
2              6                              2
3              5                              3
4              8                              3
5              2                              3
6              2                              2
7              5                              2
8              8                              2
9              5                              2
10             8                              2
11             8                              2
12             5                              3
13             6                              3
14             8                              3

需要对查询进行框架设置，以便按"Info_Id“分组。

我需要产出如下：

Info_Id CountOfRec       Type2    Type3
2              3         2              1
5              4         2              2
6              2         1              1
8              5         3              2

我试过如下所示，但我无法获得有效的输出

select  Info_Id, count(Id)as CountOfRec,
(select count(Id)from tbl_TypeInfo where Info_Id = 5 AND Type = 2) as Type2,
(select count(Id)from tbl_ TypeInfo where Info_Id = 5 AND Type = 3) as Type3
 from tbl_TypeInfo
where Info_Id = 5
group by Info_Id

输出是这样的，

Info_Id CountOfRec       Type2    Type3
5              4                              2              2

(我必须循环每个“Info_id”以获得所需的OP，有上千条记录及其耗时)

我想从表中得到突出显示的输出。我所设计的查询效率不高，有很好的解决方案，您可以帮助我解决这个问题。

Answer 1

可以使用CASE表达式只计算特定类型的行数：

SELECT Info_Id,
    COUNT(*) AS CountOfRec,
    COUNT(CASE WHEN Type = 2 THEN 1 ELSE NULL END) AS Type2
    COUNT(CASE WHEN Type = 3 THEN 1 ELSE NULL END) AS Type3
FROM tbl_TypeInfo
GROUP BY Info_Id

添加WHERE Info_Id = 5以仅检索特定ID的结果。

Update：根据注释，如果没有存储ID表，则需要将IN(..)列表更改为虚拟“表”：

SELECT vt.id,
    COUNT(*) AS CountOfRec,
    COUNT(CASE WHEN Type = 2 THEN 1 ELSE NULL END) AS Type2,
    COUNT(CASE WHEN Type = 3 THEN 1 ELSE NULL END) AS Type3
FROM (
    SELECT 1 id
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 5
    UNION SELECT 8 
  ) AS vt LEFT JOIN tbl_TypeInfo ON vt.id = tbl_TypeInfo.Info_Id
GROUP BY vt.id

Answer 2

可以使用Server的枢轴操作符

SELECT  Info_ID
        , CountOfRec = [2] + [3]
        , Type2 = [2]
        , Type3 = [3]
FROM    (        
          SELECT  *
          FROM    (          
                    SELECT  *
                    FROM    tbl_TypeInfo
                   ) s 
          PIVOT   (COUNT(Id) FOR Type IN ([2], [3])) pvt 
        ) q

测试

;WITH tbl_TypeInfo AS (
  SELECT [Id] = 1, [Info_Id] = 2, [Type] = 2
  UNION ALL SELECT 2, 6, 2
  UNION ALL SELECT 3, 5, 3
  UNION ALL SELECT 4, 8, 3
  UNION ALL SELECT 5, 2, 3
  UNION ALL SELECT 6, 2, 2
  UNION ALL SELECT 7, 5, 2
  UNION ALL SELECT 8, 8, 2
  UNION ALL SELECT 9, 5, 2
  UNION ALL SELECT 1, 8, 2
  UNION ALL SELECT 1, 8, 2
  UNION ALL SELECT 1, 5, 3
  UNION ALL SELECT 1, 6, 3
  UNION ALL SELECT 1, 8, 3
)
SELECT  Info_ID
        , CountOfRec = [2] + [3]
        , Type2 = [2]
        , Type3 = [3]
FROM    (        
          SELECT  *
          FROM    (          
                    SELECT  *
                    FROM    tbl_TypeInfo
                   ) s 
          PIVOT   (COUNT(Id) FOR Type IN ([2], [3])) pvt 
        ) q

Answer 3

这有点疯狂，但是如果类型总是，始终是2和3，这可以作为一个等式来处理，count(type2)+count(type3)=count(*)和2*count(type2)+3*count(type3)=sum(*)可以这样做

SELECT 3*c-s as Type2Count, s-2*c as Type3Count
FROM (SELECT COUNT(*) as C, SUM(Type) as S 
      FROM tbl_TypeInfo
      WHERE Info_Id = 5) SourceTable

这将是闪电快，然而，这是绝无仅有的易碎！如果曾经更改过类型，或者添加了类型，则这将无法工作。