22,296
社区成员
发帖
与我相关
我的任务
分享高分请教一个sql语句的写法,在线等待
//表结构
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'
//要的结果
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
说明一下这个结果的由来,首先按升序取一条(给他加上一个新的ID为1),然后判断除id列表的其它列,如果相同的话,相同的显示在一起
显示结果中最新的列是根据条件自动添加的,还有就是真实的数据中表中的id可能是不连续的
...全文
请发表友善的回复…
发表回复
lixueming3000 2008-09-18
- 打赏
- 举报
经过实现数据测试happyflystone,Haiwer符合要求,而且happyflystone的适合不同的字段类型，lgxyz用了临时表,也是一种解决问题的思路,在此感谢各位,结贴给分.
等不到来世 2008-09-18
- 打赏
- 举报
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
*/
昵称被占用了 2008-09-18
- 打赏
- 举报
估计他要按照 width pvt
排序，不是按照height和id
排序，不是按照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 行）lixueming3000 2008-09-18
- 打赏
- 举报
qianjin036a你这个不是我想要的,Garnett_KG 和happyflystone 这个是我想要的,正在测试实际数据,感谢各位
lgxyz 2008-09-18
- 打赏
- 举报
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 行）
*/
Garnett_KG 2008-09-18
- 打赏
- 举报
--这样？
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
*/
-晴天 2008-09-18
- 打赏
- 举报
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 行受影响)
*/-晴天 2008-09-18
- 打赏
- 举报
[Quote=引用 5 楼 lixueming3000 的回复:]
上面的二位,说的是因为这个表的测试数据,你们的那个才能实现的,实际中那样都是不对的
[/Quote]
那,实际的数据是怎么样的呢?
上面的二位,说的是因为这个表的测试数据,你们的那个才能实现的,实际中那样都是不对的
[/Quote]
那,实际的数据是怎么样的呢?
lixueming3000 2008-09-18
- 打赏
- 举报
当然不一样了,那是因为测试数据相同的我都放在一起了,请细看
-狙击手- 2008-09-18
- 打赏
- 举报
[Quote=引用 5 楼 lixueming3000 的回复:]
上面的二位,说的是因为这个表的测试数据,你们的那个才能实现的,实际中那样都是不对的
[/Quote]
[Quote=引用 7 楼 happyflystone 的回复:]
select pid = (select count(distinct CHECKSUM (height,width,pvt))
from test_tb where id <= a.id)
,*
from test_tb a
[/Quote]
上面的二位,说的是因为这个表的测试数据,你们的那个才能实现的,实际中那样都是不对的
[/Quote]
[Quote=引用 7 楼 happyflystone 的回复:]
select pid = (select count(distinct CHECKSUM (height,width,pvt))
from test_tb where id <= a.id)
,*
from test_tb a
[/Quote]
lixueming3000 2008-09-18
- 打赏
- 举报
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 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'
yangkunjie 2008-09-18
- 打赏
- 举报
感觉你表达的有问题，或者说表达和举例之间存在差距
cl9132008 2008-09-18
- 打赏
- 举报
[Quote=引用 3 楼 happyflystone 的回复:]
select pid = (select count(distinct height)
from test_tb where id <= a.id)
,*
from test_tb a
[/Quote]
确实如此！有待更正。。。
select pid = (select count(distinct height)
from test_tb where id <= a.id)
,*
from test_tb a
[/Quote]
确实如此！有待更正。。。
-狙击手- 2008-09-18
- 打赏
- 举报
select pid = (select count(distinct CHECKSUM (height,width,pvt))
from test_tb where id <= a.id)
,*
from test_tb a
from test_tb where id <= a.id)
,*
from test_tb a
-晴天 2008-09-18
- 打赏
- 举报
如果源表未排序,则
select height+1,height,width,pvt from test_tb order by height
select height+1,height,width,pvt from test_tb order by height
lixueming3000 2008-09-18
- 打赏
- 举报
上面的二位,说的是因为这个表的测试数据,你们的那个才能实现的,实际中那样都是不对的
fa_ge 2008-09-18
- 打赏
- 举报
结果和表的数据不是一样的吗?看了几遍没看出什么不同,楼下继续
-狙击手- 2008-09-18
- 打赏
- 举报
select pid = (select count(distinct height)
from test_tb where id <= a.id)
,*
from test_tb a
from test_tb where id <= a.id)
,*
from test_tb a
水族杰纶 2008-09-18
- 打赏
- 举报
select IDnew=Height+1, Height,[width], [pvt] from [dbo].[test_tb]
???
???
-晴天 2008-09-18
- 打赏
- 举报
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 行受影响)
*/是这样么?
