Sybase SQL - 删除“半重复”从查询结果 [英] Sybase SQL - Remove "semi-duplicates" from query results

问题描述

我有一个查询，使用两个 SELECT 语句，使用 UNION ALL 组合。这两个语句从类似的表中提取数据来填充查询结果。我正在尝试从查询中删除半重复行，但是这样做有问题。

I have a query that uses two SELECT statements that are combined using a UNION ALL. Both statements pull data from similar tables to populate the query results. I am attempting to remove the "semi-duplicate" rows from the query, but am having issues doing so.

我的查询如下：

    SELECT DISTINCT * 
FROM
    (
    SELECT
        TeamNum = CASE 
                WHEN T.TeamName = 'Alpha Team'
                    THEN '1'
                WHEN T.TeamName IN ('Bravo Team', 'Charlie Team')
                    THEN '2'
                WHEN T.TeamName = 'Delta Team'
                    THEN '3'
                ELSE '<Undefined>'
                END,
        P.PatientLastName AS LastName,
        P.PatientFirstName AS FirstName,
        R.PrimaryCity AS City,
        ReimbursorName = CASE
                WHEN RE.ReimbursorDescription = 'Medicare'
                    Then 'R1'
                WHEN RE.ReimbursorDescription = 'Medicaid'
                    Then 'R2'
                ELSE 'R3'
                END,
        P.PatientID AS PatientID
    FROM 
        PatReferrals PR LEFT JOIN Patient P ON PR.PatientID = P.PatientID,
        Patient P LEFT OUTER JOIN Rolodex R ON P.RolodexID = R.RolodexID,
        PatReferrals PR LEFT OUTER JOIN PatReimbursors PRE ON PR.PatientID = PRE.PatientID,
        PatReimbursors PRE LEFT OUTER JOIN Reimbursors RE ON PRE.ReimbursorID = RE.ReimbursorID,
        PatReferrals PR FULL OUTER JOIN Teams T ON PR.TeamID = T.TeamID,
    WHERE 
        PR.ReferralDate BETWEEN GETDATE()-4 AND GETDATE()-1
        AND PR.Status <> 'R' 
        AND PRE.CoveragePriority = '1'
        AND PRE.ExpirationDate IS NULL 

    UNION ALL

    SELECT
        TeamNum = CASE 
                WHEN T.TeamName = 'Alpha Team'
                    THEN '1'
                WHEN T.TeamName IN ('Bravo Team', 'Charlie Team')
                    THEN '2'
                WHEN T.TeamName = 'Delta Team'
                    THEN '3'
                ELSE '<Undefined>'
                END,
        P.PatientLastName AS LastName,
        P.PatientFirstName AS FirstName,
        R.PrimaryCity AS City,
        ReimbursorName = CASE
                WHEN RE.ReimbursorDescription = 'Medicare'
                    Then 'E1'
                WHEN RE.ReimbursorDescription = 'Medicaid'
                    Then 'E2'
                ELSE 'E3'
                END,
        P.PatientID AS PatientID
    FROM 
        PatReferrals PR LEFT JOIN Patient P ON PR.PatientID = P.PatientID,
        Patient P LEFT OUTER JOIN Rolodex R ON P.RolodexID = R.RolodexID,
        PatReferrals PR LEFT OUTER JOIN PatEligibilities PE ON PR.PatientID = PE.PatientID,
        PatEligibilities PE LEFT OUTER JOIN Reimbursors RE ON PE.ReimbursorID = RE.ReimbursorID,
        PatReferrals PR FULL OUTER JOIN Teams T ON PR.TeamID = T.TeamID,
    WHERE 
        PR.ReferralDate BETWEEN GETDATE()-4 AND GETDATE()-1
        AND PR.Status <> 'R' 
        AND PE.Status <> 'V'
        AND PE.ApplicationDate BETWEEN DATE(PR.ReferralDate)-5 AND DATE('2100/01/01')
    ) 

AS DUMMYTBL

ORDER BY 
    DUMMYTBL.LastName ASC,
    DUMMYTBL.FirstName ASC

我运行查询时收到的结果如下：

The results that I receive when I run the query is the following:

3   Doe Jane    Town    R1  19874
1   Roe John    City    R3  50016
1   Roe John    City    E1  50016
2   Smith   Jane    Town    E3  33975

一旦从原始查询中导入结果，我需要删除的数据就是基于特定条件的重复行。每个人只能列出一次，他们必须有单一的薪资来源（R1，R2，R3，E1，E2，E3）。如果有一个R＃，比那个人不能列出一个E＃。如果没有R＃的E＃必须列出。如我的示例结果所示，第2行和第3行具有列出的同一个人，但是两个付费来源（R3和E1）。

The data that I am needing to remove is duplicate rows based on a certain criteria once the results are brought in from the original query. Each person can only be listed once and they must have a single pay source (R1, R2, R3, E1, E2, E3). If there is a R#, than there cannot be a E# listed for that person. If there are no R#'s than an E# must be listed. As shown in my example results, line 2 and 3 have the same person listed, but two pay sources (R3 and E1).

如何使每个人只使用我列出的条件显示一行？

How can I go about making each person have only one row shown using the criteria that I have listed?

编辑：修改SQL查询以显示 WHERE 子句中的原始变量，以显示查询的详细信息。 PatReimbursors和PatEligibility表具有相似的数据，但标准是不同的，以便提取正确的数据。

EDIT: Modifed the SQL query to show the original variables from the WHERE clauses in order to show further detail on the query. The PatReimbursors and the PatEligibilities tables have similar data, but the criteria is different in order to pull the correct data.

推荐答案

您的查询没有意义我将从子句中删除​​由 生成的隐式笛卡尔积。

Your query does not make sense. I would start by eliminating the implicit cartesian product, generated by the , in the from clause.

我的猜测是from子句应该是：

My guess is that the from clause should be:

FROM 
    PatReferrals PR LEFT JOIN
    Patient P
    ON PR.PatientID = P.PatientID left outer join
    Rolodex R
    ON P.RolodexID = R.RolodexID left outer join
    PatEligibilities PE
    ON PR.PatientID = PE.PatientID left outer join
    Reimbursors RE
    ON PE.ReimbursorID = RE.ReimbursorID left outer join
    Teams T ON PR.TeamID = T.TeamID

一旦你这样做，你可能不需要 union all 或 select distinct 。您可以在同一查询中同时将奖励者和资格放在一起。

Once you do this, you may not need the union all or the select distinct. You may be able to put both the reimbursors and the eligibilities in the same query.

这篇关于Sybase SQL - 删除“半重复”从查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！