时间戳

首页 > 解决方案 > SQL 获取按列分组的几行的计数

sql - SQL 获取按列分组的几行的计数

问题描述

我正在尝试为每列计算多个单独的行。下面是我想要完成的一个例子。

select (select top 1 name
          from chap
         where chap.chp_id = CHS.chp_id) as Chap,
       (select count(*)
          from CHS,
               chap 
         where O_TYPE = 'PRESIDENT'
           and chs.chp_id = chap.chp_id) as Presidents,
       (select count(*)
          from CHS 
         where O_TYPE = 'VICEPRESIDENT') as VicePresidents,
       (select count(*)
          from CHS 
         where OFFICER_TYPE = 'CORRSECRETARY') as CorrespondinSecretaries,
       (select count(*)
          from CHS 
         where O_TYPE = 'RECORDINGSECRETARY') as RecordingSecretaries,
       (select count(*)
          from CHS 
         where O_TYPE = 'TREASURER') as Treasurers,
       (select count(*)
          from CHS 
         where O_TYPE = 'ADVISOR'
           and ADV_CODE = 'B') as ChiefAdvisors,
       (select count(*)
          from CHS 
         where O_TYPE = 'ADVISOR'
           and ADV_CODE <> 'B') as ChiefAdvisors
from CHS
where O_TYPE in ('PRESIDENT', 'VICEPRESIDENT', 'CORRSECRETARY', 'RECORDINGSECRETARY', 'TREASURER', 'ADVISOR')
  and Term_expire >= DateAdd(Day,DateDiff(Day,0,GetDate()),0)
  and Term_Begin <= DateAdd(Day,DateDiff(Day,0,GetDate()),0)
  and CHS.CHP_ID in (Select chp_id 
                            from chrs
                           where active = 'Y') 
Group by chs.CHP_ID

当我运行它时，它会将每一行的所有记录汇总在一起，而不仅仅是该章的记录。有什么建议么？

输出示例

AL A    247 264 247 250 246 235 739
AL B    247 264 247 250 246 235 739
AL G    247 264 247 250 246 235 739
AL D    247 264 247 250 246 235 739
AK A    247 264 247 250 246 235 739
AZ A    247 264 247 250 246 235 739
AZ B    247 264 247 250 246 235 739
AZ G    247 264 247 250 246 235 739

我真正想要的是

AL A 1 1 1 4 8 9 16
AL B 1 1 5 7 8 9 21

标签： sqlsql-serverjoinselect

解决方案

考虑将所有子查询替换为计算列，名字只是一个JOIN到主表。对于计数，通过对条件求和来使用条件聚合。此外，调整GROUP BY以包含名称而不是唯一 ID，这可能是重复数据的原因。此外，请检查术语日期范围，WHERE因为它们似乎是多余的。

SELECT chap.name AS Chap,
       SUM(O_TYPE = 'PRESIDENT') AS Presidents,
       SUM(O_TYPE = 'VICEPRESIDENT') AS VicePresidents,
       SUM(OFFICER_TYPE = 'CORRSECRETARY') AS CorrespondingSecretaries,
       SUM(O_TYPE = 'RECORDINGSECRETARY') AS RecordingSecretaries,
       SUM(O_TYPE = 'TREASURER') AS Treasurers,
       SUM(O_TYPE = 'ADVISOR' AND ADV_CODE = 'B') AS ChiefAdvisors,
       SUM(O_TYPE = 'ADVISOR' AND ADV_CODE <> 'B') AS OtherAdvisors

FROM chap
INNER JOIN CHS ON chap.chp_id = CHS.chp_id
INNER JOIN chrs ON CHS.CHP_ID = chrs.CHP_ID AND active = 'Y'
WHERE O_TYPE IN ('PRESIDENT', 'VICEPRESIDENT', 'CORRSECRETARY', 
                 'RECORDINGSECRETARY', 'TREASURER', 'ADVISOR')
  AND Term_expire >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)
  AND Term_Begin <= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0)

GROUP BY chap.name

推荐阅读