一些mysql的sql语句

行转列

SELECT NAME,
    MAX(CASE course WHEN '数学' THEN score ELSE 0 END) 数学,
    MAX(CASE course WHEN '英语' THEN score ELSE 0 END) 英语,
    MAX(CASE course WHEN '语文' THEN score ELSE 0 END) 语文
FROM student GROUP BY NAME

 

SELECT hou.*,uname FROM hos_house AS hou
 INNER JOIN sys_user AS usr
 INNER JOIN hos_type AS hty
 ON hou.uid=usr.uid
 AND hty.htname='三室一厅'
 AND hou.hTid=hty.hTid
 AND hou.sid IN
 (SELECT sid FROM hos_street AS street INNER JOIN hos_district AS district  WHERE street.`sDid`=district.`did` AND district.`dname`='西湖区')
 ORDER BY price LIMIT 0,5



SELECT * FROM hos_house;                      //查所有房屋信息
SELECT * FROM hos_house WHERE price>500;         //租金大于500

SELECT COUNT(*) FROM (
  SELECT *,COUNT(*)AS COUNT FROM sys_user GROUP BY uname ORDER BY COUNT DESC
)AS a WHERE a.count>1;  //重名的人数

SELECT * FROM sys_user;                       //查所有用户信息
        
SELECT * FROM hos_house AS a INNER JOIN hos_type AS b WHERE a.htid=b.htid AND b.htname = '三室一厅';
//查所有为三室一厅的房屋

SELECT * FROM (SELECT * FROM hos_house WHERE htid=1)AS a,hos_street AS b WHERE a.sid=b.sid AND b.sDid=1;


SELECT c.hmid FROM (SELECT a.hmid,a.price,a.sid FROM hos_house AS a,hos_type AS b WHERE a.htid=b.htid AND b.htname='三室一厅')
AS c INNER JOIN hos_street AS d INNER JOIN hos_district AS e WHERE c.sid=d.sid AND d.sDid=e.did AND e.dname='西湖区'
//查询三室一厅在西湖区的房屋 6


SELECT COUNT(DISTINCT manager)AS COUNT FROM sys_user;    //查所有管理员的数量

SELECT n.uid,n.uname,MAX(n.house) AS house FROM(
     SELECT s.uname,s.uid,COUNT(*)AS house FROM (
    SELECT s.uname,h.uid FROM hos_house AS h,sys_user AS s WHERE s.uid=h.uid
     )AS s GROUP BY s.uid ORDER BY house DESC
)AS n  //查询房屋数量最多的人

SELECT * FROM (
    SELECT a.hMid,a.price,a.htid,b.sDid,a.uid,c.dname FROM (
        SELECT * FROM hos_house WHERE htid=1
    )AS a,hos_street AS b,hos_district AS c WHERE a. sid=b.sid AND sDid=1 AND c.did=b.sDid ORDER BY price ASC LIMIT 0,5
) AS c,sys_user AS d WHERE c.uid=d.uid        //查询西湖区最便宜的5个三居房屋信息和发布人名字


SELECT * FROM (
        SELECT * FROM hos_house WHERE price BETWEEN 500 AND 1000    
        )AS a,hos_street AS b WHERE a.sid=b.sid AND b.sDid=1 ORDER BY price ASC LIMIT 0,5 //以5条记录为一页查询西湖区价格500-1000的房屋