Mysql查询发布日期减去7天 [英] Mysql Query Issue Date minus 7 days

问题描述

这总是出现错误，我不知道为什么...

This keeps coming up with an error and I cant figure out why...

SELECT
  i.user_id
FROM (SELECT
    SUM(us.score) AS score_sum,
    us.user_id
      FROM user_scores us
      WHERE us.created >= DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))
      GROUP BY us.user_id) AS i
ORDER BY i.score_sum
LIMIT 1

带有消息的例外"CDbException"

exception 'CDbException' with message

CDbCommand无法执行SQL语句:CDbCommand无法准备SQL语句:SQLSTATE [HY000]:常规错误:1在"7"附近:语法错误.

CDbCommand failed to execute the SQL statement: CDbCommand failed to prepare the SQL statement: SQLSTATE[HY000]: General error: 1 near "7": syntax error.

执行的SQL语句为:

SELECT
  i.user_id
FROM (SELECT
    SUM(us.score) AS score_sum,
    us.user_id
      FROM user_scores us
      WHERE us.created >= DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))
      GROUP BY us.user_id) AS i
ORDER BY i.score_sum
LIMIT 1



<?php

class WinnerCommand extends CConsoleCommand
{
    public function actionSelect()
    {
        $sql = "SELECT i.user_id FROM
            (SELECT SUM(us.score) AS score_sum, us.user_id FROM user_scores us
            WHERE us.created >= DATE(DATE_SUB(NOW(),INTERVAL 7 DAY))
            GROUP BY us.user_id) AS i
            ORDER BY i.score_sum
            LIMIT 1";
        $user_id = Yii::app()->db->createCommand($sql)->queryScalar();
        echo $user_id;
        return $user_id;
    }

}

?>

推荐答案

我无法解释问题，但是您可以消除表达式中的子查询.也许这会有所帮助:

I cannot explain the problem, but you can eliminate the subquery in the expression. Perhaps that will help:

        SELECT us.user_id
        FROM user_scores us
        WHERE us.created >= DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))
        GROUP BY us.user_id)
        ORDER BY SUM(us.score)
        LIMIT 1

这篇关于Mysql查询发布日期减去7天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！