如何将这些从数据库中提取统计信息的sql查询组合在一起以获得更好的性能?
$total= mysql_query("SELECT COUNT(*) as number, SUM(order_total) as sum FROM history");
$month = mysql_query("SELECT COUNT(*) as number, SUM(order_total) as sum FROM history WHERE date >= UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -30 DAY))");
$day = mysql_query("SELECT COUNT(*) as number, SUM(order_total) as sum FROM history WHERE date >= UNIX_TIMESTAMP(CURDATE())");
如果您想在一个查询中查询所有数据,您有两个选择:
- 使用
UNION
查询(如主教在他的回答中建议的) - 调整查询以在单行中获得所需的内容
我将展示选项2(选项1已经讨论过了)。
注意:我使用用户变量(在init
子查询的东西),以避免一次又一次地编写表达式。此外,为了过滤聚合数据,我使用case ... end
表达式。
select
-- Your first query:
count(*) as number, sum(order_total) as `sum`
-- Your second query:
, sum(case when `date` <= @prev_date then 1 else 0 end) as number_prev
, sum(case when `date` <= @prev_date then order_total else 0 end) as sum_prev
-- Your third query:
, sum(case when `date` <= @cur_date then 1 else 0 end) as number_cur
, sum(case when `date` <= @cur_date then order_total else 0 end) as sum_cur
from (
select @cur_date := unix_timestamp(curdate())
, @prev_date := unix_timestamp(date_add(curdate(), interval -30 day))
) as init
, history;
希望能有所帮助
由于查询具有相同的列结构,您可以要求MySQL将它们与UNION
操作结合起来:
(SELECT 'total' AS kind, COUNT(*) as number, SUM(order_total) as sum FROM history~
UNION
(SELECT 'by-month' AS kind, COUNT(*) as number, SUM(order_total) as sum FROM history WHERE date <= UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -30 DAY)))
UNION
(SELECT 'by-day' AS kind, COUNT(*) as number, SUM(order_total) as sum FROM history WHERE date <= UNIX_TIMESTAMP(CURDATE()))