I have a system that generates a SQL query with lots of derived table within sub queries. Here is a typical example:
SELECT
COUNT(`temp_table_main`.`person_id`) AS "resp_count",
`temp_table_main`.`SAMPLE`,
SUM(`temp_table_main`.`WT`) AS "resp_count_weight" ,
SUM(`temp_table_main`.`field1`) as `field1_count`,
(
SELECT
SUM(`temp_table_field1`.`WT`)
FROM
(
SELECT
`person_id`,
MAX(CASE when `variable` = 4 THEN `value` END) AS `field1`,
MAX(CASE when `variable` = 10 THEN `value` END) AS `WT`,
MAX(CASE when `variable` = 3 THEN `value` END) AS `SAMPLE`
FROM
`4_86_data`
GROUP BY `person_id`
) AS `temp_table_field1`
WHERE `temp_table_field1`.`field1` = 1
AND `SAMPLE` = `temp_table_main`.`SAMPLE`
) AS `field1_count_WT`
FROM
(
SELECT
`person_id`,
MAX(CASE when `variable` = 4 THEN `value` END) AS `field1` ,
MAX(CASE when `variable` = 3 THEN `value` END) AS `SAMPLE` ,
MAX(CASE when `variable` = 10 THEN `value` END) AS `WT`
FROM
`4_86_data`
GROUP by `person_id`
) AS `temp_table_main`
WHERE ( `field1` IS NOT NULL ) GROUP BY `SAMPLE`
What I would like to do it be able to format it as such:
SELECT
COUNT(`temp_table_main`.`person_id`) AS "resp_count",
`temp_table_main`.`SAMPLE`,
SUM(`temp_table_main`.`WT`) AS "resp_count_weight" ,
SUM(`temp_table_main`.`field1`) as `field1_count`,
(
SELECT
SUM(`temp_table_field1`.`WT`)
FROM
`temp_table_main` AS `temp_table_field1`
WHERE `temp_table_field1`.`field1` = 1
AND `SAMPLE` = `temp_table_main`.`SAMPLE`
) AS `field1_count_WT`
FROM
(
SELECT
`person_id`,
MAX(CASE when `variable` = 4 THEN `value` END) AS `field1` ,
MAX(CASE when `variable` = 3 THEN `value` END) AS `SAMPLE` ,
MAX(CASE when `variable` = 10 THEN `value` END) AS `WT`
FROM
`4_86_data`
GROUP by `person_id`
) AS `temp_table_main`
WHERE ( `field1` IS NOT NULL ) GROUP BY `SAMPLE`
You'll notice that I have attempted to refer to the outer derived table within a sub-query in the outer select statement, assigning it to temp_table_field1
.
There are cases where I will have N number of such sub queries within the outer select statement, hence the need to assign it as a different derived table name.
Perhaps this question is, can I derive a table from a derived table?
BTW, my sql works and executes fine by having lots of derived tables, I just wonder whether or not:
- it is quicker to be able to assign once, use many times
- it makes any particular difference
ALSO NOTE: I cannot assign these as temporary tables. It has to execute within one query.