按值更改Mysql组

Question

在MySQL查询中可以按参数更改组吗？例如，如果用户在下拉列表中按“周”选择期间，则按周将结果分组，如果“月”则按月分组，与“年份”相同。我正在使用Symfony2并从请求中获取$period变量。

 $period = $request->get('period');

 // value of $period are: 'day','week', 'month', 'year' 

 $sql_query = "SELECT COUNT(*), DATE_FORMAT(like_date, \"%y-%m-%d\" ) AS day, 
 WEEK(like_date) as week, MONTH(like_date) as month, YEAR(like_date) as year
 FROM user_likes
 GROUP BY date";

在这个查询中，我只按日期分组。是否可以使用CASE内部查询按参数更改组？

Answer 1

RE，您对$period的评论价值是：“日”、“周”、“月”、“年”

这可能是最简单的解决办法。

$sql_query = "SELECT COUNT(*), 
                     DATE_FORMAT(like_date, \"%y-%m-%d\" ) AS date, 
                     DAY(like_date) as day, 
                     WEEK(like_date) as week, 
                     MONTH(like_date) as month, 
                     YEAR(like_date) as year
              FROM user_likes
              GROUP BY $period";

Answer 2

假设$period变量包含其中一个值，{'day','week','month','year'}

SET @period := 'month';
SELECT 
                     COUNT(*), 
                     DATE_FORMAT(like_date, "%Y-%m-%d" ) AS date, 
                     DAY(like_date) as day, 
                     WEEK(like_date) as week, 
                     MONTH(like_date) as month, 
                     YEAR(like_date) as year
FROM user_likes
GROUP BY
     CASE 
        WHEN @period = 'day' THEN DATE_FORMAT(like_date,"%Y-%m-%d")
        WHEN @period = 'week' THEN WEEK(like_date) 
        WHEN @period = 'month' THEN MONTH(like_date) 
        WHEN @period = 'year' THEN YEAR(like_date)
      END

SQL小提琴

N:B:虽然@RiggsFolly已经给出了答案。在看到你的评论后，我强烈想马上给出答案：

@MarcB，但万一我需要比较列才能切换。$period不是列。-

您可以根据@Marc B构建动态查询，请查看SQL演示以获得更好的理解。