按日期排列sql中的项

另一个测试。

ORDER BY 
  CASE WHEN movieDate >= DATEADD(hour, -24, GETDATE()) THEN movieDate END DESC,
  CHECKSUM(NEWID())

像这样:

ORDER BY
    CASE
        WHEN [movieDate] BETWEEN DATEADD(d, -1, GETDATE()) AND GETDATE() 
            THEN [movieDate]    
        ELSE [movieID]
    END DESC    

DECLARE @date AS datetime
SET @date = DateAdd(hh, -25, GetDate())

SELECT
  *
FROM (
  SELECT
    movieID,
    movieName,
    movieData,
    movieDate AS dateToOrder
  FROM
    movies
  WHERE
    movieDate >= DateAdd(h, -24, GetDate())
  UNION ALL
  SELECT
    movieID,
    movieName,
    movieData,
    @date AS dateToOrder
  FROM
    movies
  WHERE
    movieDate < DateAdd(hh, -24, GetDate()) -- older then 24 hours
 ) AS movies
 ORDER BY dateToOrder DESC, movieID

尝试类似(未测试)的操作:

SELECT
  *
FROM (
  SELECT -- start with the newest movies
    movieID,
    movieName,
    movieDate,
    1 AS rnk -- new will need it to sort them property
  FROM
    movies
  WHERE
    movieDate >= DATEADD(h, -24, GETDATE()) -- from last 24 hours
  UNION ALL -- there will be no duplicates (so, so)
  SELECT
    movieID,
    movieName,
    movieDate,
    2 AS rnk -- goes after the newest ones
  FROM
    movies
  WHERE
    movieDate < DATEADD(hh, -24, GETDATE()) -- older then 24 hours
 ) AS movies
 ORDER BY
   rnk, 
   CASE WHEN rnk = 1 THEN movieDate ELSE newid() END -- order by movieDate newest movies and by newid() older ones

是否确实要播放所有早于24小时的电影?

欺骗 在 movieID 会更快 然后使用 newid() 用于排序的函数。

所以 新ID()

DECLARE @d AS int
SET @d = DATEPART(ss, GETDATE()) + 3 -- to avoid division by 0

然后在 ORDER 从句代替 使用 movieID % @d .