时间戳

首页 > 解决方案 > 重复项的 SQL 结果 - Oracle

sql - 重复项的 SQL 结果 - Oracle

问题描述

SO 社区，这是我的 SQL 查询，它运行时没有错误：

SELECT DISTINCT
    (L.LOCATION) AS "LOC",
    C.CITY_NAME AS "CIT",
    H.STREET_NAME AS "STR",
    U.APT_NAME AS "APT",
    U.NUM AS "NUM",
    L.DESCRIPTION AS "DES",
    H.POP AS "POP",
    S.STATE_CODE AS "STA",
    H.SITE_NAME AS "SIT",
    U.COUNTY AS "COU",
    L.DESCRIPTION AS "PRO"
FROM
    LOCATIONS L
    LEFT OUTER JOIN CGZIP9 Z
    ON L.L_ZIPCODE = Z.ZIPCODE
    LEFT OUTER JOIN STREET_V H
    ON L.LOCATION = H.LOCATION
    LEFT OUTER JOIN CGZIPCITY Y
    ON Z.CITYID = Y.CITYID
    LEFT OUTER JOIN CGCITY C
    ON Y.CITYID = C.CITYID
    LEFT OUTER JOIN CGSTATE S
    ON C.STATEFIPS = S.STATEFIPS
    LEFT OUTER JOIN UNIT U
    ON L.UNITID = U.UNIT_ID
WHERE
    L.LOCATION IN ('1', '2', '3', '4', '5', '1', '2', '4')
    ORDER BY L.LOCATION ASC;

我正在尝试检索我的 IN 中所有值的记录（即使是“1”、“2”和“4”等重复项）

这是我运行上述代码时的输出：

=============================================
|LOC|CIT|STR|APT|NUM|DES|POP|STA|SIT|COU|PRO|
=============================================
| 1 | X | X | X | X | X | X | X | X | X | X | 
| 2 | X | X | X | X | X | X | X | X | X | X |
| 3 | X | X | X | X | X | X | X | X | X | X |  
| 4 | X | X | X | X | X | X | X | X | X | X | 
| 5 | X | X | X | X | X | X | X | X | X | X |  
=============================================

这是我想要的输出：

=============================================
|LOC|CIT|STR|APT|NUM|DES|POP|STA|SIT|COU|PRO|
=============================================
| 1 | X | X | X | X | X | X | X | X | X | X | 
| 2 | X | X | X | X | X | X | X | X | X | X |
| 3 | X | X | X | X | X | X | X | X | X | X |  
| 4 | X | X | X | X | X | X | X | X | X | X | 
| 5 | X | X | X | X | X | X | X | X | X | X |  
| 1 | X | X | X | X | X | X | X | X | X | X | 
| 2 | X | X | X | X | X | X | X | X | X | X |  
| 4 | X | X | X | X | X | X | X | X | X | X |  
=============================================

关于我如何做到这一点的任何建议？

标签： sqloracle

解决方案

请尝试不过滤掉重复项的 UNION ALL 方法，如下所示：

SELECT DISTINCT (L.LOCATION)  AS "LOC",
                C.CITY_NAME   AS "CIT",
                H.STREET_NAME AS "STR",
                U.APT_NAME    AS "APT",
                U.NUM         AS "NUM",
                L.DESCRIPTION AS "DES",
                H.POP         AS "POP",
                S.STATE_CODE  AS "STA",
                H.SITE_NAME   AS "SIT",
                U.COUNTY      AS "COU",
                L.DESCRIPTION AS "PRO"
  FROM LOCATIONS L
       LEFT OUTER JOIN CGZIP9 Z ON L.L_ZIPCODE = Z.ZIPCODE
       LEFT OUTER JOIN STREET_V H ON L.LOCATION = H.LOCATION
       LEFT OUTER JOIN CGZIPCITY Y ON Z.CITYID = Y.CITYID
       LEFT OUTER JOIN CGCITY C ON Y.CITYID = C.CITYID
       LEFT OUTER JOIN CGSTATE S ON C.STATEFIPS = S.STATEFIPS
       LEFT OUTER JOIN UNIT U ON L.UNITID = U.UNIT_ID
 WHERE L.LOCATION IN ('1',
                      '2',
                      '3',
                      '4',
                      '5')
UNION ALL
SELECT DISTINCT (L.LOCATION)  AS "LOC",
                C.CITY_NAME   AS "CIT",
                H.STREET_NAME AS "STR",
                U.APT_NAME    AS "APT",
                U.NUM         AS "NUM",
                L.DESCRIPTION AS "DES",
                H.POP         AS "POP",
                S.STATE_CODE  AS "STA",
                H.SITE_NAME   AS "SIT",
                U.COUNTY      AS "COU",
                L.DESCRIPTION AS "PRO"
  FROM LOCATIONS L
       LEFT OUTER JOIN CGZIP9 Z ON L.L_ZIPCODE = Z.ZIPCODE
       LEFT OUTER JOIN STREET_V H ON L.LOCATION = H.LOCATION
       LEFT OUTER JOIN CGZIPCITY Y ON Z.CITYID = Y.CITYID
       LEFT OUTER JOIN CGCITY C ON Y.CITYID = C.CITYID
       LEFT OUTER JOIN CGSTATE S ON C.STATEFIPS = S.STATEFIPS
       LEFT OUTER JOIN UNIT U ON L.UNITID = U.UNIT_ID
 WHERE L.LOCATION IN ('1', '2', '4')
ORDER BY L.LOCATION ASC;

推荐阅读