|
CREATE TEMPORARY TABLE
/*查询张三发布的所有出租房屋信息，并显示房屋分布的街道和区县
结果数据来源于出租房屋信息表、客户信息表、区县信息表、街道信息表
使用连接查询和子查询两种方式关联多表数据实现*/
SELECT`dName`,`sName`,h.*
FROM `hos_house` AS h
INNER JOIN `sys_user` AS u ON u.UID=h.UID
INNER JOIN `hos_street` AS s ON s.SID=h.SID
INNER JOIN `hos_district` AS d ON d.DID=s.sDID
WHERE u.`uName`='张三'
如图：
子查询
SELECT (SELECT`dName` FROM `hos_district` AS d WHERE d.`DID`=s.`sDID`),`sName`,h.*
FROM `hos_house` AS h
INNER JOIN `hos_street` AS s ON s.`SID`=h.`SID`
WHERE h.`UID`=(
SELECT `UID` FROM `sys_user` WHERE `uName`='张三'
)
如图：
/*根据户型和房屋所在区县和街道，为至少有两个街道有出租房屋的区县制作出租房屋清单
先使用HAVING子句筛选出街道数量大于1的区县*/
SELECT `hTName`,`uName`,`dName`,`sName`
FROM `hos_house`AS h
INNER JOIN `hos_type` AS t ON t.`hTID`= h.`hTID`
INNER JOIN `hos_street` AS s ON s.`SID`=h.`SID`
INNER JOIN `hos_district` AS d ON d.`DID`=s.`sDID`
INNER JOIN `sys_user` AS u ON u.`UID`=h.`UID`
WHERE d.`DID` IN (
SELECT `sDID` FROM`hos_street`
WHERE SID IN(
SELECT `SID`
FROM `hos_house`
GROUP BY `SID`
)
GROUP BY `sDID`
HAVING COUNT(*)>1
)
如图：
/*按季度统计出本年各区县各街道各种户型房屋出租数量
输出2016年从1月1日起至今的全部出租房屋数量、各区县出租房屋数量及各街道、户型出租房屋数量
*/
SELECT QUARTER(`hTime`) AS 季度,' 合计' AS 区县 ,'' AS 街道,''AS 户型,COUNT(*) AS 出租数量
FROM `hos_house`
GROUP BY 季度
UNION
SELECT QUARTER(`hTime`) AS 季度,dname AS 区县 ,' 小计' AS 街道,''AS 户型,COUNT(*) AS 出租数量
FROM `hos_house` AS h
INNER JOIN hos_street AS s ON s.sid=h.sid
INNER JOIN hos_district AS d ON d.did=s.sdid
GROUP BY 季度,d.did
UNION #连接
SELECT QUARTER(`hTime`) AS 季度,dname AS 区县 ,sname AS 街道,htname AS 户型,COUNT(*) AS 出租数量
FROM `hos_house` AS h
INNER JOIN hos_street AS s ON s.sid=h.sid
INNER JOIN hos_district AS d ON d.did=s.sdid
INNER JOIN hos_type AS t ON t.htid=h.htid
GROUP BY 季度,d.did,s.sid,t.htid
ORDER BY 1,2,3,4
如图：
注意：在"小计"和“合计”的前面一定要加‘空格’这样就会排在上面
|