日期:2014-05-19  浏览次数:20502 次

排序问题
查询代码如下:(每张表都有十万以上数据,页面显示是通过WEB方式)

select   *     INTO   #T   from  
(SELECT   dbo.T1.*,   dbo.T2.Vname,
                    (SELECT   COUNT(*)   AS   eNum
                  FROM   dbo.T3
                  WHERE   T3.Vname   =   Vname)   AS   eNum
FROM   dbo.T1   INNER   JOIN
            dbo.T2   ON   dbo.T1.Vid=   dbo.T2.Vid)   AS   T

select   IDENTITY(int,   1,1)   AS   ID,*     INTO   #B   from   (select   *   FROM   #T   order   by   eNum)   as   T

能否不写入#B实现产生ID列并已是递增序号?可建查询。
这样通过查询分析器执行一次就好二十多秒!
受不了。。。。如果不产生#T基表在页面上查询分页也受不了。

------解决方案--------------------
select id=identity(int,1,1),* INTO #T from
(SELECT dbo.T1.*, dbo.T2.Vname,
(SELECT COUNT(*) AS eNum
FROM dbo.T3
WHERE T3.Vname = Vname) AS eNum
FROM dbo.T1 INNER JOIN
dbo.T2 ON dbo.T1.Vid= dbo.T2.Vid) AS T order by eNum