时间戳

首页 > 解决方案 > 将行号添加到存储过程

sql - 将行号添加到存储过程

问题描述

我正在尝试将行号添加到几年前由退休人员编写的存储过程中。这两个工会似乎是我遇到麻烦的地方。任何建议都会有所帮助，因为我在这里似乎处于死胡同。

CREATE procedure [dbo].[sp_GetReleases]
  @Area VARCHAR(10) = NULL
as

SET @Area = LOWER(@Area)

SELECT
       r.releaseid, r.release_id, r.releasedatetext, r.topic,
       r.releasedate as 'sortdate', '1' as 'sortcode', r.YouTubeID,
       rv.Videoflv, rv.Videomp4      
FROM release r, area a, ReleaseVideos rv
WHERE r.areaid = a.areaid
AND   r.releaseid  = rv.releaseid
AND a.area_id = @area
AND r.webdisplay = 1
AND GETDATE() >= availabledate

UNION

-- This is new 02-19-15
SELECT 
       r.releaseid, r.release_id, r.releasedatetext, r.topic,
       r.releasedate as 'sortdate', '1' as 'sortcode', r.YouTubeID,
       rv.Videoflv, rv.Videomp4      
FROM release r, area a, ReleaseVideos rv, ReleaseStates rs
WHERE   r.releaseid  = rv.releaseid
AND   a.area_id = @area

AND   a.areaid = rs.areaid
AND   r.releaseid = rs.releaseid

AND r.webdisplay = 1
AND GETDATE() >= availabledate

UNION

SELECT 
        r.releaseid, r.release_id, r.releasedatetext, r.topic, 
       (select r2.releasedate
        FROM release r2,  relatedrelease rr2
        WHERE r2.release_id = rr2.release_id) sortdate,
        '2' as 'sortcode',  r.YouTubeID,
        rv.Videoflv, rv.Videomp4
        
FROM release r, relatedrelease rr, ReleaseVideos rv
WHERE r.release_id = rr.related_id
AND   r.releaseid  = rv.releaseid
AND r.webdisplay = 1
AND GETDATE() >= r.availabledate
AND rr.release_id in 
    (SELECT release_id
     FROM release, area a
     WHERE a.area_id = @area
     AND release.areaid = a.areaid)
ORDER BY sortdate DESC, sortcode 
GO

标签： sqltsql

解决方案

  1. 将整个查询按原样包装，不包括 ORDER BY 子句，在CTE（命名a）中
  2. 编写一个a查询，根据a.

大纲：

WITH a AS (
    -- the original query, minus the order by clause
)
SELECT
    *,
    rn=ROW_NUMBER() OVER (/*your number partitioning/ordering here*/)
FROM
    a
-- original order by clause here

推荐阅读