--按每半个小时查询数据
select HourPart,data from 
(select
    CONVERT(varchar(100), [TM], 23) + ' ' +
	case when datepart(mi,[TM])<30 then 
		right('00'+datename(hh,[TM]),2)+':00' else 
		right('00'+datename(hh,[TM]),2)+':30' 
	end as HourPart, 
	([Z01]) as data,
	row_number() over (partition by CONVERT(datetime,case when datepart(mi,[TM])<30 then 
	CONVERT(varchar(100), [TM], 23)+ ' ' +datename(hh,[TM])+':00:00' else 
	CONVERT(varchar(100), [TM], 23)+ ' ' +datename(hh,[TM])+':30:00' end) order by [TM] asc) as rn
	from [SS_WORKDT_R]
	where STCD=@stcd and TM > @startdate and TM < @enddate
) rq where rn=1

--按半个小时统计
select T.groupid,count(T.orderid) as number from
(
select o.orderid,o.ordertime,((datename(hh,o.ordertime)*60+datename(mi,o.ordertime))-基值)/30 as groupid from orders as o 
where o.ordertime<'2013-10-20 22:00' and o.ordertime>'2013-10-20 9:00'
) as T
group by T.groupid
order by groupid


--分页查询
select * from(select row_number() over(order by model.OrderStr) Id,* from TempA) as TempInfo where Id<= @PageIndex * @PageSize and Id>(@PageIndex-1)*@PageSize


--SQL计算两个字段或者三个字段的最大值
--SQL中的MAX是不能直接使用的，但利用以下公式可以达到相应的目的， 
max(x,y)=(x+y+ABS(x-y))/2 
--ABS(x-y)是拿到x-y的差的绝对值，同样也可以得到如下公式： 
min(x,y)=(x+y-ABS(x-y))/2 

--考虑使用导出表，将三列数据合并到一列中来，然后再在外层Select中查出最大值，如以下脚本： 
select id,MAX(m) from (  
    select id,`x` as m from xyz   
        union all   
    select id,`y` as m from xyz   
        union all   
    select id,`z` as m from xyz  
) u group by id;