关于联合多表按日期查询,求教,谢谢各位

Question

关于联合多表按日期查询,求教,谢谢各位.
例如有两个表info:uid       supplier_id                information                     date  
      1        1                           WII                    2009-04-04 
      2        2                           XBOX360                2009-01-23 
      3        1                           NDS                    2009-05-06 
      4        1                           PS3                    2009-03-27 
      5        2                           XBOX                   2009-06-18 
supplier:uid          name               email                         contact  
      1      A4T             [email protected]                        Susan 
      2      BEBE          [email protected]                         ALICE 
      3     WINNER         [email protected]                        Cathy 其中info表的supplier_id与supplier表的uid是关联的:现在我要select的结果是:
      1      A4T           [email protected]                Susan         NDS                 2009-05-06
      2      BEBE         [email protected]               ALICE         XBOX                2009-06-18
      3     WINNER     [email protected]               Cathy          大概解释一下结果,首先我要所有supplier表的资料,然后要info的资料,如果info有对应的supplier的话,要info的资料是要最近今日日期的那条,本人试了很多都不行,求教各位,附上上述数据库,万分感谢

Answer 1

select s.*,i.*
from supplier s , info i
where s.uid=i.supplier_id
and not exists (select 1 from info where supplier_id=i.supplier_id and `date`>i.date)

Answer 2

select a.*,max(b.date) from info as a left join supplier as b on b.uid=a.supplier_id group by b.uid;

Answer 3

或者
select s.*,i.*
from supplier s , info i, (select supplier_id,max(`date`) as max_date from info group by supplier_id) m
where s.uid=m.supplier_id and i.supplier_id=m.supplier_id and i.date=m.max_date两句上效率根据不同应用有些差异。
当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html

Answer 4

SELECT s.*, i.infomation, MAX(i.`date`)
FROM supplier s LEFT JOIN info i ON s.uid = i.supplier_id
GROUP BY s.uid;

Answer 5

mysql> select * from supplier;
+------+--------+-------------+---------+
| uid  | name   | email       | contact |
+------+--------+-------------+---------+
|    1 | A4T    | [email protected] | Susan   | 
|    2 | BEBE   | [email protected] | ALICE   | 
|    3 | WINNER | [email protected] | Cathy   | 
+------+--------+-------------+---------+
3 rows in set (0.00 sec)mysql> select * from info;    
+------+-------------+-------------+---------------------+
| uid  | supplier_id | information | date                |
+------+-------------+-------------+---------------------+
|    1 |           1 | WII         | 2009-04-04 00:00:00 | 
|    2 |           2 | XBOX360     | 2009-01-23 00:00:00 | 
|    3 |           1 | NDS         | 2009-05-06 00:00:00 | 
|    4 |           1 | PS3         | 2009-03-27 00:00:00 | 
|    5 |           2 | XBOX        | 2009-06-18 00:00:00 | 
+------+-------------+-------------+---------------------+
5 rows in set (0.00 sec)mysql> select a.*,max(b.date) from supplier a left join info b on a.uid=b.supplier_id group by a.uid;
+------+--------+-------------+---------+---------------------+
| uid  | name   | email       | contact | max(b.date)         |
+------+--------+-------------+---------+---------------------+
|    1 | A4T    | [email protected] | Susan   | 2009-05-06 00:00:00 | 
|    2 | BEBE   | [email protected] | ALICE   | 2009-06-18 00:00:00 | 
|    3 | WINNER | [email protected] | Cathy   |                NULL | 
+------+--------+-------------+---------+---------------------+
3 rows in set (0.00 sec)

Answer 6

mysql> select * from supplier;
+------+--------+-------------+---------+
| uid  | name   | email       | contact |
+------+--------+-------------+---------+
|    1 | A4T    | [email protected] | Susan   | 
|    2 | BEBE   | [email protected] | ALICE   | 
|    3 | WINNER | [email protected] | Cathy   | 
+------+--------+-------------+---------+
3 rows in set (0.00 sec)mysql> select * from info;    
+------+-------------+-------------+---------------------+
| uid  | supplier_id | information | date                |
+------+-------------+-------------+---------------------+
|    1 |           1 | WII         | 2009-04-04 00:00:00 | 
|    2 |           2 | XBOX360     | 2009-01-23 00:00:00 | 
|    3 |           1 | NDS         | 2009-05-06 00:00:00 | 
|    4 |           1 | PS3         | 2009-03-27 00:00:00 | 
|    5 |           2 | XBOX        | 2009-06-18 00:00:00 | 
+------+-------------+-------------+---------------------+
5 rows in set (0.00 sec)
mysql> select a.*,b.information,max(b.date) from supplier a left join info b on a.uid=b.supplier_id group by a.uid;    
+------+--------+-------------+---------+-------------+---------------------+
| uid  | name   | email       | contact | information | max(b.date)         |
+------+--------+-------------+---------+-------------+---------------------+
|    1 | A4T    | [email protected] | Susan   | WII         | 2009-05-06 00:00:00 | 
|    2 | BEBE   | [email protected] | ALICE   | XBOX360     | 2009-06-18 00:00:00 | 
|    3 | WINNER | [email protected] | Cathy   | NULL        |                NULL | 
+------+--------+-------------+---------+-------------+---------------------+
3 rows in set (0.00 sec)