详细聊聊关于Mysql联合查询的那些事儿

1

select ProID,ProName from T_Province

1

2

3

4

select CityID,CityName from T_City

where ProID = (

select ProID from T_Province where ProName="湖南省"

);

1

2

3

4

5

6

select ProID,ProName from T_Province

union

select CityID,CityName from T_City

where ProID = (

select ProID from T_Province where ProName="湖南省"

);

1

2

3

4

5

6

select ProID,ProName from T_Province

union all

select CityID,CityName from T_City

where ProID = (

select ProID from T_Province where ProName="湖南省"

);

1

2

select count(CityID) from T_City

where ProID = (select ProID from T_Province where ProName="湖北省")

1

2

3

4

5

select ProName,CityName from（

T_City join T_Province

on T_City.ProID = T_Province.ProID

）

where ProName="湖北省"

1

2

3

4

5

6

select T_City.ProID,ProName,count(CityID) as cc from(

T_City join T_Province

on T_City.ProID = T_Province.ProID

)

group by T_City.ProID

order by cc desc;

1

2

3

4

5

6

select tc.ProID,ProName,count(CityID) as cc from(

T_City tc join T_Province tp

on T_City.ProID = T_Province.ProID

)

group by tc.ProID

order by cc desc;

1

2

3

4

5

6

select CityName,count(DisName) disCount from (

T_City tc join T_District td

on tc.CityID = td.CityID

)

group by CityName

having disCount > 20;

1

2

3

4

5

6

7

8

9

select tp.ProName,tcd.CityName,tcd.ci from

(

select ProID,CityName,count(ID) ci from(T_City tc join T_District td on tc.CityID = td.CityID)

group by tc.CityID

order by ci desc

limit 3

)tcd

join T_Province tp on tcd.ProID = tp.ProID;

1

2

3

4

select * from(

T_Province tp join T_City tc

on tp.ProID = tc.ProID

);

1

2

3

4

select * from(

T_Province tp left join T_City tc

on tp.ProID = tc.ProID

);

1

2

3

4

select * from(

T_Province tp right join T_City tc

on tp.ProID = tc.ProID

);