如何将这3个查询合并为一个MySQL查询?
问题描述:
下面的每个查询都给出了我个人想要的结果,但我想将它们组合成一个查询。如何将这3个查询合并为一个MySQL查询?
我要输出3个colums:$按行>天,按行$> otherPageCount,按行$> indexCount
有一个表tracking
包含的所有数据。
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`)
答
您可以使用;如果此:
SELECT DATE(`date`),
SUM(IF(`page` = 'index.php', 1, 0)) indexCount,
SUM(IF(`page` != 'index.php', 1, 0)) otherPageCount
FROM `tracking`
WHERE `date` BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY) AND NOW()
GROUP BY DATE(`date`)
juergen's answer做同样的事情,但让若隐。如果你了解它的工作原理,那是更好的答案。