仅导致一行的联接表

Question

我正尝试加入以下两张表格：

       Table Patient                   |        Table incident
patient.id   patient.birthdate         |  incident.patientid       serviceid
1                  1/1/2000            |       1                        8
2                  1/1/1990            |       1                        8
3                  1/1/2005            |       2                        10
4                  1/1/1980            |       3                        11
5                  1/1/2000            |       3                        11
6                  1/1/1990            |       3                        11
7                  1/1/1980            |       6                        23
8                  1/1/2000            |       7                        8

以便对所有按服务分组的病人进行年龄分离。

SELECT serviceid,
        SUM(CASE WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(patient.birthdate AS INTEGER)) /365.25 ) BETWEEN 0 AND 15 THEN 1 ELSE 0 END) AS [Under 15],
        SUM(CASE WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(patient.birthdate AS INTEGER)) /365.25 ) BETWEEN 16 AND 18 THEN 1 ELSE 0 END) AS [16-18],
        SUM(CASE WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(patient.birthdate AS INTEGER)) /365.25 ) BETWEEN 19 AND 23 THEN 1 ELSE 0 END) AS [19-23],
        SUM(CASE WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(patient.birthdate AS INTEGER)) /365.25 ) BETWEEN 24 AND 30 THEN 1 ELSE 0 END) AS [24-30],
        SUM(CASE WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(patient.birthdate AS INTEGER)) /365.25 ) BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS [31-40],
        SUM(CASE WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(patient.birthdate AS INTEGER)) /365.25 ) BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS [41-50],
        SUM(CASE WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(patient.birthdate AS INTEGER)) /365.25 ) BETWEEN 51 AND 65 THEN 1 ELSE 0 END) AS [51-65],
        SUM(CASE WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(patient.birthdate AS INTEGER)) /365.25 ) > 65 THEN 1 ELSE 0 END) AS [>65]

from patient
inner join incident
on patient.id = incident.patientConcerned
group by serviceid

但我在上面尝试的是，计算所有患者的年龄，这意味着我没有把不同的病人计算在内。(例如，我正在数病人1，两次和病人3，3次)

所以我想加入这两个表，但只有一行。

我怎么能这么做？

Answer 1

使用sum()代替count(distinct)。下面是一个示例：

SELECT serviceid,
       COUNT(DISTINCT CASE WHEN FLOOR((CAST (GetDate() AS INTEGER) - CAST(patient.birthdate AS INTEGER)) /365.25 ) BETWEEN 0 AND 15
                           THEN Patient.Id 
                      END) AS [Under 15],
       . . .

Answer 2

使用Distinct操作符。您的查询应该如下所示：

   SELECT Distinct a.id, a.birthdate ,b.patient from
   patient a inner join incident b ON a.serviceid=b.serviceid

Answer 3

使用group by serviceid代替group by patient.patient_id