1 select p.Id,p.Title,g.* from testPager p
2 inner join pageQuestionGroup g on p.id=g.testPagerId
3 where publicDate is null
4 order by p.id,g.id
5
6
7
8
9 declare @testPagerId int
10 set @testPagerId=33
11
12 IF OBJECT_ID('dbo.#GroupQuestion','U') IS NOT NULL DROP TABLE dbo.#GroupQuestion;
13
14 SELECT *
15 INTO dbo.#GroupQuestion
16 FROM GroupQuestion where testPagerId=@testPagerId
17 order by id
18
19 -- 声明变量
20 DECLARE
21 @id INT,
22 @rank int
23 set @rank=0
24
25 WHILE EXISTS(SELECT id FROM dbo.#GroupQuestion)
26 BEGIN
27 -- 也可以使用top 1
28 set @rank=@rank+1
29 SELECT top 1 @id=id FROM dbo.#GroupQuestion;
30 UPDATE GroupQuestion SET sort=@rank WHERE id=@id;
31 DELETE FROM dbo.#GroupQuestion WHERE id=@id;
32 END
33 select * from dbo.#GroupQuestion
34 DROP TABLE dbo.#GroupQuestion;
