继续刚才的问题,关于按合计结果排序
CREATE PROC mm_Down_CD
@BeginDate smalldatetime,
@EndDate smalldatetime
AS
SET NOCOUNT ON
SELECT 序号=IDENTITY(int, 1, 1),
A.NAME AS 专辑名称,
ISNULL(B.DownALL,0) AS 专辑下载,
ISNULL(B.DownONE,0) AS 单曲下载
INTO #tempCD
FROM MM_CD A LEFT OUTER JOIN
(
SELECT CDID,SUM(DownAll) AS DownALL,SUM(DownOne) AS DownONE
FROM MM_CD_Down
WHERE (DownDate > = @BeginDate) AND (DownDate <= @EndDate)
GROUP BY CDID
)B ON A.ID = B.CDID
ORDER BY DownALL Desc
SELECT *,下载合计=专辑下载*10+单曲下载
FROM #tempCD
GO
得到结果如下:
序号 专辑名称 专辑下载 单曲下载 下载合计
----- --------- -------- -------- -------
1 AAA 11 34 144
3 CCC 3 53 86
2 BBB 6 12 72
如何把查询结果按 下载合计 排倒序 并保持 序号按顺序呢
也就是说要在写临时表之前就按下载合计排序
序号 专辑名称 专辑下载 单曲下载 下载合计
----- --------- -------- -------- -------
1 AAA 11 34 144
2 CCC 3 53 86
3 BBB 6 12 72
[解决办法]
CREATE PROC mm_Down_CD
@BeginDate smalldatetime,
@EndDate smalldatetime
AS
SET NOCOUNT ON
SELECT 序号=IDENTITY(int, 1, 1),
A.NAME AS 专辑名称,
ISNULL(B.DownALL,0) AS 专辑下载,
ISNULL(B.DownONE,0) AS 单曲下载,
B.下载合计
INTO #tempCD
FROM MM_CD A LEFT OUTER JOIN
(
SELECT TOP 100 PERCENT CDID,SUM(DownAll) AS DownALL,SUM(DownOne) AS DownONE,
下载合计=ISNULL(SUM(DownAll), 0)*10+ISNULL(SUM(DownOne), 0)
FROM MM_CD_Down
WHERE (DownDate > = @BeginDate) AND (DownDate <= @EndDate)
GROUP BY CDID
ORDER BY 4 DESC
)B ON A.ID = B.CDID
SELECT * FROM #tempCD
GO
