A表:
[ID] [NAME]
1 A
2 B
3 C
4 DB表:
[ID] [xx]
2 xxxx
3 xxx
4 xxxx
1 xxxx
1 xxxx
2 xxxxxx如何查询出[ID] [NAME] [xxxxx]注意:[ID]要求不能有重复。[XX]显示的为max/min/任意(随便选)
[ID] [NAME]
1 A
2 B
3 C
4 DB表:
[ID] [xx]
2 xxxx
3 xxx
4 xxxx
1 xxxx
1 xxxx
2 xxxxxx如何查询出[ID] [NAME] [xxxxx]注意:[ID]要求不能有重复。[XX]显示的为max/min/任意(随便选)
from tableA a join tableB b on a.[ID] = b.[ID]
group by a.[Id], a.[Name]
insert into @t select 1,'A'
union all select 2,'B'
union all select 3,'C'
union all select 4,'D'declare @a table([ID] int,[xx] varchar(10))
insert into @a select 2,'xxxx'
union all select 3,'xxx'
union all select 4,'xxxx'
union all select 1,'xxxx'
union all select 1,'xxxx'
union all select 2,'xxxxxx'
select a.[ID],a.[NAME],max(b.[xx]) as [xx] from @t a,@a b where a.ID=b.ID group by a.[ID],a.[NAME]
from tableA a join tableB b on a.[ID] = b.[ID]
group by a.[Id], a.[Name]
order by max(b.[xx])
insert into @t select 1,'A'
union all select 2,'B'
union all select 3,'C'
union all select 4,'D'declare @a table([ID] int,[xx] varchar(10))
insert into @a select 2,'xxxx'
union all select 3,'xxx'
union all select 4,'xxxx'
union all select 1,'xxxx'
union all select 1,'xxxx'
union all select 2,'xxxxxx'
select a.[ID],a.[NAME],max(b.[xx]) as [xx] from @t a,@a b where a.ID=b.ID group by a.[ID],a.[NAME] order by b.[xx]