量表关联，有些晕，来个高人。

[shuai45]

test表：

id name
1 a
2 b
3 c
4 d
5 e
6 f


example表：

id testId sort
1 1 3
2 1 3
3 1 3
4 2 1
5 2 1
6 3 2

result:
id name
2 b
3 c
1 a
4 d
5 e
6 f

[AcHerat]

select a.id,a.name
from test a left join example b on a.id = b.testid
group by a.id,a.name
order by (case when b.sort is null then 1 else 0 end),max(b.sort),a.id

[AcHerat]

create table test(id int,name varchar(10))
insert into test
select 1 ,'a' union all
select 2 ,'b' union all
select 3 ,'c' union all
select 4 ,'d' union all
select 5 ,'e' union all
select 6 ,'f'
go

create table example(id int,testId int,sort int)
insert into example
select 1 ,1 ,3 union all
select 2 ,1 ,3 union all
select 3 ,1 ,3 union all
select 4 ,2 ,1 union all
select 5 ,2 ,1 union all
select 6 ,3 ,2
go

select a.id,a.name
from test a left join example b on a.id = b.testid
group by a.id,a.name
order by (case when max(b.sort) is null then 1 else 0 end),max(b.sort),a.id

drop table test,example

/************

id          name
----------- ----------
2           b
3           c
1           a
4           d
5           e
6           f
警告: 聚合或其他 SET 操作消除了空值。

(6 行受影响)

[shuai45]

[Quote=引用 2 楼 acherat 的回复:]
SQL code

create table test(id int,name varchar(10))
insert into test
select 1 ,'a' union all
select 2 ,'b' union all
select 3 ,'c' union all
select 4 ,'d' union all
select 5 ,'e' union all
s……
[/Quote]
test 太多字段 group 不过来，还有其它方法吗

[AcHerat]

select a.id,a.name
from test a left join 
	(select testid,max(sort) sort from example group by testid) b on a.id = b.testid
order by (case when b.sort is null then 1 else 0 end),b.sort,a.id

[shuai45]

你写SQL比发改委出台政策还快，不进中央可惜了。
分给你了，多谢。

[fancy0109]

declare @t1 table(id int, name varchar(10))
declare @t2 table(id int, testid int, sort int)

insert into @t1(id, name)
select 1, 'a'
union
select 2, 'b'
union
select 3, 'c'
union
select 4, 'd'
union
select 5, 'e'
union
select 6, 'f'

insert into @t2(id, testid, sort)
select 1, 1, 3
union
select 2, 1, 3
union
select 3, 1, 3
union
select 4, 2, 1
union
select 5, 2, 1
union
select 6, 3, 2

select id, name 
from(
select distinct a.id, a.name, isnull(cast(b.sort as varchar(10)), 'A') sort
from @t1 a left join @t2 b on a.id = b.testid
) a
order by sort, a.name