MySQL "Trending"

Question

我目前正在从我的数据库中选择“想法”，但另一个要求是能够获取“趋势想法”，即过去 7 天内投票最多的前 10 个想法。

我选择“想法”的查询是这样的:

  SELECT
  t.id AS 'id',
  CONCAT(t.first_name, ' ', SUBSTRING(t.last_name,1,1)) AS 'name',
  t.votes_up,
  t.votes_down,
  t.votes_aggregate,
  GROUP_CONCAT(DISTINCT tags.name) AS 'tags',
  t.createdon AS 'timestamp'
  FROM (
    SELECT
      ideas.id, first_name, last_name, createdon,
      COALESCE(SUM(case when value > 0 then value end),0) votes_up,
      COALESCE(SUM(case when value < 0 then value end),0) votes_down,
      COALESCE(SUM(value),0) votes_aggregate
      FROM ideas
      LEFT JOIN votes ON ideas.id = votes.idea_id
      GROUP BY ideas.id
  ) as t
  LEFT JOIN tags_rel ON t.id = tags_rel.idea_id
  LEFT JOIN tags ON tags_rel.tag_id = tags.id

如何获取并显示所有投票，但只获取最近 7 天内被投票 (votes_up) 并按 votes_up 数量排序的“想法” ?

这是我的尝试:

  SELECT
      t.id AS 'id',
      CONCAT(t.first_name, ' ', SUBSTRING(t.last_name,1,1)) AS 'name',
      t.votes_up,
      t.votes_down,
      t.votes_aggregate,
      GROUP_CONCAT(DISTINCT tags.name) AS 'tags',
      t.createdon AS 'timestamp'
      FROM (
        SELECT
          ideas.id, first_name, last_name, createdon,
          COALESCE(SUM(case when value > 0 then value end),0) votes_up,
          COALESCE(SUM(case when value < 0 then value end),0) votes_down,
          COALESCE(SUM(value),0) votes_aggregate
          FROM ideas
          LEFT JOIN votes ON ideas.id = votes.idea_id
          GROUP BY ideas.id
      ) as t
      LEFT JOIN tags_rel ON t.id = tags_rel.idea_id
      LEFT JOIN tags ON tags_rel.tag_id = tags.id
  WHERE t.published = 1
  AND (
    SELECT ideas.id,
    COALESCE(SUM(case when value > 0 then value end),0) votes_up
    FROM ideas
    LEFT JOIN votes ON ideas.id = votes.idea_id
    WHERE votes.`timestamp` > (NOW() - INTERVAL 7 DAY)
    GROUP BY ideas.id
  ) as v
  GROUP BY t.id
  ORDER BY v.votes_up DESC

但我收到错误for the right syntax to use near 'as v GROUP BY t.id ORDER BY v.votes_up DESC LIMIT 10'

Answer 1

您在 WHERE 子句中使用“AS v”，这是不可能的:

WHERE t.published = 1
  AND (
    SELECT ideas.id,
    COALESCE(SUM(case when value > 0 then value end),0) votes_up
    FROM ideas
    LEFT JOIN votes ON ideas.id = votes.idea_id
    WHERE votes.`timestamp` > (NOW() - INTERVAL 7 DAY)
    GROUP BY ideas.id
  ) as v