在mysql中使用内部联接和别名

I'm trying to join several different values from the same table. I'm trying to return printing rates in the query from different time horizons (how much has been printed in the month/day/year/week etc.) and I am getting a 1064 error when I execute the query below. What could the explanation be?

我正在尝试从同一个表中加入几个不同的值。我试图从不同的时间范围返回查询中的打印率(在月/日/年/周等打印了多少),当我执行下面的查询时,我收到1064错误。可以解释的是什么?

SELECT t1.station_name, t1.yearpages, t2.monthpages
FROM (
    SELECT station_name, SUM(print_pages) yearpages
    FROM `file_prints`
    WHERE year(print_date) = 2014;
) t1
INNER JOIN (
    SELECT station_name, sum(print_pages) monthpages
    FROM `file_prints`
    WHERE month(print_date) = 2;
) ON t1.station_name = t2.station_name;

Then, if I were to add multiple joins, would the query look like this?

然后,如果我要添加多个连接,查询是否会像这样?

SELECT t1.station_name, t1.station_name as db_name, t1.yearpages, t2.monthpages, t3.daypages, t4.weekpages
FROM (
    SELECT station_name, sum(print_pages) yearpages
    FROM `file_prints`
    WHERE year(print_date) = $year;
    GROUP BY station_name
) t1
INNER JOIN (
    SELECT station_name, sum(print_pages) monthpages
    FROM `file_prints`
    WHERE month(print_date) = $month;
    GROUP BY station_name
) t2 ON t1.station_name = t2.station_name 
INNER JOIN (
    SELECT station_name, sum(print_pages) daypages
    FROM `file_prints`
    WHERE dayofmonth(print_date) = $day;
    GROUP BY station_name
) t3 ON t2.station_name = t3.station_name
INNER JOIN (
    SELECT station_name, sum(print_pages) weekpages
    FROM `file_prints`
    WHERE week(print_date) = $week;
    GROUP BY station_name
) t4 ON t3.station_name = t4.station_name

1 个解决方案

#1

How about just using conditional aggregation?

如何使用条件聚合?

SELECT station_name,
       sum(case when year(print_date) = $year then print_pages end) as yearpages,
       sum(case when month(print_date) = $month then print_pages end) as monthpages,
       sum(case when day(print_date) = $day then print_pages end) as daypages,
       sum(case when week(print_date) = $week then print_pages end) as weekpages
FROM `file_prints`
GROUP BY station_name;