http://www.itpub.net/thread-1873736-1-1.html
Oracle:
MySQL
用相关子查询实现Oracle row_number分析函数的功能，其余一样。
-
create table test (id int,name varchar(10));
-
insert into test values(11,'aaa');
-
insert into test values(12,'aaa');
-
insert into test values(13,'aaa');
-
insert into test values(15,'aaa');
-
insert into test values(16,'bbb');
-
insert into test values(17,'bbb');
-
insert into test values(18,'ccc');
-
insert into test values(19,'zzz');
- insert into test values(20,'zzz');
-
SELECT name, decode(COUNT(*), 1, to_char(MIN(id)), MIN(id) || '-' || MAX(id)) AS b
-
FROM (
-
SELECT id, name, MAX(rn) OVER (PARTITION BY name ORDER BY id) AS rn
-
FROM (
-
SELECT id, name, decode(id - LAG(id) OVER (PARTITION BY name ORDER BY id), 1, 0, id) AS rn
-
FROM test
-
)
-
)
-
GROUP BY name, rn
- ORDER BY name, rn;
-
SELECT NAME, decode(COUNT(*), 1, to_char(MIN(id)), MIN(id) || '-' || MAX(id)) AS b
-
FROM (
-
SELECT t.*, id - ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ID) AS gp
-
FROM TEST t
-
)
-
GROUP BY NAME, gp
- ORDER BY NAME;
MySQL
-
SELECT name, CASE WHEN COUNT(*) = 1 THEN MIN(id) ELSE concat(MIN(id), '-', MAX(id)) END AS r
-
FROM (SELECT id, name, id - (
-
SELECT COUNT(*)
-
FROM test t1
-
WHERE t1.name = t2.name
-
AND t1.id <= t2.id
-
) AS rn
-
FROM test t2
-
ORDER BY name, id
-
) t1
-
GROUP BY name, rn
- ORDER BY name;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29254281/viewspace-1196222/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
TPCC-MySQL基准测试
下一篇：
简易实现Redis监控自动短信告警
请登录后发表评论
登录
全部评论
