请教一个query in mysql - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

请教一个query in mysql

请教一个query in mysql# Database - 数据库

g*e2008-04-20 07:04

1 楼

Table A:
column1 column2 column3
a x 2
a c 4
a x 1
b d 9
c f 7
c j 3
请问怎样加index based on column1 to create table B:
index column1 column2 column3
1 a x 2
2 a c 4
3 a x 1
1 b d 9
1 c f 7
2 c j 3

我想这样我就可以select * from table B where index=2
2 a c 4
2 c j 3

s*u2008-04-20 07:04

2 楼

It seems it does not make sense to create another table for this query if
the data is dynamic in Table A. Moreoevr, it loses the business logic
meaning by querying table B in your way even you can do this.

g*e2008-04-20 07:04

3 楼

Yes, probably it doesn't make sense here. But I really want to do is:
there are many accounts in the database, each account has made a different
payments along with the payment date.
I want to see the first pay of each account, the second pay of each account.
.....
It's easy to see the first pay of each account, for example,
select accountNum, sum(amount),min(paymentdate) from tablename group by
accountNum (it's ok?)
But how to query the second, third pay...makes me headache. That's why I
post t

t*n2008-04-20 07:04

4 楼

Can you query while sort it by date?

【在 g*********e 的大作中提到】

: Table A:
: column1 column2 column3
: a x 2
: a c 4
: a x 1
: b d 9
: c f 7
: c j 3
: 请问怎样加index based on column1 to create table B:
: index column1 column2 column3

c*t2008-04-20 07:04

5 楼

how about LIMIT and OFFSET.

account.

【在 g*********e 的大作中提到】

: Yes, probably it doesn't make sense here. But I really want to do is:
: there are many accounts in the database, each account has made a different
: payments along with the payment date.
: I want to see the first pay of each account, the second pay of each account.
: .....
: It's easy to see the first pay of each account, for example,
: select accountNum, sum(amount),min(paymentdate) from tablename group by
: accountNum (it's ok?)
: But how to query the second, third pay...makes me headache. That's why I
: post t

s*u2008-04-20 07:04

6 楼

You may try to modify this:
drop table if exists A;
create table A (c1 char(1), c2 char(2), c3 int);
insert into A values ('a', 'x', 0), ('a', 'c', 4), ('a', 'x', 1),('b', 'd', 9),('c', 'f', 7),('c', 'j', 3);
insert into A values ('a', 'd', 10);
SELECT IF(@last=c1, @rank:= @rank + 1,@rank:=1) as rank,@last:=c1 as c11, c3, c2
FROM A, (SELECT @rank:=1, @last:='0') x ORDER BY c1, c3;

k*e2008-04-20 07:04

7 楼

It's easy to see the first pay of each account, for example,
select accountNum, sum(amount),min(paymentdate) from tablename group by
accountNum (it's ok?)

g*e2008-04-20 07:04

8 楼

Thanks for the reply. Seattlewu's method is great but the real data is huge,
hence, we cannot insert data manually; Kissbigeye's idea is practical,
however, I am just going to query from one table.
This table includes thousands of accounts and other variables.
The table looks like:
AccountNum amount paymentdate .......
a xx 04/01/08
a xx 04/05/08
c xx 04/05/08
a xx 04/09/08
.
.
.
I want to get the query 1

s*u2008-04-20 07:04

9 楼

The 'tablename' in Kissbigeye's query is the same table. This is called self
-join -- you probably should try a little more to learn SQL.

huge,

【在 g*********e 的大作中提到】

: Thanks for the reply. Seattlewu's method is great but the real data is huge,
: hence, we cannot insert data manually; Kissbigeye's idea is practical,
: however, I am just going to query from one table.
: This table includes thousands of accounts and other variables.
: The table looks like:
: AccountNum amount paymentdate .......
: a xx 04/01/08
: a xx 04/05/08
: c xx 04/05/08
: a xx 04/09/08

g*e2008-04-20 07:04

10 楼

Wonderful! Many thanks to seattlewu and kissbigeye, you are awesome.