查询问题最新时间

Question

表A
date                        PROVIDER_ID     sum
2009-06-01 08:03:30             2            10
2009-06-01 08:03:30             3            20
2009-06-01 08:03:30             4            10 
2009-06-01 09:03:30             2            50
2009-06-09 08:03:30             4            20
2009-06-10 18:03:30             2            30
我想得到的记录是：2009-06-10 18:03:30             2            302009-06-09 08:03:30             4            202009-06-01 08:03:30             3            20这三条。请问SQL语句应该怎么写？

Answer 1

select * from tb_name a where not exists (select 1 from tb_name b where b.date<a.date and b.sum>a.sum)

Answer 2

刚的错了，改下：
select * from 表名 a where not exists (select 1 from 表名 b where b.PROVIDER_ID=a.PROVIDER_ID and b.date >a.date)

Answer 3

我要的数据是：PROVIDER_ID最大时间的那条记录，和SUM没有关系

Answer 4

SELECT a.* from tty2 a left join tty2 b
on (a.date=b.date and a.sum<b.sum ) or (a.date<b.date and a.sum>b.sum) where b.date is null

Answer 5

date                        PROVIDER_ID    sum 
2009-06-01 08:03:30            2            10 
2009-06-01 08:03:30            3            20 
2009-06-01 08:03:30            4            10 
2009-06-01 09:03:30            2            50 
2009-06-09 08:03:30            4            20 
2009-06-10 18:03:30            2            30
结果：
2009-06-01 08:03:30            4            10 
2009-06-09 08:03:30            4            20 
2009-06-10 18:03:30            2            30

Answer 6

也可以这样写：select * from 表名 a where a.date= (select max(b.date) from 表名 b where b.PROVIDER_ID=a.PROVIDER_ID )

Answer 7

你究竟要什么结果？从你一楼的结果来看
SELECT a.* from tty2 a left join tty2 b 
on (a.date=b.date and a.sum <b.sum ) or (a.date <b.date and a.sum>b.sum) where b.date is null
应该可以

Answer 8

他要的是每个PROVIDER_ID值最大时间值的那条记录。

Answer 9

select a.* from tty2 a
inner join
(
SELECT PROVIDER_ID,max(date) as ma from tty2 group by PROVIDER_ID) b
on a.date=b.ma and a.PROVIDER_ID=b.PROVIDER_ID

Answer 10

mysql> select * from t_xhj12077021;
+---------------------+-------------+------+
| date                | PROVIDER_ID | sum  |
+---------------------+-------------+------+
| 2009-06-01 08:03:30 |           2 |   10 |
| 2009-06-01 08:03:30 |           3 |   20 |
| 2009-06-01 08:03:30 |           4 |   10 |
| 2009-06-01 09:03:30 |           2 |   50 |
| 2009-06-09 08:03:30 |           4 |   20 |
| 2009-06-10 18:03:30 |           2 |   30 |
+---------------------+-------------+------+
6 rows in set (0.00 sec)mysql>
mysql> select * from t_xhj12077021 a
    -> where not exists (select 1 from t_xhj12077021 where `date`=a.`date` and `
sum`>a.`sum`);
+---------------------+-------------+------+
| date                | PROVIDER_ID | sum  |
+---------------------+-------------+------+
| 2009-06-01 08:03:30 |           3 |   20 |
| 2009-06-01 09:03:30 |           2 |   50 |
| 2009-06-09 08:03:30 |           4 |   20 |
| 2009-06-10 18:03:30 |           2 |   30 |
+---------------------+-------------+------+
4 rows in set (0.06 sec)mysql>


Answer 11

楼主给的结果好象不对吧。
根据你的数据结果好象如下。mysql> select * from t_xhj12077021 a
    -> where not exists (select 1 from t_xhj12077021 where `date`=a.`date` and PROVIDER_ID>a.PROVIDER_ID);
+---------------------+-------------+------+
| date                | PROVIDER_ID | sum  |
+---------------------+-------------+------+
| 2009-06-01 08:03:30 |           4 |   10 |
| 2009-06-01 09:03:30 |           2 |   50 |
| 2009-06-09 08:03:30 |           4 |   20 |
| 2009-06-10 18:03:30 |           2 |   30 |
+---------------------+-------------+------+
4 rows in set (0.00 sec)mysql>mysql> select a.*
    -> from  t_xhj12077021 a inner join (select `date` ,max(PROVIDER_ID) as m_PROVIDER_ID from t_xhj12077021 group by `date`) b
    ->  on a.`date`=b.`date` and a.PROVIDER_ID=b.m_PROVIDER_ID;
+---------------------+-------------+------+
| date                | PROVIDER_ID | sum  |
+---------------------+-------------+------+
| 2009-06-01 08:03:30 |           4 |   10 |
| 2009-06-01 09:03:30 |           2 |   50 |
| 2009-06-09 08:03:30 |           4 |   20 |
| 2009-06-10 18:03:30 |           2 |   30 |
+---------------------+-------------+------+
4 rows in set (0.06 sec)mysql>

Answer 12

呵呵，楼主是要按PROVIDER_ID分组，取最大date，楼主应该说明一下

Answer 13

按PROVIDER_ID分组，取最大datemysql> select * from t_xhj12077021;
+---------------------+-------------+------+
| date                | PROVIDER_ID | sum  |
+---------------------+-------------+------+
| 2009-06-01 08:03:30 |           2 |   10 |
| 2009-06-01 08:03:30 |           3 |   20 |
| 2009-06-01 08:03:30 |           4 |   10 |
| 2009-06-01 09:03:30 |           2 |   50 |
| 2009-06-09 08:03:30 |           4 |   20 |
| 2009-06-10 18:03:30 |           2 |   30 |
+---------------------+-------------+------+
6 rows in set (0.00 sec)mysql>
mysql> select *  from t_xhj12077021 a
    -> where not exists(select 1 from t_xhj12077021 where PROVIDER_ID=a.PROVIDER
_ID and `date`>a.`date`);
+---------------------+-------------+------+
| date                | PROVIDER_ID | sum  |
+---------------------+-------------+------+
| 2009-06-01 08:03:30 |           3 |   20 |
| 2009-06-09 08:03:30 |           4 |   20 |
| 2009-06-10 18:03:30 |           2 |   30 |
+---------------------+-------------+------+
3 rows in set (0.00 sec)mysql> select a.*
    -> from  t_xhj12077021 a inner join (select PROVIDER_ID ,max(`date`) as m_da
te from t_xhj12077021 group by PROVIDER_ID) b
    ->  on a.PROVIDER_ID=b.PROVIDER_ID and a.date=b.m_date;
+---------------------+-------------+------+
| date                | PROVIDER_ID | sum  |
+---------------------+-------------+------+
| 2009-06-01 08:03:30 |           3 |   20 |
| 2009-06-09 08:03:30 |           4 |   20 |
| 2009-06-10 18:03:30 |           2 |   30 |
+---------------------+-------------+------+
3 rows in set (0.00 sec)mysql>

Answer 14

我一开始以为在某一时间PROVIDER_ID最大的记录，原来是某一PROVIDER_ID的最近时间记录~~

Answer 15

select * from (select * from t2 order by date desc) a group by provider_id
先把表按date降序排列并当成临时表。再从这个临时表时取值。用组函数group by来实现。因为group by 只取指定字段的第一个数据。所以只要把date降序排列。就可以取最大的date和取到相应的sum provider_id