在JOIN语句中使用派生表

Question

I have a database query in PHP similar to:

SELECT * FROM ($myQuery) AS myDerivedTable
JOIN ( SELECT COUNT(*) FROM myDerivedTable WHERE criteriaA ) AS A
JOIN ( SELECT COUNT(*) FROM myDerivedTable WHERE criteriaB ) AS B
JOIN ( SELECT COUNT(*) FROM myDerivedTable WHERE criteriaC ) AS C
...

For now, suppose the $myQuery contains the following:

SELECT * FROM table1 ORDER BY dateOfSubmition DESC

I just want to use the derived table (myDerivedTable) again and again, instead of calculating it every time! But mySQL reports an error:

#1146 - Table 'myDB.myDerivedTable' doesn't exist

Of course it doesn't exist! By myDerivedTable, I didn't meant to mention an existent table in the database, but just a reference to the already calculated table!!

How may I achieve the desired behavior?

Answer 1

Here's another way using conditional aggregation to get counts for different criteria

SELECT 
    COUNT(CASE WHEN criteriaA THEN 1 END) AS A
    COUNT(CASE WHEN criteriaB THEN 1 END) AS B
    COUNT(CASE WHEN criteriaC THEN 1 END) AS C
FROM ($myQuery) AS myDerivedTable