例如
table1
ID NAME
1 a
2 b
3 c
4 d
5 e
table2
CLASS ID
1 2
2 3
table1中的ID即为table2中的ID
用视图将两张表整合起来
即建立的视图如下:
ID NAME CLASS
1 a
2 b 1
3 c 2
4 d
5 e
怎么建立这个视图?我写的代码如下
create view table_v(ID,NAME,CLASS) as select table1.ID,table1.NAME,table2.CLASS where table1.ID=table2.ID;
但是这样得到的结果是
ID NAME CLASS
2 b 1
3 c 2
table1
ID NAME
1 a
2 b
3 c
4 d
5 e
table2
CLASS ID
1 2
2 3
table1中的ID即为table2中的ID
用视图将两张表整合起来
即建立的视图如下:
ID NAME CLASS
1 a
2 b 1
3 c 2
4 d
5 e
怎么建立这个视图?我写的代码如下
create view table_v(ID,NAME,CLASS) as select table1.ID,table1.NAME,table2.CLASS where table1.ID=table2.ID;
但是这样得到的结果是
ID NAME CLASS
2 b 1
3 c 2
create view table_v(ID,NAME,CLASS) as
select table1.ID,table1.NAME,table2.CLASS
from table1 left join table2
on table1.ID=table2.ID
create view table_v(ID,NAME,CLASS) as select table1.ID,table1.NAME,table2.CLASS
LEFT JOIN table2 ON table1.ID=table2.ID;
即
ID NAME
1 a
2 b
2 B
3 c
4 d
5 e
最后要的视图就是
ID NAME CLASS
1 a
2 b 1
2 B 1
3 c 2
4 d
5 e这样怎么处理?
select table1.ID,table1.NAME,table2.CLASS
LEFT JOIN table2 ON table1.ID=table2.ID; 这样不行?
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 2 | B |
| 3 | c |
| 4 | d |
| 5 | e |
+------+------+
6 rows in set (0.00 sec)mysql> select * from table2;
+-------+------+
| class | id |
+-------+------+
| 1 | 2 |
| 2 | 3 |
+-------+------+
2 rows in set (0.00 sec)mysql> create view table_v
-> as
-> select table1.ID,table1.NAME,table2.CLASS
-> from table1 left join table2 on table1.ID=table2.ID;
Query OK, 0 rows affected (0.06 sec)mysql> select * from table_v;
+------+------+-------+
| ID | NAME | CLASS |
+------+------+-------+
| 1 | a | NULL |
| 2 | b | 1 |
| 2 | B | 1 |
| 3 | c | 2 |
| 4 | d | NULL |
| 5 | e | NULL |
+------+------+-------+
6 rows in set (0.00 sec)mysql>
如果还有另一张表table3,他左连table2
即table2 left join table3
怎么处理?
LEFT JOIN table2 ON table1.ID=table2.ID
LEFT JOIN table3 ON table1.ID=table3.ID
语法上如下,用哪个来做join on 的字段具体要看你的数据逻辑了,别人猜不出来你的表结构的。建议一次性把问题提清楚。create view table_v
as
select table1.ID,table1.NAME,table2.CLASS
from (table1 left join table2 on table1.ID=table2.ID)
left join table3 on table1.ID=table3.ID
我的意思是table3和table2关联
例如
table3
SCHOOL CLASS
实验中学 1
LEFT JOIN table2 ON table1.ID=table2.ID
LEFT JOIN table3 ON table2.ID=table3.class
MySQL支持以下JOIN语法。这些语法用于SELECT语句的table_references部分和多表DELETE和UPDATE语句: