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