我用的MS SQL2000,第一条语句是把搜索的结果放到临时表#temp保存,第二条语句是提取指定id范围的记录,我把两个语句分开写能在T-SQL查询分析器中顺利执行,但是一旦写成下面的子查询方式就报IDENTITY处的错,请说说合着写的方法(写成存储过程也可以),谢谢!1.分开写:(能执行)
SELECT IDENTITY(int,1,1) AS id,Bus_name INTO #temp FROM Chengdu WHERE Bus_stops LIKE '%何家桥%'
GO
SELECT * FROM #temp WHERE id BETWEEN 5 AND 8
GO2.合着写:(不能执行)
SELECT * FROM (SELECT IDENTITY(int,1,1) AS id,Bus_name INTO #temp FROM Chengdu WHERE Bus_stops LIKE '%何家桥%')
WHERE id BETWEEN 5 AND 8
GO
SELECT IDENTITY(int,1,1) AS id,Bus_name INTO #temp FROM Chengdu WHERE Bus_stops LIKE '%何家桥%'
GO
SELECT * FROM #temp WHERE id BETWEEN 5 AND 8
GO2.合着写:(不能执行)
SELECT * FROM (SELECT IDENTITY(int,1,1) AS id,Bus_name INTO #temp FROM Chengdu WHERE Bus_stops LIKE '%何家桥%')
WHERE id BETWEEN 5 AND 8
GO
WHERE id BETWEEN 5 AND 8
GO
这样就可以了
SELECT * FROM (SELECT IDENTITY(int,1,1) AS id,Bus_name INTO #temp FROM Chengdu WHERE Bus_stops LIKE '%何家桥%')
) a
WHERE a.id BETWEEN 5 AND 8
(
SELECT id = (select count(1) from Chengdu WHERE Bus_stops LIKE '%何家桥%' and Bus_name < t.Bus_name) + 1 , Bus_name FROM Chengdu t WHERE Bus_stops LIKE '%何家桥%'
) m
where id between 5 and 8
(
select Bus_stops ,px=count(*) from chengdu where Bus_stops LIKE '%何家桥%' and Bus_stops<A.Bus_stops
)B
where a.Bus_stops = B.Bus_stops
and b.px BETWEEN 5 AND 8
GO
SELECT * FROM #temp WHERE id BETWEEN 5 AND 8
GO等价于select * from
(
select *,num=(select count(1) from chengdu where a.bus_name=bus_name and id<=a.id) from chengdu a
) aa
where num BETWEEN 5 AND 8
SELECT * FROM (SELECT IDENTITY(int,1,1) AS id,Bus_name FROM Chengdu WHERE Bus_stops LIKE '%何家桥%')
WHERE id BETWEEN 5 AND 8
WHERE id BETWEEN 5 AND 8
WHERE id BETWEEN 5 AND 8 在子查询后面加个a