希望利用sql标记同一id,同一name,不同email出现的次数
和excel中countif($A$1:A1,A1)功能相同,在sql中可采用开窗函数ROW_NUMBER()OVER(partition by 分组字段,order by 排序字段) ,其执行顺序晚于 where 、group by 、order by
举例:原始数据为前三列,希望得到第四列
id | name | num | |
1829 | aa | ca1@qq.com | 1 |
1829 | aa | ca2@qq.com | 2 |
1829 | aa | ca3@qq.com | 3 |
1829 | aa | ca4@qq.com | 4 |
1032 | ling | ll@qq.com | 1 |
1032 | ling | lsla@qq.com | 2 |
1032 | ling | lqlo@qq.com | 3 |
1032286976 | liu | ww@qq.com | 4 |
1177845869 | zhao | of@qq.com | 1 |
1177845869 | zhao | dg@qq.com | 2 |
代码如下:
select l.id, l.name, l.email,
row_number() over(partition by l.id,l.name order by l.email) as num
from l;