sql - MS Access直通查询使用多个表查找重复项

Question

我正在尝试查找所有带有多个coverage_set_id附加benefit_id(值= 2004687)的summary_attribute。

没有GROUP BY＆HAVING部分，该查询似乎运行正常，但是一旦我在COUNT中添加了这些行，我的结果就会不正确。仅尝试获取重复的coverage_set_id。

通过OBDC数据库的直通查询:

SELECT DISTINCT
    b.coverage_set_id,
    COUNT (b.coverage_set_id) AS "COUNT"

FROM
    coverage_set_detail_view a
    JOIN contracts_by_sub_group_view b ON b.coverage_set_id = a.coverage_set_id
    JOIN request c ON c.request_id = b.request_id

WHERE
    b.valid_from_date BETWEEN to_date('10/01/2010','mm/dd/yyyy')
    AND to_date('12/01/2010','mm/dd/yyyy')
    AND c.request_status = 1463
    AND summary_attribute = 2004687
    AND benefit_id <> 1092333

GROUP BY
    b.coverage_set_id

HAVING
    COUNT (b.coverage_set_id) > 1

我的结果如下所示:

-----------------------
COVERAGE_SET_ID | COUNT
-----------------------
4193706         | 8
4197052         | 8
4193926         | 112
4197078         | 96
4174168         | 8

我希望所有的COUNT为2。

::编辑::

解:

SELECT
c.coverage_set_id AS "COVERAGE SET ID",
c1.description AS "Summary Attribute",
count(d.benefit_id) AS "COUNT"

FROM (
SELECT DISTINCT coverage_set_id

FROM contracts_by_sub_group_view

WHERE
    valid_from_date BETWEEN '01-OCT-2010' AND '01-DEC-2010'
    AND request_id IN (
        SELECT request_id
        FROM  request
        WHERE request_status = 1463)
) a

JOIN coverage_set_master e ON e.coverage_set_id = a.coverage_set_id
JOIN coverage_set_detail c ON c.coverage_set_id = a.coverage_set_id
JOIN benefit_summary d ON d.benefit_id = c.benefit_id
 AND d.coverage_type = e.coverage_type
JOIN codes c1 ON c1.code_id = d.summary_attribute

WHERE
d.summary_attribute IN (2004687, 2004688)
AND summary_structure = 1000217

GROUP BY c.coverage_set_id, c1.description

HAVING COUNT(d.benefit_id) > 1

ORDER BY c.coverage_set_id, c1.description

结果是:

COVERAGE SET ID  |  SUMMARY ATTRIBUTE  |  COUNT
-------------------------------------------------
4174168          |  INPATIENT          |   2
4174172          |  INPATIENT          |   2
4191828          |  INPATIENT          |   2
4191832          |  INPATIENT          |   2
4191833          |  INPATIENT          |   2
4191834          |  INPATIENT          |   2
4191838          |  INPATIENT          |   2
4191842          |  INPATIENT          |   2
4191843          |  INPATIENT          |   2
4191843          |  OUTPATIENT         |   2
4191844          |  INPATIENT          |   2
4191844          |  OUTPATIENT         |   2

Answer 1

SELECT的HAVING和计数部分中的coverage_set_id应为Benefit_id。

由于Benefit_id也位于表a中，因此您可以执行以下操作

SELECT  
    a.coverage_set_id,  
    COUNT (a.benefit_id) AS "COUNT"  

FROM  
    coverage_set_detail_view a  

WHERE  
    a.coverage_set_id in (
        SELECT b.coverage_set_id 
        FROM contracts_by_sub_group_view b
        WHERE b.valid_from_date BETWEEN to_date('10/01/2010','mm/dd/yyyy') AND to_date('12/01/2010','mm/dd/yyyy'))
    AND a.coverage_set_id in (
        SELECT b2.coverage_set_id
        FROM contracts_by_sub_group_view b2
        INNER JOIN request c on c.request_id=b2.request_id
        WHERE c.request_status = 1463)
    AND ?.summary_attribute = 2004687  
    AND a.benefit_id <> 1092333  

GROUP BY  
    a.coverage_set_id  

HAVING  
    COUNT (a.benefit_id) > 1  

这将删除FROM上发生的JOIN放大倍数，因为不需要那些表来提取coverage_set_id和Benefit_id。其他2个表的唯一剩余需求是根据WHERE子句中的条件过滤数据。

我不确定summary_attribute位于哪个表中，但是它将遵循与valid_from_date，request_status或benefit_id类似的模式。