SQL必知必会-练习代码

SELECT TOP 10 *
FROM lianxi.dbo.zldf


SELECT 用户id+'-'+convert(nvarchar,手机号) AS 用户id与手机号
FROM lianxi.dbo.zldf


SELECT 用户id,手机号
FROM lianxi.dbo.zldf
WHERE len(用户id)=12 AND 手机号 is not null

SELECT TOP 100 *
FROM lianxi.dbo.zldf
WHERE DATEPART(d,发送消息时间)=15
ORDER BY 发送消息时间 DESC



SELECT MAX(经纪人回复数) AS 经纪人回复数最大值,MIN(经纪人回复数) AS 经纪人回复数最小值
FROM lianxi.dbo.zldf
WHERE 经纪人回复数>0


SELECT AVG(经纪人回复数) AS 经纪人回复数平均值
FROM lianxi.dbo.zldf


SELECT COUNT(*) AS 总行数,COUNT(手机号) AS 非空手机号行数,COUNT(*)-COUNT(手机号) AS 空手机号行数
FROM lianxi.dbo.zldf



SELECT 分类,AVG(经纪人回复数) AS 经纪人回复数平均值
FROM lianxi.dbo.zldf
GROUP BY 分类
ORDER BY 经纪人回复数平均值



SELECT TOP 100 *
FROM lianxi.dbo.zldf



SELECT COUNT(DISTINCT 序号) AS 去重直聊通数
FROM lianxi.dbo.zldf



SELECT riqi,COUNT(DISTINCT 序号) AS 去重直聊通数
FROM lianxi.dbo.zldf
GROUP BY riqi


SELECT COUNT(DISTINCT 序号) AS 去重直聊通数
FROM lianxi.dbo.zldf

--查询回复数为55与58的用户id
SElECT DISTINCT 用户id,经纪人回复数
FROM lianxi.dbo.zldf
WHERE 经纪人回复数 in (55,58)



--子查询
SElECT DISTINCT 用户id,StaffNo,经纪人回复数
FROM lianxi.dbo.zldf
WHERE 用户id in (SELECT 用户id
FROM lianxi.dbo.zldf
WHERE StaffNo like 'aa76%')