drop table test
create table test
(
a int,
b varchar(10),
c varchar(10)
)
insert into test values(1,'b','c')
insert into test values(1,'v','x')
insert into test values(1,'d','e')
insert into test values(2,'g','h')
insert into test values(2,'f','bn')
insert into test values(2,'12','34')
insert into test values(3,'t','f')
insert into test values(3,'k','l')
insert into test values(3,'m','n')
select a,b,c from test
现在要得到的结果是当遇见a相同的时候就只输出第一条语句,直到遇见下一条不相同的时候又在输出下调语句
要的是结果:
a b c
1 b c
2 g h
3 t f
大侠们,能否不用游标有用简单一点的查询语句啊????
create table test
(
a int,
b varchar(10),
c varchar(10)
)
insert into test values(1,'b','c')
insert into test values(1,'v','x')
insert into test values(1,'d','e')
insert into test values(2,'g','h')
insert into test values(2,'f','bn')
insert into test values(2,'12','34')
insert into test values(3,'t','f')
insert into test values(3,'k','l')
insert into test values(3,'m','n')
select a,b,c from test
现在要得到的结果是当遇见a相同的时候就只输出第一条语句,直到遇见下一条不相同的时候又在输出下调语句
要的是结果:
a b c
1 b c
2 g h
3 t f
大侠们,能否不用游标有用简单一点的查询语句啊????
select * from # t where not exists(select 1 from # where a=t.a and id<t.id)
(
a int,
b varchar(10),
c varchar(10)
)
insert into #test values(1,'b','c')
insert into #test values(1,'v','x')
insert into #test values(1,'d','e')
insert into #test values(2,'g','h')
insert into #test values(2,'f','bn')
insert into #test values(2,'12','34')
insert into #test values(3,'t','f')
insert into #test values(3,'k','l')
insert into #test values(3,'m','n')
select a,b,c from #test Select identity(int,1,1) as id ,* into #s from #test
select * from #s
select * from #s inner join (select min(id) as id ,a from #s group by a) a on #s.id=a.id
select a,b,c from # t where id in(select min(id) from # where a=t.a)
select a,b,c from # t where id in(select min(id) from # where a=t.a)
(
a int,
b varchar(10),
c varchar(10)
)
insert into testgs values(1,'b','c')
insert into testgs values(1,'v','x')
insert into testgs values(1,'d','e')
insert into testgs values(2,'g','h')
insert into testgs values(2,'f','bn')
insert into testgs values(2,'12','34')
insert into testgs values(3,'t','f')
insert into testgs values(3,'k','l')
insert into testgs values(3,'m','n')
SELECT * FROM testgs a WHERE NOT exists(SELECT 1 FROM testgs WHERE a=a.a and CHECKSUM(b,c)<CHECKSUM(a.b,a.c))
--result
/*
a b c
----------- ---------- ----------
1 d e
2 g h
3 m n(所影响的行数为 3 行)
*/
(
a int,
b varchar(10),
c varchar(10)
)
insert into test values(1,'b','c')
insert into test values(1,'v','x')
insert into test values(1,'d','e')
insert into test values(2,'g','h')
insert into test values(2,'f','bn')
insert into test values(2,'12','34')
insert into test values(3,'t','f')
insert into test values(3,'k','l')
insert into test values(3,'m','n')
select id=identity(int,1,1),* into # from test
select a,b,c from # T where not exists(select 1 from # where a = T.a and id <T.id)
drop table test
drop table test
create table test
(
a varchar(10),
b varchar(10),
c varchar(10)
)
insert into test values('aa','b','c')
insert into test values('aa','v','x')
insert into test values('aa','d','e')
insert into test values('bb','g','h')
insert into test values('bb','f','bn')
insert into test values('bb','12','34')
insert into test values('cc','t','f')
insert into test values('cc','k','l')
insert into test values('cc','m','n')
要的是结果:
a b c
aa b c
bb g h
cc t f
大侠们,能否不用游标有用简单一点的查询语句啊????
(
a varchar(10),
b varchar(10),
c varchar(10)
)
insert into test values('aa','b','c')
insert into test values('aa','v','x')
insert into test values('aa','d','e')
insert into test values('bb','g','h')
insert into test values('bb','f','bn')
insert into test values('bb','12','34')
insert into test values('cc','t','f')
insert into test values('cc','k','l')
insert into test values('cc','m','n')
select id=identity(int,1,1),* into # from test
select a,b,c from # T where not exists(select 1 from # where a = T.a and id <T.id)
drop table test,#