SQL server查询笔记
select datepart(year,createtime) as [year],count(o.Teacherid) as teachernum from rrs_client c left join rrs_order__ o on c.clientid = o.clientid where between YEAR(createtime)='2017' and YEAR(createtime)='2013' and c.deleted=0 and o.deleted=0 and o.clientid ='24965' group by datepart(year,createtime)
//根据年份分组 查询
datepart(year,createtime)
group by datepart(year,createtime)
//year 年 week周 month 月 quarter 季度
ORDER BY CONVERT(INT,DATENAME(week,AddDate));
CONVERT() 函数是把日期转换为新数据类型的通用函数。
CONVERT() 函数可以用不同的格式显示日期/时间数据。
GETDATE() 函数来获得当前的日期/时间：
//求分组后数量的最大值
sum(sum) as [aa]
order by aa desc
//求求和数量的指定范围
sum(money)
having sum(money)>0
//就算两个日期的查（天，小时，，分 秒）
datediff(DAY,'','') as diff
第一个时间跟第二个时间相差多少
//month 月 day 天 hour 小时 minute 分钟 second 秒
isnull(o.endtime, c.createddate)
判断前一个值是不是为空 是的话就用后面一个值填上空缺
GETDATE()当前时间
CONVERT(VARCHAR(20),DATEADD(SECOND,deleted,'1970-01-01 00:00:00'),120)
时间戳转日期
//常用连表统计
select 表一.部门名称 as 部门,count(表二.id)as 人数
from 表一
left join 表二 on 表一id=表二.部门id
group by 表一.部门名称
--一天内：
select * from tbl where convert(varchar(10),posttime,120) = '2010-04-03'
--一周内：
select * from tbl where datedepart(dw,posttime) = datedepart(dw,'2010-04-03')
--一月内
select * from tbl where convert(varchar(7),posttime,120) = '2010-04'
根据 金额范围分组
SELECT
o.clientid,
o.Inchargeuserid AS userid,
SUM (totalmoney) AS money,
o.totalday,
o.createtime,
datepart(YEAR, o.createtime) AS [year],
datepart(MONTH, o.createtime) AS [month],
datepart(DAY, o.createtime) AS [day],
CASE
WHEN SUM (totalmoney) >= 500000 THEN
'50万以上'
WHEN SUM (totalmoney) >= 200000
AND SUM (totalmoney) < 500000 THEN
'20-50万'
WHEN SUM (totalmoney) >= 100000
AND SUM (totalmoney) < 200000 THEN
'10-20万'
WHEN SUM (totalmoney) >= 50000
AND SUM (totalmoney) < 100000 THEN
'5-10万'
WHEN SUM (totalmoney) >= 20000
AND SUM (totalmoney) < 50000 THEN
'2-5万'
ELSE
'2万以下'
END AS leval
FROM
rrs_order o
WHERE
o.otype = 'D'
AND o.deleted = 0
AND o.status = 1
GROUP BY
o.itemname,
o.clientid,
o.Inchargeuserid,
o.totalday,
o.createtime
结果：
