sql三表连接查询 - 使用sqlite 演示

表；

人员表，职位表，部门表；

现在要做的是：列出所有人员，以及他们所属的部门和职位；

由于要列出tb_user中的每一条记录，需要用左连接查询。这里要连接3个表；

代码如下；

SELECT u.sName,p.sCaption,d.sCaption FROM tb_user AS u LEFT JOIN (tb_pos AS p LEFT JOIN  tb_dpt AS d ON p.id_Dpt=d.id) ON u.id_Pos=p.id;

运行；

提示 p.sCaption ，没有此列；

检查一下sql；应该没错；

可能括号里面又有别名，这种sql写法，sqlite不能识别；

换个写法；

select tb_user.sName,tb_pos.sCaption, tb_dpt.sCaption FROM tb_user left join tb_pos on tb_user.id_Pos=tb_pos.id left join tb_dpt on tb_pos.id_Dpt=tb_dpt.id;

运行，结果有了；

 

外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。

参阅；

        https://www.runoob.com/sqlite/sqlite-joins.html

全部的cmd操作如下；

Microsoft Windows [版本 6.1.7601]
Copyright (c) 2010 Microsoft Corporation.  All rights reserved.

S:\6\sqlite>sqlite3 test1.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .database
seq  name             file

---  ---------------  ----------------------------------------------------------

0    main             S:\6\sqlite\test1.db

sqlite> create table tb_user (
   ...> id int primary key not null,
   ...> sName varchar(50),
   ...> id_Pos);
sqlite> create table tb_pos (
   ...> id int primary key not null,
   ...> sCaption varchar(50),
   ...> id_Dpt int);
sqlite> create table tb_dpt (
   ...> id int primary key not null,
   ...> sCaption varchar(50));
sqlite> .tables
tb_dpt   tb_pos   tb_user
sqlite> insert into tb_user (1,'李一一',2);
Error: near "1": syntax error
sqlite> insert into tb_user (1,"李一一",2);
Error: near "1": syntax error
sqlite> .shcema tb_user
Error: unknown command or invalid arguments:  "shcema". Enter ".help" for help
sqlite> .schema tb_user
CREATE TABLE tb_user (
id int primary key not null,
sName varchar(50),
id_Pos);
sqlite> drop table tb_user;
sqlite> create table tb_user (
   ...> id int primary key not null,
   ...> sName varchar(50),
   ...> id_Pos int);
sqlite> insert into tb_user values (1,'李一一',2);
sqlite> insert into tb_user values (2,'王不二',3);
sqlite> insert into tb_user values (3,'孙六七',1);
sqlite> insert into tb_pos values (1, '经理', 0);
sqlite> insert into tb_pos values (2, '程序', 1);
sqlite> insert into tb_pos values (3, '', 1);
sqlite> insert into tb_dpt values (1, '设计部');
sqlite> insert into tb_dpt values (2, '运维部');
sqlite> select * from tb_user;
1|李一一|2
2|王不二|3
3|孙六七|1
sqlite> select * from tb_pos;
1|经理|0
2|程序|1
3||1
sqlite> select * from tb_dpt;
1|设计部
2|运维部
sqlite> SELECT u.sName p.sCaption d.sCaption FROM tb_user AS u LEFT JOIN (tb_pos
 AS p LEFT JOIN tb_dpt AS d ON p.id_Dpt=d.id) ON u.id_Pos=p.id;
Error: near ".": syntax error
sqlite> SELECT u.sName p.sCaption d.sCaption FROM tb_user AS u LEFT JOIN (tb_pos
 AS p LEFT JOIN  tb_dpt AS d ON p.id_Dpt=d.id) ON u.id_Pos=p.id;
Error: near ".": syntax error
sqlite> SELECT u.sName p.sCaption d.sCaption FROM tb_user AS u LEFT JOIN (tb_pos
 AS p LEFT JOIN  tb_dpt AS d ON p.id_Dpt=d.id) ON u.id_Pos=p.id;
Error: near ".": syntax error
sqlite> SELECT u.sName,p.sCaption,d.sCaption FROM tb_user AS u LEFT JOIN (tb_pos
 AS p LEFT JOIN  tb_dpt AS d ON p.id_Dpt=d.id) ON u.id_Pos=p.id;
Error: no such column: p.sCaption
sqlite> .schema tb_pos
CREATE TABLE tb_pos (
id int primary key not null,
sCaption varchar(50),
id_Dpt int);
sqlite> select tb_user.sName,tb_pos.sCaption, id_Dpt.sCaption FROM tb_user left
join tb_pos on tb_user.id_Pos=tb_pos.id left join tb_dpt on tb_pos.id_Dpt=tb_dpt
.id;
Error: no such column: id_Dpt.sCaption
sqlite> select tb_user.sName,tb_pos.sCaption, tb_dpt.sCaption FROM tb_user left
join tb_pos on tb_user.id_Pos=tb_pos.id left join tb_dpt on tb_pos.id_Dpt=tb_dpt
.id;
李一一|程序|设计部
王不二||设计部
孙六七|经理|
sqlite>