1. sql 实现按日期分组
需要用convert函数转换日期格式，并且需要用group
by来实现分组。
1、创建测试表及插入数据：
create table test
(tdate datetime,
sal int);
insert into test values ('2015-09-23 12:22:22',100);
insert into test values ('2015-09-23 01:54:34',456);
insert into test values ('2015-09-24 14:32:35',45);
insert into test values ('2015-09-24 22:23:43',67);
insert into test values ('2015-09-25 19:43:22',234);
insert into test values ('2015-09-25 08:14:12',67);
insert into test values ('2015-09-26 04:53:34',45);
insert into test values ('2015-09-26 09:46:54',78);2、执行sql语句：
select convert(varchar(10),tdate,120) tdate,sum(sal) sal from test group by convert(varchar(10),tdate,120);3、执行结果：
2. SQL里怎么把日期截取为月份
1、首先我们需要找到一个带日期字段的数据表。
3. SQL 拆分时间并分组
--假设你的投诉时间字段是date类型
--统计18年累计到当月的投诉量
select
投诉产品,
sum(case when to_char(投诉时间,'yyyymm')='201801' then 1 else 0 end) "一月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201802' then 1 else 0 end) "二月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201803' then 1 else 0 end) "三月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201804' then 1 else 0 end) "四月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201805' then 1 else 0 end) "五月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201806' then 1 else 0 end) "六月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201807' then 1 else 0 end) "七月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201808' then 1 else 0 end) "八月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201809' then 1 else 0 end) "九月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201810' then 1 else 0 end) "十月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201811' then 1 else 0 end) "十一月",
sum(case when to_char(投诉时间,'yyyymm')>='201801' and to_char(投诉时间,'yyyymm')<='201812' then 1 else 0 end) "十二月"
from 投诉表
group by 投诉产品;
--统计每月投诉量
select
投诉产品,
sum(case when to_char(投诉时间,'yyyymm')='201801' then 1 else 0 end) "一月",
sum(case when to_char(投诉时间,'yyyymm')='201802' then 1 else 0 end) "二月",
sum(case when to_char(投诉时间,'yyyymm')='201803' then 1 else 0 end) "三月",
sum(case when to_char(投诉时间,'yyyymm')='201804' then 1 else 0 end) "四月",
sum(case when to_char(投诉时间,'yyyymm')='201805' then 1 else 0 end) "五月",
sum(case when to_char(投诉时间,'yyyymm')='201806' then 1 else 0 end) "六月",
sum(case when to_char(投诉时间,'yyyymm')='201807' then 1 else 0 end) "七月",
sum(case when to_char(投诉时间,'yyyymm')='201808' then 1 else 0 end) "八月",
sum(case when to_char(投诉时间,'yyyymm')='201809' then 1 else 0 end) "九月",
sum(case when to_char(投诉时间,'yyyymm')='201810' then 1 else 0 end) "十月",
sum(case when to_char(投诉时间,'yyyymm')='201811' then 1 else 0 end) "十一月",
sum(case when to_char(投诉时间,'yyyymm')='201812' then 1 else 0 end) "十二月"
from 投诉表
group by 投诉产品;
4. sql 查询日期分段问题
这个我做过个统计模块，可以实现，贴其中1个时间段SQL整理函数你理解下，不理解可以hi我
function global_sql($type, $date, $from, $to, $vol){
if($type==0){
if ($date==0){
$sql = "DATE(".$vol.")=DATE(now())";
}else if ($date==1){
$sql = "DATE(".$vol.")=DATE_SUB(now(),Interval 1 DAY)";
}else if ($date==2){
$from = date("Y-m-",time()).$from;
$to = date("Y-m-",time()).$to;
$sql = "DATE(".$vol.")>='".$from."' and DATE(".$vol.")<='".$to."'";
}
}else if ($type==1){ #天
if ($date==0){
$sql = "DATE(".$vol.")=DATE(now())";
}else if ($date==1){
$sql = "DATE(".$vol.")=DATE(DATE_SUB(now(),Interval 1 DAY))";
}else if ($date==2){
$from = date("Y-m-",time()).$from;
$to = date("Y-m-",time()).$to;
$sql = "DATE(".$vol.")>='".$from."' and DATE(".$vol.")<='".$to."'";
}
}else if ($type==2){ #周
if ($date==0){
$sql = "DATE_FORMAT(".$vol.",'%Y%u')=DATE_FORMAT(now(),'%Y%u')";
}else if ($date==1){
if ( date("W",time())<=1 ){
$temp = (date("Y",time())-1).(52);
}else{
$temp = date("Y",time()).(date("W",time())-1);
}
$sql = "DATE_FORMAT(".$vol.",'%Y%u')='".$temp."'";
}else if ($date==2){
$from = date("Y",time()).$from;
$to = date("Y",time()).$to;
$sql = "DATE_FORMAT(".$vol.",'%Y%u')>='".$from."' and DATE_FORMAT(".$vol.",'%Y%u')<='".$to."'";
}
}else if ($type==3){ #月
if ($date==0){
$sql = "DATE_FORMAT(".$vol.",'%Y%c')=DATE_FORMAT(now(),'%Y%c')";
}else if ($date==1){
if ( date("n",time()) <= 1 ){
$temp = (date("Y",time())-1).(12);
}else{
$temp = date("Y",time()).(date("n",time())-1);
}
$sql = "DATE_FORMAT(".$vol.",'%Y%c')=".$temp;
}else if ($date==2){
$from = date("Y",time()).$from;
$to = date("Y",time()).$to;
$sql = "DATE_FORMAT(".$vol.",'%Y%c')>=".$from." and DATE_FORMAT(".$vol.",'%Y%c')<=".$to;
}
}else if ($type==4){ #季度
$year = date("Y",time());
if ($date==0){
$temp = re_quarter( quarter( date("n",time()) ) );
$sql = "DATE_FORMAT(".$vol.",'%Y%c') in (".$year.$temp[0].",".$year.$temp[1].",".$year.$temp[2].")";
}else if ($date==1){
if ( date("n",time()) <= 1 ){
$year -= 1;
$temp = re_quarter( quarter( 4 ) );
}else{
$temp = re_quarter( quarter( date("n",time())-1 ) );
}
$sql = "DATE_FORMAT(".$vol.",'%Y%c') in (".$year.$temp[0].",".$year.$temp[1].",".$year.$temp[2].")";
}else if ($date==2){
$year = date("Y",time());
#$sql = "DATE_FORMAT(".$vol.",'%Y%c')>=".$from." and DATE_FORMAT(".$vol.",'%Y%c')<=".$to;
$sql = "year(".$vol.")=".$year." and (quarter(".$vol.")>=".$from." and quarter(".$vol.")<=".$to.")";
}
}else if ($type==5){ #年
if ($date==0){
$sql = "YEAR(".$vol.")=YEAR(now())";
}else if ($date==1){
$sql = "YEAR(".$vol.")=YEAR(now())-1";
}else if ($date==2){
$sql = "YEAR(".$vol.")>=".$from." and YEAR(".$vol.")<=".$to;
}
}
return $sql;
}
5. 怎么将SQL Server时间与日期分开查询
sql server 中，
可以用cast()函数将日期时间转换为日期，
比如：cast('2014-01-22 13:22:35.000' as date) 的结果为2014-01-22
以下语句是查询2012年的数据，日期范围可以修改
select * 表名
where CAST(时间字段 as date) between '2012-01-01' and '2012-12-31'
如果要查全年数据，也可以这样，
select * 表名 where year(时间字段)=2012
另外，用convert()函数也可以将日期时间字段转换为日期字段来代替cast，具体用法一下
如果是oracle数据库请用to_date()代替cast将日期时间字段转换为日期来查询
6. SQL 拆分字段，并插入到原表中
怎么将查询结果插入会数据表1中啊？
---------
insert
T2(年,月,日)--指定对应的列
select
year(日期)
as
年,month(日期)
as
月,day(日期)
as
日
7. 怎么将SQL中的时间段，拆分成时间点
你参考一下
while datediff(d,@dt,@et)>=0
begin
print @dt
set @dt=convert(varchar(10),dateadd(d,1,@dt),120)
end