56,687
社区成员
发帖
与我相关
我的任务
分享
mysql> select * from aaaa;
+------+------+------+
| id | name | type |
+------+------+------+
| 1 | A1 | 1 |
| 2 | A2 | 1 |
| 3 | A3 | 1 |
| 4 | A4 | 2 |
+------+------+------+
4 rows in set (0.00 sec)
mysql> select * from bbbb;
+------+----------+------+
| id | date | data |
+------+----------+------+
| 1 | 20100101 | 1.1 |
| 1 | 20100102 | 1.2 |
| 2 | 20100101 | 2.1 |
| 2 | 20100102 | 2.2 |
| 3 | 20100101 | 3.1 |
| 4 | 20100101 | 4.1 |
+------+----------+------+
6 rows in set (0.00 sec)
mysql> SELECT aaaa.id, aaaa.name,bbbb.date, bbbb.data
-> FROM (select * from aaaa where aaaa.type=1) aaaa LEFT JOIN (select * fr
om bbbb where bbbb.date = 20100102) as bbbb ON aaaa.id = bbbb.id
-> ;
+------+------+----------+------+
| id | name | date | data |
+------+------+----------+------+
| 1 | A1 | 20100102 | 1.2 |
| 2 | A2 | 20100102 | 2.2 |
| 3 | A3 | NULL | NULL |
+------+------+----------+------+
3 rows in set (0.00 sec)
mysql>
create table test.A select 1 as ID ,'A1' NAME union all select 2, 'A2' union all select 3, 'A3';
create table test.B select 1 ID , '20100101' DATE, '1.1' DATA union all select 2, '20100102','1.2';
select a.id,a.name,b.date,b.data
from test.A a left join test.B b
on a.id=b.id;
mysql> select * from a;
+------+------+
| id | name |
+------+------+
| 1 | A1 |
| 2 | A2 |
| 3 | A3 |
+------+------+
3 rows in set (0.02 sec)
mysql> select * from b;
+------+----------+------+
| id | DATE | DATA |
+------+----------+------+
| 1 | 20100101 | 1.1 |
| 2 | 20100102 | 1.2 |
+------+----------+------+
2 rows in set (0.00 sec)
mysql>
mysql> select *
-> from a left join b using(id);
+------+------+----------+------+
| id | name | DATE | DATA |
+------+------+----------+------+
| 1 | A1 | 20100101 | 1.1 |
| 2 | A2 | 20100102 | 1.2 |
| 3 | A3 | NULL | NULL |
+------+------+----------+------+
3 rows in set (0.04 sec)
mysql>