mysql版本:5.0.45
表中不存在auto_increment字段
要求:
比如显示下面的数据:
dep val1 序号
市场部 26 1
市场部 25 2
市场部 24 3
办公室 16 1
办公室 12 2
研发部 19 1
研发部 11 2这样的sql怎么写呢??oracle有分析函数,很容易解决。mysql怎么处理好?
表中不存在auto_increment字段
要求:
比如显示下面的数据:
dep val1 序号
市场部 26 1
市场部 25 2
市场部 24 3
办公室 16 1
办公室 12 2
研发部 19 1
研发部 11 2这样的sql怎么写呢??oracle有分析函数,很容易解决。mysql怎么处理好?
from yourTable as a ,yourTable as b
where a.dep=b.dep and a.val1<=b.val1当然也可以用 inner join, 条件相同。.
[align=center]==== 思想重于技巧 ====
[/align]
.
贴子分数<20:对自已的问题不予重视。
贴子大量未结:对别人的回答不予尊重。
.
from yourTable as a ,yourTable as b
where a.dep=b.dep and a.val1<=b.val1
group by a.dep,a.val1.
[align=center]==== 思想重于技巧 ====
[/align]
.
贴子分数<20:对自已的问题不予重视。
贴子大量未结:对别人的回答不予尊重。
.
INNER JOIN TT as b
ON a.dep=b.dep and a.val1<=b.val1 group by a.dep,a.val1如VAL1不唯一,加入自增字段ID,将上述VAL1->ID
1、Table's structure and sample data.
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、Procedure's body.
DELIMITER $$CREATE DEFINER=`root`@`%` PROCEDURE `sp_generate_auto`()
BEGIN
declare cnt int default 0;
declare i int default 0;
drop table if exists tmp;
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 ;
3、Result.call sp_generate_auto();query result(7 records)
dep val1 num
办公室 16 1
办公室 12 2
市场部 26 1
市场部 25 2
市场部 24 3
研发部 19 1
研发部 11 2
select a.dep,a.val1,count(*) as 序号 from company as a
INNER JOIN company as b
ON a.dep=b.dep and a.val1 <=b.val1 group by a.dep,a.val1
就OK了
市场部 26
市场部 26
市场部 26
办公室 16
办公室 12
研发部 19
研发部 11
(select count(*) as 序号 from company as b
where a.dep=b.dep and a.val1<=b.val1 and a.id>=b.id) as px
from company as a
select a.dep,a.val1,a.id,
(select count(*) as 序号 from company as b
where a.dep=b.dep and a.val1 <=b.val1 and a.id>=b.id) as px
from company as a
就OK
1、 借用yueliangdao0608的结构
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、
set @a1='';
set @b1=1;
select dep,val1,
if(@a1='',@b1,if(@a1=dep,@b1:=@b1+1,@b1:=1)),
@a1:=dep
from company
order by dep,val1 desc;