CREATE VIEW dbo.V_View2
AS
SELECT
id=(select count(*)
from V_View1
where
Col1>a.Col1
or
(Col1=a.Col1 and Col2>a.Col2)
or
(Col1=a.Col1 and Col2=a.Col2 Col2>=a.Col2)),
a.*
FROM
V_View1 a
order by
id
AS
SELECT
id=(select count(*)
from V_View1
where
Col1>a.Col1
or
(Col1=a.Col1 and Col2>a.Col2)
or
(Col1=a.Col1 and Col2=a.Col2 Col2>=a.Col2)),
a.*
FROM
V_View1 a
order by
id
但加一个自动编号还是有办法实现的:
create view v_1 as
Select TOP 100 PERCENT Col1, Col2, Col3, Count(RID) As Num ,
id=(select count(1) from BaseTable Group By Col1, Col2, Col3
having col1<=a.col1 and
col2<=a.col2 and col3<=a.col3)
From BaseTable a
Group By Col1, Col2, Col3
Order By Col1Desc
您的代码中 (Col1=a.Col1 and Col2=a.Col2 Col2>=a.Col2)),有问题
执行结果是Incorrect syntax near 'Col2'.
insert tb
select 1,1,2 union all
select 1,1,3 union all
select 1,1,4 union all
select 1,2,2 union all
select 1,2,3 union all
select 1,2,4 union all
select 12,1,2 union all
select 12,1,3 union all
select 12,1,4 union all
select 12,2,2 union all
select 12,2,3 union all
select 12,2,4
go
Select Col1, Col2,
id=(select count(1) from (select col1,col2 from tb where col1<a.col1 or (col1=a.col1 AND
col2<=a.col2) Group By Col1, Col2 )aa
)
From tb a
Group By Col1, Col2
Order By Col1 Descdrop table tb
/*
Col1 Col2 id
----------- ----------- -----------
12 1 3
12 2 4
1 1 1
1 2 2(所影响的行数为 4 行)
*/
select 序号=(select coun(1) from 表 where col1=T.col1 and col2=T.col2 and col3<=T.col3),* from 表 T
--解法二,用临时表增加一个标识列字段
--解法三:在物理表中添加一个字段后,最后删除--具体参考:
http://blog.csdn.net/zlp321002/archive/2005/01/12/250961.aspx
Select TOP 100 PERCENT Col1, Col2, Col3, Count(RID) As Num ,
id=(select count(1) from
(select col1,col2 from BaseTable where col1<a.col1 or (col1=a.col1 AND
(col2<a.col2 OR (COL2=A.COL2 AND COL3<=A.COL3))) Group By Col1, Col2,COL3 )aa
)
From BaseTable a
Group By Col1, Col2,COL3
Order By Col1 Desc
Col2 和 Col3 是varchar类型的字段,
照您的代码执行,create view 没有错误,但select * from V_1出现错误提示:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Win2000 Pro(EN) + SQL SERVER 2000 Person Edition(EN)
[RID] int IDENTITY(1, 1) NOT NULL PRIMARY KEY ,
[Col1] [datetime] NOT NULL ,
[Col2] varchar(10) NOT NULL ,
[Col3] varchar(200) NOT NULL ,
)
insert tb(col1,col2,col3)
select '2005-05-01','1','1' union all
select '2005-05-01','1','1' union all
select '2005-05-01','1','2' union all
select '2005-05-01','1','2' union all
select '2005-05-01','2','1' union all
select '2005-05-01','2','1' union all
select '2005-05-01','2','2' union all
select '2005-05-01','2','2' union all
select '2005-06-01','1','1' union all
select '2005-06-01','1','1' union all
select '2005-06-01','1','2' union all
select '2005-06-01','1','2' union all
select '2005-06-01','2','1' union all
select '2005-06-01','2','1' union all
select '2005-06-01','2','2' union all
select '2005-06-01','2','2'
go
CREATE VIEW V_1 AS
Select TOP 100 PERCENT Col1, Col2, Col3, Count(RID) As Num ,
id=(select count(1) from
(select col1,col2 from tb where col1<a.col1 or (col1=a.col1 AND
(col2<a.col2 OR (COL2=A.COL2 AND COL3<=A.COL3))) Group By Col1, Col2,COL3 )aa
)
From tb a
Group By Col1, Col2,COL3
Order By Col1 Desc
go
select * from v_1 order by id
/*
2005-05-01 00:00:00.000 1 1 2 1
2005-05-01 00:00:00.000 1 2 2 2
2005-05-01 00:00:00.000 2 1 2 3
2005-05-01 00:00:00.000 2 2 2 4
2005-06-01 00:00:00.000 1 1 2 5
2005-06-01 00:00:00.000 1 2 2 6
2005-06-01 00:00:00.000 2 1 2 7
2005-06-01 00:00:00.000 2 2 2 8*/
drop table tb
drop view v_1