g*********e
发帖数: 29 | 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*******u
发帖数: 19 | 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*********e
发帖数: 29 | 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**n
发帖数: 30 | 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*****t
发帖数: 1879 | 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*******u
发帖数: 19 | 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********e
发帖数: 702 | 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*********e
发帖数: 29 | 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*******u
发帖数: 19 | 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*********e
发帖数: 29 | 10 Wonderful! Many thanks to seattlewu and kissbigeye, you are awesome. |
|
