15.22数据库(22):MySQL多表联合查询

Amiee7 · · 114 次点击 · 开始浏览    置顶
备注:所有查询基于前面章节中建立的中国数据库 ``` /*联合查询*/ -- union (select CityID,CityName from t_city where CityID between 6 and 10) union (select ProName,ProID from t_province where ProID between 6 and 10); --查询中国共有多少地级市 select count(*) from t_city; --查询河北省有多少地级市 select count(*) from t_city where ProID = ( select ProID from t_province where ProName = '河北省' ); --统计各省地级市的数量 select ProID,count(CityID) from t_city group by ProID; --求每个省份中最大的城市ID select ProID,max(CityID) from t_city group by ProID; --地级市最多的省份取前10名 select ProID,count(CityID) as cityCount from t_city group by ProID order by cityCount desc limit 10; --查询拥有区县最多的城市的前10名 select CityID,count(Id) as disCount from t_district group by CityID order by disCount desc limit 10; --查询拥有20个以上区县的城市 select CityID,count(Id) as disCount from t_district group by CityID having disCount > 19 order by disCount desc; --打出拥有20个以上区县的城市名字 -- 子查询实现 select * from t_city where CityID in ( select CityID from ( select CityID,count(Id) as disCount from t_district group by CityID having disCount > 19 order by disCount desc )fuck ); --打出拥有20个以上区县的城市名字 -- join --内连接:基于左右两表共有的ProID为样本进行查询 --左/右连接:基于【左右两表共有的ProID】+【左/右表独有的ProID】为样本进行查询 select td.CityID,tc.CityName,count(td.Id) discount from t_district td join t_city tc on td.CityID=tc.CityID group by CityID order by discount desc limit 20; /* insert into t_district(DisName,CityID) values ('上帝区',400), ('小鬼区',400), ('妖精区',400); insert into t_city(CityName,CityID) values ('你妹市',373); */ select tc.CityID,tc.CityName,count(td.Id) discount from t_district td left join t_city tc on td.CityID=tc.CityID group by td.CityID order by tc.CityID desc; --北京所有的区县 select * from t_district where CityID = ( select CityID from t_city where CityName = '北京市' ); select td.CityID,DisName,CityName from t_district td join t_city tc on td.CityID = tc.CityID where CityName = "北京市"; --北京有多少区县 /*select count(CityID) from t_district where CityID = ( select CityID from t_city where CityName = '北京市' );*/ select count(td.DisName) from t_district td join t_city tc on td.CityID = tc.CityID where tc.CityName = "北京市"; --找出地级市最多的省份 select tc.ProID,count(CityID) cc,ProName from t_city tc join t_province tp on tc.ProID = tp.ProID group by tc.ProID order by cc desc limit 1; --区县最多的城市是哪个省的什么市,查询结果包含省名、市名、区县数量; --现有最宜居城市排行榜如下:("宁波市","银川市","宜春市","宜昌市","咸阳市","芜湖市","泰州市","秦皇岛市","南通市","南京市","昆明市","桂林市","丹东市","大连市","长沙市","包头市","遂宁市","绵阳市","河州市") --求哪个省拥有最多的宜居城市,各有几个? -- 你家乡所在的省份拥有哪些宜居城市 select ProName,CityName from t_city tc join t_province tp on tc.ProID = tp.ProID where CityName in ("宁波市","银川市","哈尔滨市","宜春市","宜昌市","咸阳市","芜湖市","泰州市","绥芬河市","秦皇岛市","南通市","南京市","昆明市","桂林市","丹东市","大连市","长沙市","包头市","遂宁市","绵阳市","河州市") and ProName = '黑龙江省'; --查询哪个城市拥有最多的“旗”? select fuck.CityID,CityName,count(fuck.DisName) cfd from( select * from t_district where DisName like '%旗' )fuck join t_city on fuck.CityID = t_city.CityID group by fuck.CityID order by cfd desc limit 1; --查询省级行政区有哪几种? select distinct ProRemark from t_province; --查询全国有多少县级市? select count(*) from t_district where DisName like '%市'; -- 查询叫X县的地级市 select * from t_city where CityName like '%县'; --安徽的县级市数量 select ProName,CityName,DisName from (t_district td join t_city tc on td.CityID = tc.CityID join t_province tp on tc.ProID = tp.ProID) where tp.ProName = '安徽省' and DisName like '%市'; --哪个省的县级市最多? select tp.ProName,count(td.DisName) cd from t_province tp join t_city tc on tp.ProID = tc.ProID join t_district td on tc.CityID = td.CityID where td.DisName like '%市' group by tp.ProID order by cd desc; 欧阳桫老师博客:https://blog.csdn.net/u010986776 `` 欧阳桫老师博客:https://blog.csdn.net/u010986776 `` 欧阳桫老师博客:https://blog.csdn.net/u010986776 `` GO语言交流群:721929980`` GO语言交流群:721929980`` GO语言交流群:721929980````
114 次点击  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传