日期:2014-05-18  浏览次数:20529 次

从表里根据时间随机抽取一个字段生成试图
表结构:
每六秒一个值 数据量太大 要做一个折线图 想每小时抽取一个值 生成视图 


------解决方案--------------------
SQL code
select
  distinct b.*
from
  tb a
cross apply
  (select top 1 * from tb where convert(varchar(13),riqi,120)=convert(varchar(13),t.riqi,120) order by riqi)b

------解决方案--------------------
SQL code
SELECT b.*
FROM 
(SELECT DISTINCT CAST(CONVERT(VARCHAR(14),rigi,121)+'00' AS DATETIME) AS rigi FROM TABLE1 )AS a
CROSS APPLY
(SELECT TOP 1 * FROM table1 WHERE DATEDIFF(h,a.rigi,rigi)=0 ORDER BY NEWID()) AS b