select * from test; create temporary table tmp select distinct * from test where to_days(date_col)>=to_days('2004-03-01') and to_days(date_col)<=to_days('2004-07-01'); select * from tmp; select user_col,sum(mount) as mount from tmp group by user_col order by mount desc; drop table tmp; 执行上面的语句如下:mysql> select * from test; +----------+------------+-------+ | user_col | date_col | mount | +----------+------------+-------+ | a | 2004-03-01 | 5 | | b | 2004-05-03 | 6 | | a | 2004-03-01 | 5 | | c | 2004-06-03 | 6 | | b | 2004-05-03 | 6 | | b | 2004-05-03 | 6 | | c | 2004-05-06 | 3 | +----------+------------+-------+ 7 rows in set (0.00 sec)mysql> create temporary table tmp select distinct * from test where to_days(date _col)>=to_days('2004-03-01') and to_days(date_col)<=to_days('2004-07-01'); Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings: 0mysql> select * from tmp; +----------+------------+-------+ | user_col | date_col | mount | +----------+------------+-------+ | a | 2004-03-01 | 5 | | b | 2004-05-03 | 6 | | c | 2004-06-03 | 6 | | c | 2004-05-06 | 3 | +----------+------------+-------+ 4 rows in set (0.00 sec)mysql> select user_col,sum(mount) as mount from tmp group by user_col order by m ount desc; +----------+-------+ | user_col | mount | +----------+-------+ | c | 9 | | b | 6 | | a | 5 | +----------+-------+ 3 rows in set (0.00 sec)mysql> drop table tmp; Query OK, 0 rows affected (0.00 sec)
I just did the same thing like your guidance yesterday except one thing: the last sentence "drop table tmp" I searched for the information about the "temporary table" which says the temporary table will auto-dispear when your process ends. I have test this just though IE and it looks well, now I wonder need I insert the "drop" action at the end of my program. ( the tools JSP,Tomcat, OS is Linux) thanks
It's up to u , But I suggested drop the temporary table
create temporary table tmp select distinct * from test where to_days(date_col)>=to_days('2004-03-01') and to_days(date_col)<=to_days('2004-07-01');
select * from tmp;
select user_col,sum(mount) as mount from tmp group by user_col order by mount desc;
drop table tmp;
执行上面的语句如下:mysql> select * from test;
+----------+------------+-------+
| user_col | date_col | mount |
+----------+------------+-------+
| a | 2004-03-01 | 5 |
| b | 2004-05-03 | 6 |
| a | 2004-03-01 | 5 |
| c | 2004-06-03 | 6 |
| b | 2004-05-03 | 6 |
| b | 2004-05-03 | 6 |
| c | 2004-05-06 | 3 |
+----------+------------+-------+
7 rows in set (0.00 sec)mysql> create temporary table tmp select distinct * from test where to_days(date
_col)>=to_days('2004-03-01') and to_days(date_col)<=to_days('2004-07-01');
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select * from tmp;
+----------+------------+-------+
| user_col | date_col | mount |
+----------+------------+-------+
| a | 2004-03-01 | 5 |
| b | 2004-05-03 | 6 |
| c | 2004-06-03 | 6 |
| c | 2004-05-06 | 3 |
+----------+------------+-------+
4 rows in set (0.00 sec)mysql> select user_col,sum(mount) as mount from tmp group by user_col order by m
ount desc;
+----------+-------+
| user_col | mount |
+----------+-------+
| c | 9 |
| b | 6 |
| a | 5 |
+----------+-------+
3 rows in set (0.00 sec)mysql> drop table tmp;
Query OK, 0 rows affected (0.00 sec)
I searched for the information about the "temporary table" which says the temporary table will auto-dispear when your process ends. I have test this just though IE and it looks well, now I wonder need I insert the "drop" action at the end of my program. ( the tools JSP,Tomcat, OS is Linux)
thanks