sql server 按日期時間分組查詢，sql server 按日期，時間，年，月，日，時，分，秒分組查詢

 sql server 按日期時間年，月，日，時分組查詢

select datepart(YEAR,CreateDateTime) yyyy,datepart(month,CreateDateTime) mm,datepart(day,CreateDateTime) dd ,datepart(hour,CreateDateTime) hh
from Symptom
GROUP BY datepart(YEAR,CreateDateTime),datepart(month,CreateDateTime),datepart(day,CreateDateTime),datepart(hour,CreateDateTime)      

 sql server 按日期時間年，月，日，時分組查詢，并統計數量

select datepart(YEAR,CreateDateTime) yyyy,datepart(month,CreateDateTime) mm,datepart(day,CreateDateTime) dd ,datepart(hour,CreateDateTime) hh,COUNT(SymptomId) count
from Symptom
GROUP BY datepart(YEAR,CreateDateTime),datepart(month,CreateDateTime),datepart(day,CreateDateTime),datepart(hour,CreateDateTime)      

SqlServer日期時間函數，最全

​​SQL Server 查詢基礎及分組（每三個小時一組）​​

原文位址​

一、連結查詢

　　内連接配接(inner join)。左連接配接(left join)，以左邊為基準,全部查詢右邊沒有比對的值顯示為空。右連結(right join)，和左連接配接相反,以右邊為基準左邊表的字段為空。全連結(full outer join)左右全部連接配接沒有對應值得字段為空。交叉連結(coress join)笛卡爾積

-- inner join 顯示
select * from Users 
inner join Wallets on Users.ID = Wallets.User_ID 
where  Money > 30000
-- inner join 隐式
select * from Wallets,Users 
where Wallets.User_ID = Users.ID and Money > 10000
-- left join
select * from Users 
left join Wallets  on Users.ID = Wallets.User_ID
-- right join
select * from Wallets 
right join Users on Users.ID = Wallets.User_ID 
-- full outer join
select * from Users
full outer join Wallets on Users.ID = Wallets.User_ID 
where User_ID is null or Users.ID is null
-- cross join
select * from Users 
cross join Wallets where Users.ID = 3      

二、from子查詢(嵌套查詢)

--查詢錢包裡金額大于30000
select * from Users where exists (select User_ID from Wallets where Money > 30000 and User_ID = Users.ID)
select * from Users where ID in (select User_ID from Wallets where Money > 30000)      

三、分組

1. 根據日期分組

　　根據convert 的第一個參數，設定結果的長度達到更具年月分組或年月日分組等

　　2017-09 長度 7 varchar(7)

　　2017-09-01 長度10 varchar(10)

-- 使用convert() 函數方式
select convert(varchar(7),CreatTime,120)日期,COUNT(*) 次數,sum(Money)總數 from Orders
group by convert(varchar(7),CreatTime,120)
-- 先查出對應的年月日等時間，再根據查詢出的字段分組
select YEAR(CreatTime) 年,month(CreatTime) 月,COUNT(*) 次數,sum(Money)總數 from Orders
group by month(CreatTime),YEAR(CreatTime)
order by 年,月 asc      

2. 根據劃分時間段進行分組(每三個小時一組)

　　dataname:傳回代表指定日期的指定日期部分的字元串。一個參數代表傳回的格式，第二個參數為具體值dw:星期幾，qq, q:季度，yy, yyyy:年，mm,m:月，dd、d:日，hh:小時，mi, n:分鐘 ss、s:秒，ms:毫秒

select t.number 記錄數,
CONVERT(varchar(2),t.timeGroup * 3)+':00~'+ CONVERT(varchar(2),t.timeGroup * 3 + 2)+':59' 時間段
from 
(select datename(hh,CreateTime) /3 timeGroup,COUNT(1) number from ClassOptions 
group by datename(hh,CreateTime) / 3) t      

​​SQL Server中時間按天分組​​

原文位址​

例1：

select Min(VisitTime),count(*) as Num from online group by datediff(day,VisitTime,getdate())      

例2：

select count(*),Min(PayTime) from LimitBidBills where PayTime>'2020-07-25' and Status='已确認'
group by datediff(day,PayTime,getdate())
order by Min(PayTime) asc      

例3：

select count(*),Min(PayTime) from LimitBidBills join LimitBidMail on LimitBidBills.InfoID = LimitBidMail.InfoID where PayTime>'2020-06-20' and   PayTime<'2020-07-5' and Status='已确認'
group by datediff(day,PayTime,getdate())
order by Min(PayTime) asc      

​​sqlserver中如何實作時間按月,日,小時分組查詢​​

原文位址​

--按照月份統計
select count(id) cnt,datepart(mm,time) [Month]
from [table]
where [time] between '2007/09/08 09:10:43' and '2007/10/09 04:32:37'
group by datepart(mm,time)


--按照日統計
select count(id) cnt,datepart(dd,time) [Day]
from [table]
where time between '2007/09/08 09:10:43' and '2007/10/09 04:32:37'
group by datepart(dd,time)


--按照小時統計
select count(id) cnt,datepart(hh,time) [Hour]
from [table]
where [time] between '2007/09/08 09:10:43' and '2007/10/09 04:32:37'

group by datepart(hh,time)      

1、每年

select year(ordertime) AS '年',

sum(Total) '銷售合計'

from order_list

group by year(ordertime)

2、每月

select year(ordertime) '年',

month(ordertime) '月',

sum(Total) '銷售合計'

from order_list

group by year(ordertime),

month(ordertime)

3、每日

select year(ordertime) '年',

month(ordertime) '月',

day(ordertime) '日',

sum(Total) '銷售合計'

from order_list

group by year(ordertime),

month(ordertime),

day(ordertime)

另外每日也可以這樣:

select convert(char(8),ordertime,112) dt,

sum(Total) '銷售合計'

from order_list

group by convert(char(8),ordertime,112)

另外，每月(年、日)的記錄條數

select year(ordertime) '年',

month(ordertime) '月',

count(*) '銷售記錄'

from order_list

group by year(ordertime),

month(ordertime)