一些SQL语句示例

2018-06-24

SQL

如:
表:consume_record
字段:consume (money类型)
date (datetime类型)

请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月 1200元
2月 3400元
3月 2800元

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
--按日
select sum(consume),day([date])
 from consume_record where year([date]) = '2006'
group by day([date])
 
--按周quarter
select sum(consume),datename(week,[date])
from consume_record where year([date]) = '2006'
group by datename(week,[date])
 
--按月
select sum(consume),month([date])
from consume_record where year([date]) = '2006'
group by month([date])
 
--按季
select sum(consume),datename(quarter,[date])
from consume_record where year([date]) = '2006'
group by datename(quarter,[date])
 
  
 
--指定日期你就看上面的例子变通下呀，无非就是一个聚合函数和Group by
 
select [date],sum(consume) from consume_record
where [date] between '2006-06-01' and '2006-07-10'
group by [date]
1
2
3
4
5
6
7
8
9
10
11
--2011年每月注册的人数
SELECT CONVERT(CHAR(7), joindate,120), COUNT(*)
FROM member
where year(joindate)=2011
GROUP BY CONVERT(CHAR(7), joindate,120)
 
--2011年3月每天注册的人数
SELECT CONVERT(CHAR(10), joindate,120), COUNT(*)
FROM member
where year(joindate)=2011 and month(joindate)=3
GROUP BY CONVERT(CHAR(10), joindate,120)
1
2
3
4
5
6
--获取每家公司前一百条新闻的ID
select a.WebNewsID from WebNews as a
where a.WebNewsID in (select top 100 WebNewsID
from WebNews where Code=a.Code
order by PageGetTime desc)
group by a.WebNewsID,a.Code  order by a.Code

Related Posts