导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求一sql算法

Pra_lijian 2008-01-19 04:02:00
将如下规律的集合
A , B , 1
A , B , 2
A , B , 3
A , C , 1
A , C , 2
A , C , 3
A , D , 1
A , D , 2
A , D , 3
想转化成:
A, B, 1
A, C, 2
A, D, 3
sql怎么写呢?
...全文
69 点赞 收藏 15
写回复
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
Pra_lijian 2008-01-19
当第三行不是1,2,3 而是一些无规律的数字时呢?
回复
ORARichard 2008-01-19
select 'A' c1   ,   'B' c2  ,   1 c3 into # union all
select 'A' , 'B' , 2 union all
select 'A' , 'B' , 3 union all
select 'A' , 'C' , 1 union all
select 'A' , 'C' , 2 union all
select 'A' , 'C' , 3 union all
select 'A' , 'D' , 1 union all
select 'A' , 'D' , 2 union all
select 'A' , 'D' , 3

select c1,c2,c3=(select count(0) from (select distinct c1,c2 from #) v where v.c1=t.c1 and v.c2<=t.c2)
from ( select distinct c1,c2 from #) t
drop table #

/*
c1 c2 c3
---- ---- -----------
A B 1
A C 2
A D 3

(3 row(s) affected)
*/
回复
dawugui 2008-01-19
--sql server 2005的写法.
create table tb(col1 varchar(10) , col2 varchar(10), col3 varchar(10))
insert into tb values('A' , 'B' , 1 )
insert into tb values('A' , 'B' , 2 )
insert into tb values('A' , 'B' , 3 )
insert into tb values('A' , 'C' , 1 )
insert into tb values('A' , 'C' , 2 )
insert into tb values('A' , 'C' , 3 )
insert into tb values('A' , 'D' , 1 )
insert into tb values('A' , 'D' , 2 )
insert into tb values('A' , 'D' , 3 )
go

select col1,col2,id = row_number() over(order by col1 , col2) from
(
select distinct col1,col2 from tb
) t

drop table tb

/*
col1 col2 id
---------- ---------- -----------
A B 1
A C 2
A D 3

(所影响的行数为 3 行)
*/
回复
wzy_love_sly 2008-01-19
declare @tb table (col1 varchar(10),col2 varchar(10),col3 int)
insert into @tb select 'a','b',1
insert into @tb select 'a','b',2
insert into @tb select 'a','b',3

insert into @tb select 'a','c',1
insert into @tb select 'a','c',2
insert into @tb select 'a','c',3

insert into @tb select 'a','d',1
insert into @tb select 'a','d',2
insert into @tb select 'a','d',3

select col1 , col2 , id = row_number() over(order by col1 , col2) from
(
select distinct col1 , col2 from @tb
) t


都行
回复
dawugui 2008-01-19
--sql server 2000的写法.
create table tb(col1 varchar(10) , col2 varchar(10), col3 varchar(10))
insert into tb values('A' , 'B' , 1 )
insert into tb values('A' , 'B' , 2 )
insert into tb values('A' , 'B' , 3 )
insert into tb values('A' , 'C' , 1 )
insert into tb values('A' , 'C' , 2 )
insert into tb values('A' , 'C' , 3 )
insert into tb values('A' , 'D' , 1 )
insert into tb values('A' , 'D' , 2 )
insert into tb values('A' , 'D' , 3 )
go

select col1,col2,id = identity(int , 1, 1) into tmp from
(
select distinct col1,col2 from tb
) t

select * from tmp

drop table tb,tmp

/*
col1 col2 id
---------- ---------- -----------
A B 1
A C 2
A D 3

(所影响的行数为 3 行)
*/
回复
中国风 2008-01-19
用大小关系时,用生成临时表的方法..
回复
中国风 2008-01-19
--用这个
--col2有大小关系时
select
distinct
col1,
col2,
col3=(select count(1) from T where col1=a.col1 and col2!>a.col2)
from
T a
回复
Pra_lijian 2008-01-19
如上方法得到结果为:
A, B, 3
A, C, 3
A, D, 3
回复
中国风 2008-01-19
以上为2000写法,05:row_number排序
第2列col2没有大小关系时,用临时表实现效率好一点,用语句嵌套效率低
回复
dawugui 2008-01-19
将如下规律的集合
A , B , 1
A , B , 2
A , B , 3
A , C , 1
A , C , 2
A , C , 3
A , D , 1
A , D , 2
A , D , 3
想转化成:
A, B, 1
A, C, 2
A, D, 3
sql怎么写呢?

--2000
select distinct col1,col2, col3 = identity(int,1,1) into tmp from tb
select * from tmp

--2005
select col1 , col2 , id = rownumber() over(order by col1 , col2) from
(
select distinct col1 , col2 from tb
) t
回复
中国风 2008-01-19
--col2有大小关系时
select
distinct
col1,
col2,
col3=(select count(1) from T where col1=a.col1 and col2!>a.col2)
from
T a
回复
wzy_love_sly 2008-01-19
insert into @tb select 'a','c',1
insert into @tb select 'a','c',2
insert into @tb select 'a','c',3

insert into @tb select 'a','d',1
insert into @tb select 'a','d',2
insert into @tb select 'a','d',3

select col1,col2,row_number() over(order by col2)
from @tb group by col1,col2


col1 col2 (无列名)
a b 1
a c 2
a d 3
回复
wzy_love_sly 2008-01-19
select col1,col2,row_number() over(order by col2)
from tb group by col1,col2
回复
中国风 2008-01-19
select
col1,
col2,
min(col3)
from t
group by col1, col2
回复
中国风 2008-01-19
select *
from
t a
where
not exists(select 1 from t where col1=a.col1 and col2=a.col2 and col3>a.col3)
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告