关于分组序号在MySQL中的实现
来源:互联网 发布:painter for mac 2017 编辑:程序博客网 时间:2024/05/16 16:01
好像ORACLE中有相应的函数,可惜在MSSQL 或者MySQL中没有对应的函数。后两者就得用临时表来实现了。
1、表结构以及示例数据。
create table company
(dep char(10) not null,
val1 int unsigned not null
);
insert into company values
('市场部', 26),
('市场部',25),
('市场部',24),
('办公室',16),
('办公室',12),
('研发部',19),
('研发部',11);
2、存储过程
1)、循环实现
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_generate_auto`()
BEGIN
declare cnt int default 0;
declare i int default 0;
drop table if exists tmp;
-- Temporary table to save the result.
create temporary table tmp like company;
alter table tmp add num int unsigned not null;
select count(1) as total from (select count(1) from company where 1 group by dep) T into cnt;
while i < cnt
do
set @stmt = concat('select dep from company where 1 group by dep order by dep asc limit ',i,',1 into @t_dep');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = NULL;
set @num = 0;
set @stmt2 = concat('insert into tmp select dep,val1,@num := @num + 1 as sequence from company where dep = ''',@t_dep,''' order by dep asc');
prepare s1 from @stmt2;
execute s1;
deallocate prepare s1;
set @stmt2 = NULL;
set i = i + 1;
end while;
select * from tmp;
set @t_dep = NULL;
END$$
DELIMITER ;
2)、游标实现DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_generate_auto_cursor`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_generate_auto_cursor`()
BEGIN
declare done1 int default 0;
declare a char(10);
declare i int unsigned default 0;
-- Cursor one to get the group total
declare cur1 cursor for select dep from company group by dep;
declare continue handler for 1329 set done1 = 1;
-- Temporary table to save the result.drop table if exists tmp;
create table tmp like company;
alter table tmp add num int unsigned not null;
open cur1;
while done1 != 1
do
fetch cur1 into a;
if not done1 then
set @i = 0;
begin
declare done2 int default 0;
declare b int unsigned default 0;
declare c int unsigned default 0;
-- Cursor two to get per group total.
declare cur2 cursor for select val1,@i := @i + 1 from company where dep = a;
declare continue handler for 1329 set done2 = 1;
open cur2;
while done2 <> 1
do
fetch cur2 into b,c;
if not done2 then
insert into tmp select a,b,c;
end if;
end while;
close cur2;
end;
end if;
end while;
close cur1;
select * from tmp;
END$$
DELIMITER ;
3、调用结果
call sp_generate_auto();
call sp_generate_auto_cursor();
query result(7 records)
depval1num办公室161办公室122市场部261市场部252市场部243研发部191研发部112uery result(7 records)
depval1num办公室161办公室122市场部261市场部252市场部243研发部191研发部112(7 row(s)returned)
(15 ms taken)
(0 row(s)affected)
(0 ms taken)
(7 row(s)returned)
(16 ms taken)
(0 row(s)affected)
(0 ms taken)
- 关于分组序号在MySQL中的实现
- 关于分组序号在MySQL中的实现
- MySQL 分组后增加分组排序号
- mysql 实现伪序号
- ireport 序号 合计,分组序号
- MYSQL 中关于时间戳在C#中的实现
- mysql实现分组排序
- ORALCE分组序号
- SqlServer 分组显示序号
- RDLC分组序号
- oracle分组序号
- 在XtraReport中实现分组中的行号从新计算
- zTree实现获取当前选中的第一个节点在同级节点中的序号
- 关于mysql分组的探索
- 关于序号
- 关于jqGrid中的分组功能
- MySQL中的分组聚合查询
- mysql查询中实现oracle中的rownum函数的效果,返回每行查询结果的行序号
- JM86之误码掩盖参考代码完全剖析
- 新手上路
- Etract Datasets
- MYSQL 常用命令列表
- 耶鲁法学院院长二〇〇七年秋季迎新致辞
- 关于分组序号在MySQL中的实现
- 英语面试问题及对策集锦:General Question
- Linux 引导过程内幕
- fstream简单应用
- 数字证书介绍
- volatile的作用
- 关于回调函数(2)
- 看淘宝网如何炒热自己
- 聪明的希尔顿(图)