22,300
社区成员




//表结构
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 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
*/
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 Height,[width],[pvt],idno=identity(int,1,1) into #t from test_tb
group by Height,[width],[pvt] order by Height
select 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.pvt
drop 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 行)
*/
--这样?
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
*/
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 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 行受影响)
*/
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 行受影响)
*/