在mysql中查询表和哪些表关联_sql语句（数据库中表和表自己本身关联查询）

例子1：

select a.name 城市,b.name 地区,c.name 乡镇,d.name 道路

from address a

join address  b on b.parentid = a.id

join address  c on c.parentid = b.id

join address  d on d.parentid = c.id

where d.name = '凌兆路';

例子2：

在数据库中有这样一张表格，其中主要包含三个字段，GoodsId(货物编号),TypeId(类型编号),State(状态)。货物编号不重复，GoodsId与TypeId之间属于N对1的关系，State为状态主要分为两种“null”和“1”，“null”表示该货物未卖出UnSold，“1”表示货物Sold。现在有如下业务，统计出类型编号下有多少货物Sold，多少货物UnSold。

最后需要统计出(输出)的结果为：

建表语句

-- 建表语句

CREATE TABLE Goods(

GoodsId VARCHAR(255),

TypeId VARCHAR(255),

State VARCHAR(255)

);

--- 插入语句

insert into Goods(GoodsId,TypeId,State) values('001','1','1');

insert into Goods(GoodsId,TypeId,State) values('002','1','null')

insert into Goods(GoodsId,TypeId,State) values('003','1','null');

insert into Goods(GoodsId,TypeId,State) values('004','1','null');

insert into Goods(GoodsId,TypeId,State) values('005','1','null');

insert into Goods(GoodsId,TypeId,State) values('006','1','1');

insert into Goods(GoodsId,TypeId,State) values('001','2','1');

insert into Goods(GoodsId,TypeId,State) values('002','2','null');

insert into Goods(GoodsId,TypeId,State) values('003','2','null');

insert into Goods(GoodsId,TypeId,State) values('004','2','1');

insert into Goods(GoodsId,TypeId,State) values('005','2','null');

insert into Goods(GoodsId,TypeId,State) values('006','2','null');

insert into Goods(GoodsId,TypeId,State) values('007','2','1');

insert into Goods(GoodsId,TypeId,State) values('008','2','null');

insert into Goods(GoodsId,TypeId,State) values('001','3','1');

insert into Goods(GoodsId,TypeId,State) values('002','3','null');

insert into Goods(GoodsId,TypeId,State) values('003','3','null');

在上述问题中无疑所要用到的重点就是表格自身关联，以及计数方法count函数，以及分组group by等。在此根据题目提出的问题，我们需要用到的表格连接方法为内连接(inner join)。因为在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

select g1.TypeId,count(*)AS Sold,g3.UnSold from Goods g1

inner join

(select TypeId,count(*)AS UnSold from Goods g2 WHERE State='null'

group by g2.TypeId) g3

on g1.TypeId = g3.TypeId

WHERE State ='1' group by g1.TypeId,g3.UnSold