问一个分组问题？

[chaozhou]

table1
(
id varchar(8) primary key,
name varchar(64)
);

table2
(
sd varchar(12) primary key,
id varchar(8) foreign key (id) references table1(id)
aaa varchar(64)
);

table1表数据
00000001 a
00000002 b
00000003 c
00000004 d

table2表数据
dfgsdfg 00000001 56756
sdghggt 00000001 56776
yuhfggg 00000002 58736
fgfghgh 00000003 56596
jkhjkjk 00000003 56676

想得到下面的数据
dfgsdfg 00000001 a 56756
yuhfggg 00000002 b 58736
jkhjkjk 00000003 c 56676

sql写成两个
(1)select table2.sd,table1.id,table1,name,table2.aaa from table1,table2 where table2.[id]=table1.[id] group by table1.id order by table2.aaa
(2)select table2.sd,distinct(table1.id),table1,name,table2.aaa from table1,table2 where table2.[id]=table1.[id] order by table2.aaa

问上面两个sql语句，那个可以运行，如果两个都不可以，sql应该怎样写？谢谢

[-晴天]

select a.sd,a.id,b.name,a.aaa
from table2 a inner join table1 b on a.id=b.id
where not exists(select 1 from table2 where id=a.id and aaa<a.aaa)

[chaozhou]

aaa字段不是都不相等的，有个别两个记录相等的。

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-09-28 15:39:09
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
go 
create table [table1]([col1] varchar(8),[col2] varchar(1))
insert [table1]
select '00000001','a' union all
select '00000002','b' union all
select '00000003','c' union all
select '00000004','d'
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
go 
create table [table2]([col3] varchar(7),[col1] varchar(8),[col4] int)
insert [table2]
select 'dfgsdfg','00000001',56756 union all
select 'sdghggt','00000001',56776 union all
select 'yuhfggg','00000002',58736 union all
select 'fgfghgh','00000003',56596 union all
select 'jkhjkjk','00000003',56676
--------------开始查询--------------------------
select
  b.col3,a.*,b.col4
from
  table1 a join table2 b
on
  a.col1=b.col1
and
  b.col4=(select MIN(col4) from table2  where col1=b.col1 )
----------------结果----------------------------
/* col3    col1     col2 col4
------- -------- ---- -----------
dfgsdfg 00000001 a    56756
yuhfggg 00000002 b    58736
fgfghgh 00000003 c    56596

(3 行受影响)

*/

[中国风]

select 
MIN(b.sd) AS sd,a.id,a.name,b.aaa 
from table1 AS a,table2 AS b  
where b.[id]=a.[id] 
GROUP BY a.id,b.aaa,a.name
order by b.aaa

[baiynije]

select a.sd,a.id,b.name,a.aaa
from table2 a inner join table1 b on a.id=b.id
where a.aaa = (select min(aaa) from table2 where table2.id = a.id)

[chaozhou]

假如
dfgsdfg 00000001 56756
sdghggt 00000001 56756
这样怎么办啊就是出现两个记录，aaa字段最小都是56756

[中国风]

[Quote=引用 6 楼 chaozhou 的回复:]

假如
dfgsdfg 00000001 56756
sdghggt 00000001 56756
这样怎么办啊就是出现两个记录，aaa字段最小都是56756
[/Quote]

--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
go 
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
go 



CREATE TABLE table1
(
id varchar(8) primary key,
name varchar(64)
);

CREATE TABLE  table2
(
sd varchar(12) primary key,
id varchar(8) foreign key (id) references table1(id),
aaa varchar(64)
);

insert [table1]
select '00000001','a' union all
select '00000002','b' union all
select '00000003','c' union all
select '00000004','d'


insert [table2]
select 'dfgsdfg','00000001',56756 union all
select 'sdghggt','00000001',56776 union all
select 'yuhfggg','00000002',58736 union all
select 'fgfghgh','00000003',56596 union all
select 'jkhjkjk','00000003',56676
GO
SELECT b.sd AS sd,a.id,a.name,b.aaa
from table1 AS a,[table2] AS b
where b.[id]=a.[id] 
AND NOT exists(SELECT 1 FROM table2 WHERE ID=b.ID AND aaa<b.aaa)
AND NOT EXISTS(SELECT 1 FROM table2 WHERE ID=b.ID AND aaa=b.aaa AND sd<b.sd)
ORDER BY ID

/*
sd	id	name	aaa
dfgsdfg	00000001	a	56756
yuhfggg	00000002	b	58736
fgfghgh	00000003	c	56596
*/