表Temp结构如下:
Name Grade SNum
张三 B 1000
李四 A 1004
王五 B 1001想通过在视图中插入一个AutoID列作为序号,先按Grade分组,序号自动迭加。视图vw_Temp效果如下:
AutoID Name Grade SNum
1 李四 A 1004
2 张三 B 1000
3 王五 B 1001
应该如何实现?SELECT TOP 100 PERCENT
(SELECT SUM(1)
FROM Temp
WHERE SNum <= A.SNum) AS AutoID, A.*
FROM dbo.Temp A
ORDER BY AutoID
Name Grade SNum
张三 B 1000
李四 A 1004
王五 B 1001想通过在视图中插入一个AutoID列作为序号,先按Grade分组,序号自动迭加。视图vw_Temp效果如下:
AutoID Name Grade SNum
1 李四 A 1004
2 张三 B 1000
3 王五 B 1001
应该如何实现?SELECT TOP 100 PERCENT
(SELECT SUM(1)
FROM Temp
WHERE SNum <= A.SNum) AS AutoID, A.*
FROM dbo.Temp A
ORDER BY AutoID
from Temp
from Temp
2> go
Name |Grade|SNum
----------|-----|-----------
张三 |B | 1000
李四 |A | 1004
王五 |B | 1001(3 rows affected)
1> select row_number() over (order by SNum desc) as AutoID,*
2> from Temp
3> go
AutoID |Name |Grade|SNum
--------------------|----------|-----|-----------
1|李四 |A | 1004
2|王五 |B | 1001
3|张三 |B | 1000(3 rows affected)
1>
select
autoid=row_number()over(partition by Grade order by getdate()),*
from
tb
select
autoid=row_number()over(order by grade),*
from
tb
from Temp t1> select (select count(*) from Temp where SNum>=t.SNum) as AutoID,*
2> from Temp t
3> go
AutoID |Name |Grade|SNum
-----------|----------|-----|-----------
3|张三 |B | 1000
1|李四 |A | 1004
2|王五 |B | 1001(3 rows affected)
1>
select
autoid=(select count(Grade)+1 from TB where T.Grade>Grade),*
from
TB t
select (select count(*) from Temp where SNum>=t.SNum) as AutoID,*
from Temp t
order by SNum desc1> select (select count(*) from Temp where SNum>=t.SNum) as AutoID,*
2> from Temp t
3> order by SNum desc
4> go
AutoID |Name |Grade|SNum
-----------|----------|-----|-----------
1|李四 |A | 1004
2|王五 |B | 1001
3|张三 |B | 1000(3 rows affected)
1>
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone*/-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(NameGrade varchar(1),SNum int)
Go
Insert into TB
SELECT 'B',1000 union all
SELECT 'A',1004 union all
SELECT 'B',1001
Go
--Start
Select ID=IDENTITY(INT,1,1),* INTO #T from TB ORDER BY NameGradeSELECT * FROM #T
--Result:
/*
ID NameGrade SNum
----------- --------- -----------
1 A 1004
2 B 1000
3 B 1001(所影响的行数为 3 行)*/
--End
select (select count(*) from Temp where Grade>t.Grade or Grade=t.Grade and SNum>=t.SNum) as AutoID,*
from Temp t
order by Grade,SNum desc1> select (select count(*) from Temp where Grade>t.Grade or Grade=t.Grade and SNum>=t.SNum) as AutoID,*
2> from Temp t
3> order by Grade,SNum desc
4> go
AutoID |Name |Grade|SNum
-----------|----------|-----|-----------
3|李四 |A | 1004
1|王五 |B | 1001
2|张三 |B | 1000(3 rows affected)
1>
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
不好意思,结果应该如下,而您所给的结果AutoID不是按顺序生成。AutoID |Name |Grade|SNum
-----------|----------|-----|-----------
1|李四 |A | 1004
2|王五 |B | 1001
3|张三 |B | 1000
from Temp t
order by Grade,SNum desc1> select (select count(*) from Temp where Grade<t.Grade or Grade=t.Grade and SNum>=t.SNum) as AutoID,*
2> from Temp t
3> order by Grade,SNum desc
4> go
AutoID |Name |Grade|SNum
-----------|----------|-----|-----------
1|李四 |A | 1004
2|王五 |B | 1001
3|张三 |B | 1000(3 rows affected)
1>