我在PHP中有一个类似于以下内容的数据库查询:
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
...
现在,假设$myQuery
包含以下内容:
SELECT * FROM table1 ORDER BY dateOfSubmition DESC
我只想一次又一次地使用派生表(myDerivedTable
(,而不是每次都计算它!但是mySQL报告了一个错误:
#1146 - Table 'myDB.myDerivedTable' doesn't exist
当然不存在!myDerivedTable
,我并不是要提到数据库中存在的表,而只是对已经计算好的表的引用!!
我怎样才能达到预期的行为?
这是使用条件聚合获取不同条件计数的另一种方法
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
请参考下面,它可能会对您有所帮助。
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1570/html/sqlug/sqlug432.htm