Using 2 levels avobe column in union subquery-CodePudding

I have a very complicated query which involves a subquery and this subquery usas an union as the table. I want to use a column from the first level (a field before the subquery) as part of the where clausule in the union. Like this:

SELECT 
    type, 
    registered_number - (
        SELECT 
            MAX(last)
        FROM (
            SELECT 
                MAX(b) as last
            FROM 
                x
            WHERE  
                a = type 
            UNION ALL 
            SELECT 
                MAX(b) as last
            FROM 
                y
            WHERE 
                a = type
        ) as last_table
    ) as last
FROM `x`;

Sample data

Table X

a	b
1	25
2	26
3	27

TABLE Y

a	b
1	25
2	24
3	31

TABLE s

id	type	registered_number
1	1	7
2	2	8
3	3	9

EXPECTED RESULT

type	last
1	18
2	18
3	22

CodePudding user response：

I suggest doing a union of the x and y tables first, then join s to an aggregate of the union subquery.

SELECT s.type, t.b AS last
FROM s
INNER JOIN
(
    SELECT a, MAX(b) AS b
    FROM
    (
        SELECT a, b FROM x
        UNION ALL
        SELECT a, b FROM y
    ) t
    GROUP BY a
) t
    ON t.a = s.type
ORDER BY s.type;