这两个语句能合并成一个字查询语句吗？

[sqq4290]

我用的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
GO

2.合着写：（不能执行）

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

[utpcb]

SELECT * FROM (SELECT IDENTITY(int,1,1) AS id,Bus_name FROM Chengdu WHERE Bus_stops LIKE '%何家桥%')
WHERE id BETWEEN 5 AND 8
GO
这样就可以了

[utpcb]

你那中间不能要生成临时表

[areswang]

select * from (
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

[dawugui]

select * from 
(
  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

[正宗老冉]

学习。

[hery2002]

这样?

select * from chengdu A,
(
   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

[ojuju10]

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
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

[sqq4290]

LS的方法我都测试了，只有“老乌龟”的能通过，其余都有错

[dreamice01]

不能在嵌套子句里生成一张新表的记录吧，呵呵

把INTO #temp删除试试


SELECT * FROM (SELECT IDENTITY(int,1,1) AS id,Bus_name FROM Chengdu WHERE Bus_stops LIKE '%何家桥%')
WHERE id BETWEEN 5 AND 8

[律己修心]

SELECT * FROM (SELECT IDENTITY(int,1,1) AS id,Bus_name INTO #temp FROM Chengdu WHERE Bus_stops LIKE '%何家桥%') a
WHERE id BETWEEN 5 AND 8

[律己修心]

SELECT * FROM (SELECT IDENTITY(int,1,1) AS id,Bus_name FROM Chengdu WHERE Bus_stops LIKE '%何家桥%') a
WHERE id BETWEEN 5 AND 8

在子查询后面加个a

[律己修心]

[Quote=引用 4 楼 dawugui 的回复:]
SQL codeselect * from
(
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
[/Quote]

牛