mysql 如何不使用任何函数或过程为每个用户运行此查询？

h7appiyu 于 2023-02-11 发布在 Mysql

关注(0)|答案(1)|浏览(81)

下面是我的查询，它返回特定用户=27的条纹：

SELECT MAX(sum) AS streak
FROM (
  SELECT
    game_date,
    IF(points > 0, @sum:=@sum+1, @sum:=0) AS sum
  FROM (
    SELECT
      game_date,
      (SELECT COUNT(*) FROM point WHERE user_id = 27 AND bet_id = b.id AND goals > 0) AS points
    FROM bet b
    WHERE game_date < NOW()
    ORDER BY game_date DESC
  ) t1, (SELECT @sum:=0) t2
) t;

**我的尝试：**它在我的本地MySQL上运行成功，但在实时phpMyAdmin上给出一个错误，即user_id是where子句中的未知列。

SELECT DISTINCT user_id,(
SELECT MAX(SUM) AS streak
FROM (
  SELECT
    game_date,
    IF(points > 0, @sum:=@sum+1, @sum:=0) AS SUM
  FROM (
    SELECT
      game_date,
      (SELECT COUNT(*) FROM POINT WHERE user_id = p.user_id AND bet_id = b.id AND goals > 0) AS points
    FROM bet b
    WHERE game_date < NOW()
    ORDER BY game_date DESC
  ) t1, (SELECT @sum:=0) t2
) t) AS streak FROM POINT p;

mysql

来源：https://stackoverflow.com/questions/75390630/how-can-i-run-this-query-for-each-user-without-using-any-function-or-procedure

1条答案

按热度按时间

ut6juiuv1#

如果没有任何样本数据来测试，我可能会偏离主题，但给予这个：

SELECT user_id, MAX(SUM) AS streak
FROM (
    SELECT b.game_date, u.user_id,
        IF(
            IFNULL(p.bet_id, 0) > 0,
            IF(@prev_user = u.user_id, @sum := @sum + 1, @sum := 1),
            @sum := 0
        ) AS SUM,
        @prev_user := u.user_id
    FROM bet b
    CROSS JOIN (SELECT DISTINCT user_id FROM `POINT`) u
    LEFT JOIN `POINT` p ON b.id = p.bet_id AND u.user_id = p.user_id AND p.goals > 0
    JOIN (SELECT @sum:=0, @prev_user:=0) vars
    WHERE b.game_date < NOW()
    ORDER BY u.user_id ASC, b.game_date DESC
) t
GROUP BY user_id;

这是gaps-and-islands问题的一个变体，该查询采用了一种不同的（“现代”）方法，使用ROW_NUMBER（）window function-

SELECT user_id, IFNULL(MAX(streak), 0)
FROM (
    SELECT *, rn1 - rn2,
        IF (
            goals IS NOT NULL,
            ROW_NUMBER() OVER (PARTITION BY user_id, rn1 - rn2 ORDER BY game_date),
            NULL
        ) AS streak
    FROM (
        SELECT b.id AS bet_id, b.game_date, u.user_id, p.goals,
            ROW_NUMBER() OVER (ORDER BY u.user_id, game_date) rn1,
            ROW_NUMBER() OVER (PARTITION BY u.user_id, IF(p.bet_id IS NULL, 0, 1) ORDER BY game_date) rn2
        FROM bet b
        CROSS JOIN (SELECT DISTINCT user_id FROM `POINT`) u
        LEFT JOIN `POINT` p ON b.id = p.bet_id AND u.user_id = p.user_id AND p.goals > 0
        WHERE b.game_date < NOW()
    ) t1
) t2
GROUP BY user_id;

赞(0）回复(0）举报 2023-02-11

我来回答

mysql 如何不使用任何函数或过程为每个用户运行此查询？

1条答案

相关问题

热门标签

最新问答