SQL 按分钟合并\统计数据

select min(id) id, min(createTime),sum(价格) 价格总计 from ( select * ,Convert(varchar(13),createTime,121)+ case 
 when  datepart(mi,createTime)<=15 then 'aa'
 when datepart(mi,createTime) between 16 and 30 then 'bb' 
 when datepart(mi,createTime) between 31 and 45 then 'cc' 
when datepart(mi,createTime) between 46 and 59 then 'dd' end as date  from table) a group by date

http://bbs.csdn.net/topics/390774960

月排班

--当月排班表

;with ct as
(
select Convert(varchar,dateadd(dd,number,Convert(varchar,Convert(varchar(7),getdate(),23))+'-01'),23)'日期'
 from master.dbo.spt_values where type='p' and 
number < datediff(dd,Convert(varchar,Convert(varchar(7),getdate(),23))+'-01',Convert(varchar,Convert(varchar(7),dateadd(m,1,getdate()),23))+'-01')
)
select * ,
case datepart(dw,日期) when 1 then '星期日'
                       when 2 then '星期一'
                        when 3 then '星期二'
                         when 4 then '星期三'
                           when 5 then '星期四'
                             when 6 then '星期五'
                             else  '星期六' end '星期',
case  when datepart(dw,日期)<7 and
datepart(dw,日期)>1 then '工作' else '放假' end '安排'
 from ct

排序

select a.place,a.id from
(
select ROW_NUMBER() over (partition by place order by id) as rn, t.* from #a t
) a 
left join 
(
    select MIN(sn) as sn,place from
    (
    select ROW_NUMBER() over(order by getdate()) as sn, place from #a
    ) b group by place
) c on a.place = c.place
order by sn,id 

or 

--drop table aa
create table aa ( place varchar(50),id int)

insert into aa
select  'F  ',    2 union all
select    'F',      3 union all
select    'F',      1 union all
select    'A',      2 union all
select    'A',      1 union all
select    'A',      4 union all
select    'Z',      3 union all
select    'Z',      1 union all
select    'N',      2 union all
select    'M',      2 
go


if OBJECT_ID('tempdb..#temp') is not null
   drop table #temp

select *,
       ROW_NUMBER() over( order by getdate()) rownum 
       into #temp
From aa


select place,id
from #temp
order by MIN(rownum) over(partition by place),id
/*
place	id
F	1
F  	2
F	3
A	1
A	2
A	4
Z	1
Z	3
N	2
M	2
*/

http://bbs.csdn.net/topics/390657977?page=1