select * from tb t where not exists(select 1 from tb where a=t.a and b<t.b)
select a,min(b) b from tb group by a
都不对啊 结果变成了 A1 B1 A2 B2 A3 B1 想要的是 A1 B1 A2 B2 A3
select min(A) as A,isnull(min(B),'') AS B from tbl
select distinct a,b from tb
;with f as ( select id=row_number()over(partition by a order by getdate()),* from tb ) select isnull(a.a,'') as a,isnull(b.b,'') as b from (select px=row_number()over(order by getdate()),* from (select distinct id,a from f where id=1)t)a left join (select px=row_number()over(order by getdate()),* from (select distinct id,b from f where id=1)t)b on a.px=b.px
create table T (a varchar(16), b varchar(16)) insert into T select 'A1','B1' union select 'A1','B2' union select 'A2','B1' union select 'A3','B2'declare @tb_a table (id int identity(1,1), ch varchar(16)) declare @tb_b table (id int identity(1,1), ch varchar(16))insert into @tb_a select distinct a from T order by ainsert into @tb_b select distinct b from T order by bselect i.id, a.ch, b.ch from (select id from @tb_a union select id from @tb_b) as i left join @tb_a as a on a.id = i.id left join @tb_b as b on b.id = i.iddrop table T -- id ch ch -- 1 A1 B1 -- 2 A2 B2 -- 3 A3
create table T (a varchar(16), b varchar(16)) insert into T select 'A1','B1' union select 'A1','B2' union select 'A2','B1' union select 'A3','B2'declare @tb_a table (id int identity(1,1), ch varchar(16)) declare @tb_b table (id int identity(1,1), ch varchar(16))insert into @tb_a select distinct a from T order by ainsert into @tb_b select distinct b from T order by bselect i.id, a.ch, b.ch from (select id from @tb_a union select id from @tb_b) as i left join @tb_a as a on a.id = i.id left join @tb_b as b on b.id = i.iddrop table T -- id ch ch -- 1 A1 B1 -- 2 A2 B2 -- 3 A3
create table T (a varchar(16), b varchar(16)) insert into T select 'A1','B1' union select 'A1','B2' union select 'A2','B1' union select 'A3','B2'declare @tb_a table (id int identity(1,1), ch varchar(16)) declare @tb_b table (id int identity(1,1), ch varchar(16))insert into @tb_a select distinct a from T order by ainsert into @tb_b select distinct b from T order by bselect i.id, a.ch, b.ch from (select id from @tb_a union select id from @tb_b) as i left join @tb_a as a on a.id = i.id left join @tb_b as b on b.id = i.iddrop table T -- id ch ch -- 1 A1 B1 -- 2 A2 B2 -- 3 A3
select a,min(b) b from tb group by a
结果变成了
A1 B1
A2 B2
A3 B1
想要的是
A1 B1
A2 B2
A3
select min(A) as A,isnull(min(B),'') AS B from tbl
(
select id=row_number()over(partition by a order by getdate()),* from tb
)
select
isnull(a.a,'') as a,isnull(b.b,'') as b
from
(select px=row_number()over(order by getdate()),* from (select distinct id,a from f where id=1)t)a
left join
(select px=row_number()over(order by getdate()),* from (select distinct id,b from f where id=1)t)b
on
a.px=b.px
insert into T
select 'A1','B1' union
select 'A1','B2' union
select 'A2','B1' union
select 'A3','B2'declare @tb_a table (id int identity(1,1), ch varchar(16))
declare @tb_b table (id int identity(1,1), ch varchar(16))insert into @tb_a
select distinct a
from T
order by ainsert into @tb_b
select distinct b
from T
order by bselect i.id, a.ch, b.ch
from (select id from @tb_a union select id from @tb_b) as i
left join @tb_a as a on a.id = i.id
left join @tb_b as b on b.id = i.iddrop table T
-- id ch ch
-- 1 A1 B1
-- 2 A2 B2
-- 3 A3
insert into T
select 'A1','B1' union
select 'A1','B2' union
select 'A2','B1' union
select 'A3','B2'declare @tb_a table (id int identity(1,1), ch varchar(16))
declare @tb_b table (id int identity(1,1), ch varchar(16))insert into @tb_a
select distinct a
from T
order by ainsert into @tb_b
select distinct b
from T
order by bselect i.id, a.ch, b.ch
from (select id from @tb_a union select id from @tb_b) as i
left join @tb_a as a on a.id = i.id
left join @tb_b as b on b.id = i.iddrop table T
-- id ch ch
-- 1 A1 B1
-- 2 A2 B2
-- 3 A3
insert into T
select 'A1','B1' union
select 'A1','B2' union
select 'A2','B1' union
select 'A3','B2'declare @tb_a table (id int identity(1,1), ch varchar(16))
declare @tb_b table (id int identity(1,1), ch varchar(16))insert into @tb_a
select distinct a
from T
order by ainsert into @tb_b
select distinct b
from T
order by bselect i.id, a.ch, b.ch
from (select id from @tb_a union select id from @tb_b) as i
left join @tb_a as a on a.id = i.id
left join @tb_b as b on b.id = i.iddrop table T
-- id ch ch
-- 1 A1 B1
-- 2 A2 B2
-- 3 A3