mysql - 结合数据库查询

Question

如何组合这些从我的数据库中提取统计信息的 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())");

Answer 1

如果您想在单个查询中获取所有数据，您有两种选择:

1. 使用 UNION 查询 ( as sugested by bishop in his answer )
2. 调整查询以在一行中获得您需要的内容

我将展示选项 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;

希望对你有帮助