变通解决SQL视图中排序异常问题

----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date : 2010-06-04 08:21:56

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

-- Blog : http://blog.csdn.net/htl258

-- Subject: 变通解决SQL视图中排序异常问题

----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

DROP TABLE [tb]

GO

CREATE TABLE [tb] ([a] [int],[b] [nvarchar](10),[c] [nvarchar](10))

INSERT INTO [tb]

SELECT '3','c','测' UNION ALL

SELECT '1','a','试' UNION ALL

SELECT '5','e','视' UNION ALL

SELECT '6','f','图' UNION ALL

SELECT '2','d','排' UNION ALL

SELECT '4','b','序'

--SELECT * FROM [tb]

-->SQL查询如下:

--1.正常的顺序：

IF OBJECT_ID('vw_TestOrder') >0

DROP VIEW vw_TestOrder

GO

CREATE VIEW vw_TestOrder

AS

SELECT * FROM tb

GO

SELECT * FROM vw_TestOrder

/*

a b c

----------- ---------- ----------

3 c 测

1 a 试

5 e 视

6 f 图

2 d 排

4 b 序

(6 行受影响)

*/

--2.加上TOP 100 PERCENT后的排序

IF OBJECT_ID('vw_TestOrder') >0

DROP VIEW vw_TestOrder

GO

CREATE VIEW vw_TestOrder

AS

SELECT TOP 100 PERCENT *

FROM tb

GO

SELECT * FROM vw_TestOrder

/*

a b c

----------- ---------- ----------

3 c 测

1 a 试

5 e 视

6 f 图

2 d 排

4 b 序

(6 行受影响)

*/

--3.变通处理后的排序

IF OBJECT_ID('vw_TestOrder') >0

DROP VIEW vw_TestOrder

GO

CREATE VIEW vw_TestOrder

AS

WITH t AS

(

SELECT * FROM tb

)

SELECT TOP (SELECT COUNT(1) FROM t)

*

FROM t

ORDER BY a

GO

SELECT * FROM vw_TestOrder

/*

a b c

----------- ---------- ----------

1 a 试

2 d 排

3 c 测

4 b 序

5 e 视

6 f 图

(6 行受影响)

*/

--下面是为一个需求贴而写，链接：http://topic.csdn.net/u/20100604/06/77111376-863a-4c03-8844-69f1e9bb8ed0.html?62587

----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date : 2010-06-04 08:16:38

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

-- Blog : http://blog.csdn.net/htl258

-- Subject: 变通解决SQL视图中排序异常问题

----------------------------------------------------------------------------------

--> 生成测试数据表: [barea]

IF OBJECT_ID('[barea]') IS NOT NULL

DROP TABLE barea

GO

CREATE TABLE barea

(

area_id INT PRIMARY KEY,--地区编号

--layer_code VARCHAR(10) NOT NULL,--分层码

area_name NVARCHAR(10) not null,--地区名称

--full_name NVARCHAR(50) NOT NULL,--全名称

parent_id INT DEFAULT(0),--上级地区编号

order_id INT DEFAULT(0)--排序序号

)

INSERT INTO BAREA VALUES(10,'河南省',0,1)

INSERT INTO BAREA VALUES(1001,'郑州市',10,1)

INSERT INTO BAREA VALUES(1002,'南阳市',10,2)

INSERT INTO BAREA VALUES(100101,'安县',1001,3)

INSERT INTO BAREA VALUES(100201,'红县',1002,4)

INSERT INTO BAREA VALUES(20,'海南省',0,0)

INSERT INTO BAREA VALUES(2001,'海口市',20,1)

GO

-->SQL查询如下

IF OBJECT_ID('v_BOM') IS NOT NULL

DROP VIEW v_BOM

GO

CREATE VIEW v_BOM

AS

WITH t AS

(

SELECT *, full_name = CAST(area_name AS VARCHAR), lvl = 1, CAST(

RIGHT(

100+ROW_NUMBER()OVER(PARTITION BY parent_id ORDER BY area_id), 2

) AS VARCHAR

) layer_code, px = CAST(ROW_NUMBER()OVER(ORDER BY order_id) AS VARBINARY)

FROM barea

WHERE parent_id = 0

UNION ALL

SELECT a.*, CAST(full_name+a.area_name AS VARCHAR), lvl+1, CAST(

layer_code+RIGHT(

100+ROW_NUMBER()OVER(PARTITION BY a.parent_id ORDER BY a.area_id), 2

) AS VARCHAR

), px = CAST(

px+CAST(

ROW_NUMBER()OVER(PARTITION BY a.parent_id ORDER BY a.order_id) AS VARBINARY

) AS VARBINARY

)

FROM barea a, t b

WHERE a.parent_id = b.area_id

)

SELECT TOP (SELECT COUNT(1) FROM t) --主要是这一句

area_id, layer_code, area_name, full_name, parent_id, order_id

FROM t

ORDER BY px

GO

SELECT * FROM v_bom

/*

area_id layer_code area_name full_name parent_id order_id

----------- ------------------------------ ---------- ------------------------------ ----------- -----------

20 02 海南省 海南省 0 0

2001 0201 海口市 海南省海口市 20 1

10 01 河南省 河南省 0 1

1001 0101 郑州市 河南省郑州市 10 1

100101 010101 安县 河南省郑州市安县 1001 3