跪求SQL大佬优化语句!!

该sql存在重复查询导致的数据重复问题,跪求大佬帮忙优化。

SELECT
    a.projectCode projectCode,
    a.Room1 r1,
    a.Room2 r2,
    b.Room1 r3,
    b.Room2 r4 
FROM
    (
SELECT
    p.projectCode,
    sum( CASE WHEN h.roomType = '1' THEN 1 ELSE 0 END ) AS Room1,
    sum( CASE WHEN ( h.roomType = '2' OR h.roomType = '4' ) THEN 1 ELSE 0 END ) AS Room2 
FROM
    House h,
    Project p 
WHERE
    h.projectId = p.id 
    AND p.projectCode IS NOT NULL 
    AND h.STATUS = 2 and h.id IN (
SELECT DISTINCT
    t.houseId 
FROM
    ( SELECT Contract.*, row_number ( ) over ( PARTITION BY familyId ORDER BY startDate DESC ) rn FROM Contract ) t 
WHERE
    rn = 1 and t.status=1
    ) 
GROUP BY
    p.projectCode 
    ) AS a
    full JOIN (
SELECT
    p.projectCode,
    sum( CASE WHEN h.roomType = '1' THEN 1 ELSE 0 END ) AS Room1,
    sum( CASE WHEN ( h.roomType = '2' OR h.roomType = '4' ) THEN 1 ELSE 0 END ) AS Room2 
FROM
    House h,
    Project p 
WHERE
    h.projectId = p.id 
    AND p.projectCode IS NOT NULL 
    AND h.STATUS = 2 and  h.id IN (
SELECT DISTINCT
    t.houseId 
FROM
    ( SELECT Contract.*, row_number ( ) over ( PARTITION BY familyId ORDER BY startDate DESC ) rn FROM Contract ) t 
WHERE
    rn = 1 and t.status=0
    ) 
GROUP BY
    p.projectCode 
    ) AS b ON a.projectCode = b.projectCode 
WHERE
    a.projectCode LIKE '110%'

1个回答

你这么多连接,数据粒度相同不,不相同肯定翻倍。

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问