如何将这 3 个查询合并为一个 MySQL 查询? [英] How do I combine these 3 queries into one MySQL query?

问题描述

下面的每个查询都为我提供了我想要的结果，但我想将它们合并为一个查询.

Each of the below queries gives me exactly what I want as a result individually, but I want to combine them into one query.

我想输出 3 列:$row->day, $row->otherPageCount, $row->indexCount

I want to output 3 colums: $row->day, $row->otherPageCount, $row->indexCount

有一个表 tracking 包含所有数据.

There is one table tracking that contains all the data.

1).

SELECT COUNT(`page`) AS indexCount FROM `tracking`
WHERE `page` = 'index.php'
AND `date`
BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY)
AND NOW()
GROUP BY DATE(`date`)

2).

SELECT COUNT(`page`) AS otherPageCount FROM `tracking`
WHERE `page` != 'index.php'
AND `date`
BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY)
AND NOW()
GROUP BY DATE(`date`)

3).

SELECT DATE(`date`) AS day FROM `tracking`
WHERE `date`
BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY)
AND NOW()
GROUP BY DATE(`date`)

推荐答案

SELECT DATE(`date`),
       COUNT(`page`) AS indexCount, 
       SUM(`page` = 'index.php') as idx_count, 
       SUM(`page` <> 'index.php') as not_idx_count
FROM `tracking`
WHERE `date` BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY) AND NOW()
GROUP BY DATE(`date`)

这篇关于如何将这 3 个查询合并为一个 MySQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！