CREATE TABLE [dbo].[test_tb](
[id] [int] IDENTITY(1,1) NOT NULL,
[height] [int] NULL,
[width] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[pvt] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into test_tb
select 0,'5.11','6.11'
union all
select 0,'5.11','6.11'
union all
select 0,'5.11','6.11'
union all
select 0,'5.11','6.11'
union all
select 0,'5.11','6.11'
union all
select 1,'5.22','6.22'
union all
select 1,'5.22','6.22'
union all
select 1,'5.22','6.22'
union all
select 1,'5.22','6.22'
union all
select 1,'5.22','6.22'
union all
select 2,'5.33','6.33'
select height+1,height,width,pvt from test_tb
go
drop table test_tb
/*
(11 行受影响)
height width pvt
----------- ----------- ---------- ----------
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
3 2 5.33 6.33 (11 行受影响)
*/
是这样么?
[id] [int] IDENTITY(1,1) NOT NULL,
[height] [int] NULL,
[width] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[pvt] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into test_tb
select 0,'5.11','6.11'
union all
select 0,'5.11','6.11'
union all
select 0,'5.11','6.11'
union all
select 0,'5.11','6.11'
union all
select 0,'5.11','6.11'
union all
select 1,'5.22','6.22'
union all
select 1,'5.22','6.22'
union all
select 1,'5.22','6.22'
union all
select 1,'5.22','6.22'
union all
select 1,'5.22','6.22'
union all
select 2,'5.33','6.33'
select height+1,height,width,pvt from test_tb
go
drop table test_tb
/*
(11 行受影响)
height width pvt
----------- ----------- ---------- ----------
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
3 2 5.33 6.33 (11 行受影响)
*/
是这样么?
???
from test_tb where id <= a.id)
,*
from test_tb a
select height+1,height,width,pvt from test_tb order by height
from test_tb where id <= a.id)
,*
from test_tb a
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 45,'5.33','6.33'
[id] [int] IDENTITY(1,1) NOT NULL,
[height] [int] NULL,
[width] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[pvt] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into test_tb
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 45,'5.33','6.33'
select height+1,height,width,pvt from test_tb order by height
go
drop table test_tb
/*
height width pvt
----------- ----------- ---------- ----------
46 45 5.33 6.33
51 50 5.11 6.11
51 50 5.11 6.11
51 50 5.11 6.11
51 50 5.11 6.11
51 50 5.11 6.11
71 70 5.22 6.22
71 70 5.22 6.22
71 70 5.22 6.22
71 70 5.22 6.22
71 70 5.22 6.22 (11 行受影响)*/
--这样?
select (
SELECT COUNT(DISTINCT height)
FROM test_tb
WHERE height<=a.height
AND width<=a.width
AND pvt<=a.pvt
) as New_ID,
height,width,pvt
from test_tb a/*
NewID height width pvt
----------------------------------------
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
3 2 5.33 6.33 */
select Height,[width],[pvt],idno=identity(int,1,1) into #t from test_tb
group by Height,[width],[pvt] order by Heightselect ID=idno,a.Height,a.width,a.pvt from test_tb a
left join #t b
on a.Height=b.Height and a.width=b.width and a.pvt=b.pvtdrop table #t/*
ID Height width pvt
----------- ----------- ---------- ----------
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
3 2 5.33 6.33 (所影响的行数为 11 行)
*/
排序,不是按照height和id
CREATE TABLE [dbo].[test_tb](
[id] [int] IDENTITY(1,1) NOT NULL,
[height] [int] NULL,
[width] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[pvt] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
insert into test_tb
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 50,'5.11','6.11'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 70,'5.22','6.22'
union all
select 45,'5.33','6.33'
select (select count(*) from (select distinct width,pvt from test_tb where width<a.width or width=a.width and pvt<=a.pvt) as t) as id
,height,width,pvt from test_tb a order by width,pvt
go--结果
id height width pvt
----------- ----------- ---------- ----------
1 50 5.11 6.11
1 50 5.11 6.11
1 50 5.11 6.11
1 50 5.11 6.11
1 50 5.11 6.11
2 70 5.22 6.22
2 70 5.22 6.22
2 70 5.22 6.22
2 70 5.22 6.22
2 70 5.22 6.22
3 45 5.33 6.33 (所影响的行数为 11 行)
select nid=(select count(distinct height) from test_tb where height<=a.height),a.height,a.width,a.pvt from test_tb a/*
--------------------------------------
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
1 0 5.11 6.11
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
2 1 5.22 6.22
3 2 5.33 6.33
*/