如图,如何获得一个用户 number 和大于等于 10 的那一条数据

image.png

现有一个数据表,需要获取同一个用户,number 累计达到大于等于 10 的那一条数据,也就是第 6 行和第 10 行。

大佬们这个 SQL 怎么写。

  • SQL
    99 引用 • 285 回帖 • 3 关注
  • Q&A

    提问之前请先看《提问的智慧》,好的问题比好的答案更有价值。

    1732 引用 • 11428 回帖 • 583 关注

赞助商 我要投放

被采纳的回答
  • PeterChu
    id user_id number
    1 1 1
    2 1 2
    3 1 2
    4 1 4
    5 1 7
    6 1 2
    7 2 1
    8 2 3
    9 2 8
    10 2 6
    SELECT
    	* 
    FROM
    	(
    		SELECT
    			c.id,
    			c.user_id,
    			c.number,
    			sum( c.number ) over ( PARTITION BY c.user_id rows unbounded preceding ) AS f 
    		FROM
    			user001 c 
    	) r 
    WHERE
    	r.f >= 10 
    GROUP BY
    	r.user_id 
    ORDER BY
    	r.id;
    

    结果:
    image.png

13 回帖
请输入回帖内容 ...
  • CismonX 1 1 评论

    我在本地模拟了一下,借助窗口函数可以轻松解决。(注意,MySQL 8.0 以后的版本才支持这一特性)

    select distinct r.user_id,
                    first_value(r.number) over (partition by r.user_id) as number
    from (
             select c.user_id,
                    c.number,
                    sum(c.number) over (partition by c.user_id rows unbounded preceding) AS f
             from (
                      select 1 as user_id, 1 as number
                      union all
                      select 1 as user_id, 2 as number
                      union all
                      select 1 as user_id, 2 as number
                      union all
                      select 1 as user_id, 4 as number
                      union all
                      select 1 as user_id, 7 as number
                      union all
                      select 1 as user_id, 2 as number
                      union all
                      select 2 as user_id, 1 as number
                      union all
                      select 2 as user_id, 8 as number
                      union all
                      select 2 as user_id, 3 as number
                      union all
                      select 2 as user_id, 6 as number
                  ) c
             group by c.user_id, c.number
         ) r
    where r.f >= 10
    group by r.user_id, r.number;
    

    🤔 group by c.user_id, c.number 为什么 c.number
    PeterChu
  • 其他回帖
  • hjljy 1 评论

    楼主要是 SQL 实现了,求一观!!! 完全没有思路

    SQL 实现参考我在楼下的回复
    CismonX
  • Eddie

    Oracle 可以用 over 函数

  • PeterChu
    id user_id number
    1 1 1
    2 1 2
    3 1 2
    4 1 4
    5 1 7
    6 1 2
    7 2 1
    8 2 3
    9 2 8
    10 2 6
    SELECT
    	* 
    FROM
    	(
    		SELECT
    			c.id,
    			c.user_id,
    			c.number,
    			sum( c.number ) over ( PARTITION BY c.user_id rows unbounded preceding ) AS f 
    		FROM
    			user001 c 
    	) r 
    WHERE
    	r.f >= 10 
    GROUP BY
    	r.user_id 
    ORDER BY
    	r.id;
    

    结果:
    image.png

  • 查看更多回帖