A表
ID NAME
1 A1
2 A2
3 A3B表
ID DATE DATA
1 20100101 1.1
2 20100102 1.2A表的ID=B表的ID,B表比A表少一条记录,想得到如下结果1 A1 20100101 1.1
2 A2 20100101 1.2
3 A3 20100101 NULL (或0)
ID NAME
1 A1
2 A2
3 A3B表
ID DATE DATA
1 20100101 1.1
2 20100102 1.2A表的ID=B表的ID,B表比A表少一条记录,想得到如下结果1 A1 20100101 1.1
2 A2 20100101 1.2
3 A3 20100101 NULL (或0)
from A left join B on A.id = B.id
FROM [档案] db LEFT OUTER JOIN
数据 dnl ON db.ID = dnl.ID
WHERE (dnl.日期 = 20100102) 这是我写的SQL语句,但结果不对。
2 A2 20100101 1.2 这里为什么不是 20100102 ?
3 A3 20100101 NULL (或0) 这个20100101怎么来的?
+------+------+
| 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>
left join ,左连接。
大体框架是用 left join就ok了!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;
A表
ID NAME TYPE
1 A1 1
2 A2 1
3 A3 1
4 A4 2B表
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
A表的ID=B表的ID,想得到如下结果1 A1 20100101 1.1
2 A2 20100101 2.1
3 A3 NULL NULL
现在的语句
SELECT A.ID, A.NAME, B.DDATE, B.DDATA
FROM A LEFT OUTER JOIN
B ON A.ID = B.ID
WHERE (A.TYPE = 1) AND (B.DDATE = 20100102)只能得到两条记录
1 A1 20100102 1.2
2 A2 20100102 2.2
还少一条
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.1A表的ID=B表的ID,想得到如下结果1 A1 20100101 1.1 (B表中ID=1的有两条,为什么取的是这一条?)
2 A2 20100101 2.1
3 A3 NULL NULL (表B中,难道你找不到一条ID=3的?)问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
+------+------+------+
| 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>先在各自的表先把条件过滤了,再join.不要join之后再过滤
是行的。刚试了