如何阻止SQL查询多次显示表？

Question

在我的脚本中，我有一个显示SQL查询结果的表。但是，我使用结果作为摘要，但是由于Where子句的原因，SQL使页面显示该表的次数与返回SQL中允许的结果(行)的次数一样多。即，如果有3组人被汇总，则该表将相同的信息重复3次

有什么常见的方法来解决这个问题吗？谢谢!

SELECT 
    Demographics.Name,
    Demographics.NDoc_Number,
    Demographics.PID_alphanumeric,
    Demographics.Company,
    Demographics.Company_Name,
    Demographics.Location,
    Demographics.Location_Name,
    Demographics.Team_CMT, 
    Demographics.Case_Manager,
    Demographics.Case_Manager_UID,
    Demographics.SiteName, 
    CareEpisodes.BGNDATE, 
    CareEpisodes.BRFA, 
    CareEpisodes.ENDDATE, 
    CareEpisodes.ERFA,
    OASIS_Improvement.BGNRPT,
    OASIS_Improvement.ENDRPT,
    ISNULL(SUM(CASE OASIS_Improvement.O_I_@Request.MooNum~
        WHEN 'NA'
        THEN 1
        ELSE 0
    END),0) AS numberImproveNA,
    ISNULL(SUM(CASE OASIS_Stabilization.O_S_@Request.MooNum~
        WHEN 'NA'
        THEN 1
        ELSE 0
    END),0) AS numberStabilizeNA,
    ISNULL(SUM(CASE OASIS_Improvement.O_I_@Request.MooNum~
        WHEN 'NA'
        THEN 0
        ELSE 1
    END),0) AS couldShowImprovement,
    ISNULL(SUM(CASE OASIS_Stabilization.O_S_@Request.MooNum~
        WHEN 'NA'
        THEN 0
        ELSE 1
    END),0) AS couldShowStabilization,
    ISNULL(COUNT(Demographics.Name),0) AS patientCount,
    ISNULL(SUM(CASE OASIS_Improvement.O_I_@Request.MooNum~
        WHEN 1
        THEN 1
        ELSE 0
    END),0) AS doShowImprovement,
    ISNULL(SUM(CASE OASIS_Stabilization.O_S_@Request.MooNum~
        WHEN 1
        THEN 1
        ELSE 0
    END),0) AS doShowStabilization

FROM 
    Demographics  
     INNER JOIN OASIS_Improvement  ON 
    Demographics.NDoc_Number = OASIS_Improvement.NDocNumber 
     INNER JOIN OASIS_Stabilization  ON 
    Demographics.NDoc_Number = OASIS_Stabilization.NDocNumber AND
        OASIS_Improvement.BGNRPT=OASIS_Stabilization.BGNRPT
     INNER JOIN CareEpisodes  ON 
    Demographics.NDoc_Number = CareEpisodes.NDocNumber AND
        OASIS_Improvement.BGNRPT=CareEpisodes.BGNRPT
WHERE
    (Demographics.Company IN (@SingleQuote.Request.companyInput~) OR '@Request.companyInput~' = '') AND
    (Demographics.Location IN (@SingleQuote.Request.locationInput~) OR '@Request.locationInput~' = '') AND
    (Demographics.Team_CMT IN (@SingleQuote.Request.teamInput~) OR '@Request.teamInput~' = '') AND
    (Demographics.Case_Manager_UID IN (@SingleQuote.Request.clinicianInput~) OR '@Request.clinicianInput~' = '') AND
    CareEpisodes.ERFA <> 6 AND
    (OASIS_Improvement.O_I_@Request.MooNum~ = 'NA' OR
    OASIS_Improvement.O_I_@Request.MooNum~ = 1 OR
    OASIS_Improvement.O_I_@Request.MooNum~ = 0) AND
    (OASIS_Stabilization.O_S_@Request.MooNum~ = 'NA' OR
    OASIS_Stabilization.O_S_@Request.MooNum~ = 0 OR
    OASIS_Stabilization.O_S_@Request.MooNum~ = 1) AND
    CareEpisodes.BGNDATE >= '@Request.FromDateInput~' AND
    CareEpisodes.ENDDATE <= '@Request.ThruDateInput~'

Answer 1

如果要对信息进行分组，则需要考虑使用GROUP BY语句。如果您想总结信息，请尝试使用ROLLUP查看GROUP BY。汇总将允许您根据在group BY中选择的值对信息进行分组和汇总。