A B C 1 1 2006-1-1 2 2 2006-1-1 3 3 2006-1-2 4 4 2006-1-2 select m.* from tb, (select c,min(a) as a from tb group by c) n where m.c = n.c and m.a = n.a在你的例中取min(b)也可.
而且top 1只会出来一条记录啊。
if object_id('pubs..tb') is not null drop table tb gocreate table tb ( a int, b int, c varchar(10) )insert into tb(a,b,c) values(1, 1, '2006-1-1') insert into tb(a,b,c) values(2 , 2, '2006-1-1') insert into tb(a,b,c) values(3 , 3, '2006-1-2') insert into tb(a,b,c) values(4 , 4, '2006-1-2')select m.* from tb m, (select c,min(a) as a from tb group by c) n where m.c = n.c and m.a = n.adrop table tb a b c ----------- ----------- ---------- 1 1 2006-1-1 3 3 2006-1-2(所影响的行数为 2 行)
另外一种方法if object_id('pubs..tb') is not null drop table tb gocreate table tb ( a int, b int, c varchar(10) )insert into tb(a,b,c) values(1, 1, '2006-1-1') insert into tb(a,b,c) values(2 , 2, '2006-1-1') insert into tb(a,b,c) values(3 , 3, '2006-1-2') insert into tb(a,b,c) values(4 , 4, '2006-1-2')select * from tb b where a=(select top 1 a from tb where c=b.c)drop table tba b c ----------- ----------- ---------- 1 1 2006-1-1 3 3 2006-1-2(所影响的行数为 2 行)
To : dawugui(潇洒老乌龟) 那如果表中是下面这个情况呢?? A B C 1 1 2006-1-1 1 1 2006-1-1 1 2 2006-1-1 2 1 2006-1-1 3 3 2006-1-2 3 3 2006-1-2 3 4 2006-1-2 4 3 2006-1-2
declare @tb table ( a int, b int, c varchar(10) )insert into @tb(a,b,c) values(1, 1, '2006-1-1') insert into @tb(a,b,c) values(1, 1, '2006-1-1') insert into @tb(a,b,c) values(1, 2, '2006-1-1') insert into @tb(a,b,c) values(2, 1, '2006-1-1') insert into @tb(a,b,c) values(3 , 3, '2006-1-2') insert into @tb(a,b,c) values(3 , 3, '2006-1-2') insert into @tb(a,b,c) values(3 , 4, '2006-1-2') insert into @tb(a,b,c) values(4 , 3, '2006-1-2')select * from @tb b where a=(select top 1 a from @tb where c=b.c)这个是结果a b c 1 1 2006-1-1 1 1 2006-1-1 1 2 2006-1-1 3 3 2006-1-2 3 3 2006-1-2 3 4 2006-1-2已经违背了要求。 希望的结果是这样的: a b c 1 1 2006-1-1 3 3 2006-1-2
To : dawugui(潇洒老乌龟) 那如果表中是下面这个情况呢?? A B C 1 1 2006-1-1 1 1 2006-1-1 1 2 2006-1-1 2 1 2006-1-1 3 3 2006-1-2 3 3 2006-1-2 3 4 2006-1-2 4 3 2006-1-2select id1=identity(int,1,1),* into t1 from tb select m.a,m.b,m.c from t1 m, (select c,min(id1) as id1 from t1 group by c) n where m.id1 = n.id1 and m.c = n.c
看来避免不了新建identity临时表啊。唉~~
declare @tb table ( a int, b int, c varchar(10) )insert into @tb(a,b,c) values(1, 1, '2006-1-1') insert into @tb(a,b,c) values(1, 1, '2006-1-1') insert into @tb(a,b,c) values(1, 2, '2006-1-1') insert into @tb(a,b,c) values(2, 1, '2006-1-1') insert into @tb(a,b,c) values(3 , 3, '2006-1-2') insert into @tb(a,b,c) values(3 , 3, '2006-1-2') insert into @tb(a,b,c) values(3 , 4, '2006-1-2') insert into @tb(a,b,c) values(4 , 3, '2006-1-2') select min(a),min(b),c from @tb group by c
To : xluzhong(Ralph) 如果min(a), min(b)的话,有可能结果不属于同一条记录。 例如 a b c 1 5 2006-1-1 5 1 2006-1-1你的结果: a b c 1 1 2006-1-1期望结果: a b c 1 5 2006-1-1嗯,好了,暂时到这里。谢谢大家的讨论。结了。
1 1 2006-1-1
2 2 2006-1-1
3 3 2006-1-2
4 4 2006-1-2
select m.* from tb,
(select c,min(a) as a from tb group by c) n
where m.c = n.c and m.a = n.a在你的例中取min(b)也可.
drop table tb
gocreate table tb
(
a int,
b int,
c varchar(10)
)insert into tb(a,b,c) values(1, 1, '2006-1-1')
insert into tb(a,b,c) values(2 , 2, '2006-1-1')
insert into tb(a,b,c) values(3 , 3, '2006-1-2')
insert into tb(a,b,c) values(4 , 4, '2006-1-2')select m.* from tb m,
(select c,min(a) as a from tb group by c) n
where m.c = n.c and m.a = n.adrop table tb
a b c
----------- ----------- ----------
1 1 2006-1-1
3 3 2006-1-2(所影响的行数为 2 行)
drop table tb
gocreate table tb
(
a int,
b int,
c varchar(10)
)insert into tb(a,b,c) values(1, 1, '2006-1-1')
insert into tb(a,b,c) values(2 , 2, '2006-1-1')
insert into tb(a,b,c) values(3 , 3, '2006-1-2')
insert into tb(a,b,c) values(4 , 4, '2006-1-2')select * from tb b
where a=(select top 1 a from tb where c=b.c)drop table tba b c
----------- ----------- ----------
1 1 2006-1-1
3 3 2006-1-2(所影响的行数为 2 行)
那如果表中是下面这个情况呢??
A B C
1 1 2006-1-1
1 1 2006-1-1
1 2 2006-1-1
2 1 2006-1-1
3 3 2006-1-2
3 3 2006-1-2
3 4 2006-1-2
4 3 2006-1-2
(
a int,
b int,
c varchar(10)
)insert into @tb(a,b,c) values(1, 1, '2006-1-1')
insert into @tb(a,b,c) values(1, 1, '2006-1-1')
insert into @tb(a,b,c) values(1, 2, '2006-1-1')
insert into @tb(a,b,c) values(2, 1, '2006-1-1')
insert into @tb(a,b,c) values(3 , 3, '2006-1-2')
insert into @tb(a,b,c) values(3 , 3, '2006-1-2')
insert into @tb(a,b,c) values(3 , 4, '2006-1-2')
insert into @tb(a,b,c) values(4 , 3, '2006-1-2')select * from @tb b
where a=(select top 1 a from @tb where c=b.c)这个是结果a b c
1 1 2006-1-1
1 1 2006-1-1
1 2 2006-1-1
3 3 2006-1-2
3 3 2006-1-2
3 4 2006-1-2已经违背了要求。
希望的结果是这样的:
a b c
1 1 2006-1-1
3 3 2006-1-2
那如果表中是下面这个情况呢??
A B C
1 1 2006-1-1
1 1 2006-1-1
1 2 2006-1-1
2 1 2006-1-1
3 3 2006-1-2
3 3 2006-1-2
3 4 2006-1-2
4 3 2006-1-2select id1=identity(int,1,1),* into t1 from tb
select m.a,m.b,m.c from t1 m,
(select c,min(id1) as id1 from t1 group by c) n
where m.id1 = n.id1 and m.c = n.c
(
a int,
b int,
c varchar(10)
)insert into @tb(a,b,c) values(1, 1, '2006-1-1')
insert into @tb(a,b,c) values(1, 1, '2006-1-1')
insert into @tb(a,b,c) values(1, 2, '2006-1-1')
insert into @tb(a,b,c) values(2, 1, '2006-1-1')
insert into @tb(a,b,c) values(3 , 3, '2006-1-2')
insert into @tb(a,b,c) values(3 , 3, '2006-1-2')
insert into @tb(a,b,c) values(3 , 4, '2006-1-2')
insert into @tb(a,b,c) values(4 , 3, '2006-1-2')
select min(a),min(b),c from @tb group by c
如果min(a), min(b)的话,有可能结果不属于同一条记录。
例如
a b c
1 5 2006-1-1
5 1 2006-1-1你的结果:
a b c
1 1 2006-1-1期望结果:
a b c
1 5 2006-1-1嗯,好了,暂时到这里。谢谢大家的讨论。结了。