如何在不使用WITH AS子句的情况下改写指定SQL查询（解决结果重复问题）

Got it, let's tackle this problem head-on. The core issue here is that your reporting tool doesn't support WITH AS, and your initial nested SELECT attempt introduced duplicate rows because of how the joins were structured. Let's break down the original logic and rewrite it properly.

First, Understand the Original Logic #

The table1 CTE is doing a per-ticketsid aggregation: counting how many records are in Closed (STATE=3) and Pending (STATE=1) status, by joining tickets with fields, taskinfo, and TaskDetails. The main query then joins this aggregated data back to the base tables and filters for TEMPLATEID='123'.

Why Your Initial Nested SELECT Caused Duplicates #

The full joins in your main query were creating Cartesian products when a single ticketsid had multiple matching records in fields or taskinfo. When you joined this duplicated dataset to the single-row-per-ticketsid aggregated data, you ended up with repeated rows for the same ticket.

Rewrite Option 1: Replace CTE with a Subquery + LEFT JOIN + GROUP BY #

This approach keeps the aggregation as a subquery, uses LEFT JOIN instead of FULL JOIN (since we only care about tickets matching the template filter), and adds a GROUP BY to collapse any remaining duplicates from the fields join:

SELECT 
    tbl_.ticketsid AS "Ticket ID",
    tbl_.TITLE AS "Title",
    CAST(DATEADD(SECOND, tbl_.OPENED/1000, '1970/1/1') AS DATE) AS OpenDate,
    -- Use MAX if a ticket can have multiple Category values; adjust if needed
    MAX(tbl_f.UDF_CHAR13) AS "Category",
    t.Closed,
    t.Pending
FROM "tickets" tbl_
LEFT JOIN "fields" tbl_f ON tbl_.ticketsid = tbl_f.ticketsid
LEFT JOIN (
    -- Original CTE logic wrapped as a subquery
    SELECT 
        tbl_inner.ticketsid,
        COUNT(CASE WHEN STATE = 3 THEN 1 ELSE NULL END) AS Closed,
        COUNT(CASE WHEN STATE = 1 THEN 1 ELSE NULL END) AS Pending
    FROM "tickets" tbl_inner
    LEFT JOIN "fields" tbl_f_inner ON tbl_inner.ticketsid = tbl_f_inner.ticketsid
    LEFT JOIN "taskinfo" tbl_t_inner ON tbl_inner.ticketsid = tbl_t_inner.ticketsid
    LEFT JOIN TaskDetails td_inner ON td_inner.TASKID = tbl_t_inner.TASKID
    GROUP BY tbl_inner.ticketsid
) t ON tbl_.ticketsid = t.ticketsid
WHERE tbl_.TEMPLATEID = '123'
GROUP BY tbl_.ticketsid, tbl_.TITLE, OpenDate, t.Closed, t.Pending

Rewrite Option 2: Use Correlated Subqueries (Simpler, More Efficient) #

If the STATE field comes from taskinfo or TaskDetails (which it likely does), we can simplify the aggregation by moving it directly into the SELECT clause as correlated subqueries. This avoids extra joins entirely and eliminates duplicate risks:

SELECT 
    tbl_.ticketsid AS "Ticket ID",
    tbl_.TITLE AS "Title",
    CAST(DATEADD(SECOND, tbl_.OPENED/1000, '1970/1/1') AS DATE) AS OpenDate,
    MAX(tbl_f.UDF_CHAR13) AS "Category",
    -- Correlated subquery for Closed count
    (
        SELECT COUNT(CASE WHEN STATE = 3 THEN 1 ELSE NULL END)
        FROM "taskinfo" tbl_t_inner
        LEFT JOIN TaskDetails td_inner ON td_inner.TASKID = tbl_t_inner.TASKID
        WHERE tbl_t_inner.ticketsid = tbl_.ticketsid
    ) AS Closed,
    -- Correlated subquery for Pending count
    (
        SELECT COUNT(CASE WHEN STATE = 1 THEN 1 ELSE NULL END)
        FROM "taskinfo" tbl_t_inner
        LEFT JOIN TaskDetails td_inner ON td_inner.TASKID = tbl_t_inner.TASKID
        WHERE tbl_t_inner.ticketsid = tbl_.ticketsid
    ) AS Pending
FROM "tickets" tbl_
LEFT JOIN "fields" tbl_f ON tbl_.ticketsid = tbl_f.ticketsid
WHERE tbl_.TEMPLATEID = '123'
GROUP BY tbl_.ticketsid, tbl_.TITLE, OpenDate

Key Notes #

• The MAX(tbl_f.UDF_CHAR13) handles cases where a single ticket has multiple entries in the fields table. If each ticket only has one UDF_CHAR13 value, you can replace MAX with just tbl_f.UDF_CHAR13 and keep it in the GROUP BY.
• Using LEFT JOIN instead of FULL JOIN ensures we only pull tickets that match your TEMPLATEID filter, which aligns with the original query's intent.

内容的提问来源于stack exchange，提问作者YoBigMan