13 查询数据

-查询QQ号码为88662753的用户的所有好友信息
SELECT R.RelationQQID AS 好友QQ号,B.NickName AS 昵称,B.Age AS 年龄
FROM Relation AS R,BaseInfo AS B
WHERE R.QQID=88662753 AND R.RelationStatus=0 AND R.RelationQQID=B.QQID

--查询当前在线用户的信息
SELECT Q.QQID AS QQ号,NickName  AS 昵称,Sex AS 性别,Age AS 年龄,Province AS 省份,City AS 城市
FROM QQUser AS Q,BaseInfo AS B
WHERE Q.QQID=B.QQID AND Q.Online=0

--查询北京的、年龄在18至45岁之间的在线用户的信息。
SELECT Q.QQID AS QQ号,NickName  AS 昵称,Sex AS 性别,Age AS 年龄,Province AS 省份,City AS 城市
FROM QQUser AS Q,BaseInfo AS B
WHERE Q.QQID=B.QQID AND Q.Online=0 AND (B.Age BETWEEN 18 AND 45) AND (B.Province LIKE '北京%' OR B.City LIKE '北京%')

--查询昵称为“小笨猪”的用户信息。
SELECT * FROM BaseInfo WHERE NickName='小笨猪'

--查询QQ号码为54789625的用户的好友中每个省份的总人数，并且总人数按由大到小排序。
SELECT B.Province AS 省份,COUNT(*) AS 好友总人数
FROM Relation AS R,BaseInfo AS B
WHERE( R.QQID=54789625 AND R.RelationStatus=0 AND R.RelationQQID=B.QQID)
GROUP BY B.Province ORDER BY COUNT(*) DESC

--查询至少有150天未登录QQ帐号的用户信息。
SELECT QQUser.QQID,LastLogTime,Level,NickName,Age FROM QQUser,BaseInfo 
WHERE QQUser.QQID=BaseInfo.QQID AND DATEDIFF(DAY,LastLogTime,GETDATE())>150 
ORDER BY LastLogTime DESC

--查询QQ号码为54789625的好友中等级为10级以上的“月亮”级用户信息。
SELECT R.RelationQQID AS 好友QQ号,B.NickName AS 昵称,B.Age AS 年龄,Q.Level AS 等级
FROM Relation AS R 
INNER JOIN BaseInfo AS B
ON R.QQID=54789625 AND R.RelationStatus=0 AND R.RelationQQID=B.QQID 
INNER JOIN QQUser AS Q
ON R.RelationQQID=Q.QQID AND Q.Level>=10 

--查询QQ号码为54789625的好友中隐身的用户信息。
SELECT R.RelationQQID AS 好友QQ号,B.NickName AS 昵称,B.Age AS 年龄
FROM Relation AS R ```
INNER JOIN BaseInfo AS B
ON R.QQID=54789625 AND R.RelationStatus=0 AND R.RelationQQID=B.QQID 
INNER JOIN QQUser AS Q
ON R.RelationQQID=Q.QQID AND Q.OnLine=2

--查询好友超过20个的用户信息。
SELECT QQID AS QQ号,COUNT(*) AS 好友总数  FROM Relation 
WHERE RelationStatus=0 
GROUP BY QQID 
HAVING COUNT(*)>=20

--为了查看信誉度，管理员需要查询被当做黑名单人物排名前20的最低信誉度用户。
SELECT TOP 20 (RelationQQID )AS QQ号,COUNT(*) AS 被当做黑名单人物总数  FROM Relation 
WHERE RelationStatus=1 
GROUP BY RelationQQID 
ORDER BY COUNT(*) DESC