Virtuel-计数，行 [英] Virtuel-Counting-Row
问题描述
我使用数据库mysql v.4。
我的问题是......
我有一个选择：
select * from user where language =" de";
结果如下：
id name
= ==================
1 max
5 tim
99 otto
所以我需要的是一个带有计数结果索引的虚拟行（pos）：
id name pos
= ================== ===
1最多1
5蒂姆2
99 otto 3
我怎么能用mysql做这个？
我的第一个想法就是用视图制作它，但mysql不能这样做！
MFG Alexander W.
I use the database mysql v.4.
My problem is...
I have a select like:
select * from user where language = "de";
the result are then:
id name
===================
1 max
5 tim
99 otto
so and what i need is a virtuell row (pos) with a counting index of the result:
id name pos
=================== ===
1 max 1
5 tim 2
99 otto 3
how can i make this with mysql?
My first idea was to made that with views but mysql can''t do that!
MFG Alexander W.
推荐答案
< wa ****** @ web.de> ;在消息中写道
<wa******@web.de> wrote in message
我有一个选择：
select * from user where language =" de" ;;
结果如下：
id name
===================
1 max
5 tim
99 otto
所以我需要的是一个虚拟行（pos），其计数索引为
结果：id name pos
============== ===== ===
1 max 1
5 tim 2
99 otto 3
如何用mysql制作这个？
我的第一个想法是用视图制作，但mysql不能这样做！
I have a select like:
select * from user where language = "de";
the result are then:
id name
===================
1 max
5 tim
99 otto
so and what i need is a virtuell row (pos) with a counting index of the result: id name pos
=================== ===
1 max 1
5 tim 2
99 otto 3
how can i make this with mysql?
My first idea was to made that with views but mysql can''t do that!
可能有一些特殊功能，我不确定。但是在5.0
他们将有游标，在目前的版本中你总是可以用
C ++和其他语言来做。
我可以考虑尝试一个额外的表，但它看起来不是最好的
解决方案。
create table temp_product（
id int（1）unsigned auto_increment，
product_id int（1）unsigned not null引用product（id），
主键（id）
）;
....
从temp_product删除;
插入temp_product（product_id）
从产品中选择ID
按名称desc排序;
select temp_product.id，产品。*
产品内部连接temp_product on product.id =
temp_product.product_id;
There might be some special function, I don''t know for sure. But in 5.0
they will have cursors, and in the present version you can always do it in
C++ and other languages.
I can think of trying an extra table, but it doesn''t seem like the best
solution.
create table temp_product (
id int(1) unsigned auto_increment,
product_id int(1) unsigned not null references product(id),
primary key (id)
);
....
delete from temp_product;
insert into temp_product (product_id)
select id from product
order by name desc;
select temp_product.id, product.*
from product inner join temp_product on product.id =
temp_product.product_id;
< wa******@web.de>在消息中写道
<wa******@web.de> wrote in message
我有一个选择：
select * from user where language =" de" ;;
结果如下：
id name
===================
1 max
5 tim
99 otto
所以我需要的是一个虚拟行（pos），其计数索引为
结果：id name pos
============== ===== ===
1 max 1
5 tim 2
99 otto 3
如何用mysql制作这个？
我的第一个想法是用视图制作，但mysql不能这样做！
I have a select like:
select * from user where language = "de";
the result are then:
id name
===================
1 max
5 tim
99 otto
so and what i need is a virtuell row (pos) with a counting index of the result: id name pos
=================== ===
1 max 1
5 tim 2
99 otto 3
how can i make this with mysql?
My first idea was to made that with views but mysql can''t do that!
可能有一些特殊功能，我不确定。但是在5.0
他们将有游标，在目前的版本中你总是可以用
C ++和其他语言来做。
我可以考虑尝试一个额外的表，但它看起来不是最好的
解决方案。
create table temp_product（
id int（1）unsigned auto_increment，
product_id int（1）unsigned not null引用product（id），
主键（id）
）;
....
从temp_product删除;
插入temp_product（product_id）
从产品中选择ID
按名称desc排序;
select temp_product.id，产品。*
产品内部连接temp_product on product.id =
temp_product.product_id;
There might be some special function, I don''t know for sure. But in 5.0
they will have cursors, and in the present version you can always do it in
C++ and other languages.
I can think of trying an extra table, but it doesn''t seem like the best
solution.
create table temp_product (
id int(1) unsigned auto_increment,
product_id int(1) unsigned not null references product(id),
primary key (id)
);
....
delete from temp_product;
insert into temp_product (product_id)
select id from product
order by name desc;
select temp_product.id, product.*
from product inner join temp_product on product.id =
temp_product.product_id;
< wa ****** @ web.de>在消息中写道
news：b1 ************************** @ posting.google.c om ...
<wa******@web.de> wrote in message
news:b1**************************@posting.google.c om...
我使用数据库mysql v.4。
我的问题是......
我有一个选择：
select * from user where language =" de";
结果是：
id name
===================
1 max
5 tim
99 otto
所以我需要的是一个虚拟行（pos），其计数索引为
结果：id名称pos
=================== ===
1 max 1
5 tim 2
99 otto 3
I use the database mysql v.4.
My problem is...
I have a select like:
select * from user where language = "de";
the result are then:
id name
===================
1 max
5 tim
99 otto
so and what i need is a virtuell row (pos) with a counting index of the result: id name pos
=================== ===
1 max 1
5 tim 2
99 otto 3
假设下表：
CREATE TABLE student（
s_id int（11）NOT NULL auto_increment，
名称varchar（15），
PRIMARY KEY（s_id）
）;
添加一些行和删除（只是为了让你在所有情况下都能工作
独立于主键）
inse rt into student（name）values（''tom''）;
插入学生（姓名）值（''bob''）;
插入学生（ name）values（''sue''）;
插入学生（姓名）值（''mike''）;
插入学生（姓名）值（ ''joe''）;
插入学生（姓名）值（''zoe''）;
插入学生（姓名）值（''harpo' '）;
从学生中删除name =''bob'';
现在，注意mc是行数......独立于s_id;
选择a.name，sum（1）为mc
来自学生a，学生b
其中a.s_id< ; = b.s_id
由a.s_id分组，a。由mc命令;
+ ------- + ------ +
|名字| mc |
+ ------- + ------ +
| harpo | 1 |
|佐伊| 2 |
|乔| 3 |
|迈克| 4 |
|起诉| 5 |
|汤姆| 6 |
+ ------- + ------ +
6行套装（0.00秒）
参考（提示22）
http ：//osdn.dl.sourceforge.net/sourc...ADME_mysql.txt
希望这会有所帮助，
Mike Chirico
Assume the following table:
CREATE TABLE student (
s_id int(11) NOT NULL auto_increment,
name varchar(15),
PRIMARY KEY (s_id)
);
Add some rows and delete (just to convice you this works in all cases
independent of the primary key)
insert into student (name) values (''tom'');
insert into student (name) values (''bob'');
insert into student (name) values (''sue'');
insert into student (name) values (''mike'');
insert into student (name) values (''joe'');
insert into student (name) values (''zoe'');
insert into student (name) values (''harpo'');
delete from student where name = ''bob'';
Now, note mc is the row count...independent of s_id;
select a.name,sum(1) as mc
from student a, student b
where a.s_id <= b.s_id
group by a.s_id, a.name order by mc;
+-------+------+
| name | mc |
+-------+------+
| harpo | 1 |
| zoe | 2 |
| joe | 3 |
| mike | 4 |
| sue | 5 |
| tom | 6 |
+-------+------+
6 rows in set (0.00 sec)
Reference (TIP 22)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt
Hope this helps,
Mike Chirico
这篇关于Virtuel-计数，行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
