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

debugcn 发表于 Dev

亚当·弗雷米勒

我正在尝试从同一张表中加入几个不同的值。我正在尝试从不同的时间范围返回查询中的打印率（在月/日/年/周等中已打印了多少），并且在执行以下查询时遇到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;

然后，如果我要添加多个联接，查询将看起来像这样吗？

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

戈登·利诺夫（Gordon Linoff）

仅使用条件聚合怎么样？

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;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。