TABLE1:
ID MC AGE
1 A 5
2 A 7
3 A 8
4 A 5
5 B 1
6 B 3
7 C 4
8 C 3
9 C 3
通过select * from table t where age=(select min(age) from table where MC=t.MC ) 得到下面的结果(得到字段AGE(按MC分组)的最小值的记录):
ID MC AGE
1 a 5
4 a 5
5 b 1
8 c 3
9 c 3
那要再通过怎么样才能得到下面的结果:(上面的结果集中取ID小的记录)
ID MC AGE
1 A 5
4 B 1
7 C 3
ID MC AGE
1 A 5
2 A 7
3 A 8
4 A 5
5 B 1
6 B 3
7 C 4
8 C 3
9 C 3
通过select * from table t where age=(select min(age) from table where MC=t.MC ) 得到下面的结果(得到字段AGE(按MC分组)的最小值的记录):
ID MC AGE
1 a 5
4 a 5
5 b 1
8 c 3
9 c 3
那要再通过怎么样才能得到下面的结果:(上面的结果集中取ID小的记录)
ID MC AGE
1 A 5
4 B 1
7 C 3
解决方案 »
- 请教一条删除重复字段的语句
- sql 问题 急待解决
- 请教文件组备份的问题
- 怎样sql把419.200变成419.2
- 求一sql,请各位大侠帮忙,在线等
- 如何用最简单的方法横向求平均 比如表 ID 数学 英语 化学 几何 平均成绩 注意里面有个ID字段不能包含在内把结果填入平均成绩字段内
- 在同一存储过程中如何实现建库并使用新建的库?急急!!
- 数据库应该做哪些维护/
- ZHUZHICHAO 请进
- 熟悉sql server7.0 存储过程的高手请进
- 提问:jsp连数据库的测试时,甩出异常:com.microsoft.sqlserver.jdbc.SQLServerException: 。。。。。
- 事务性复制不能找到链接服务器
INSERT @TB
SELECT 1, 'A', 5 UNION ALL
SELECT 2, 'A', 7 UNION ALL
SELECT 3, 'A', 8 UNION ALL
SELECT 4, 'A', 5 UNION ALL
SELECT 5, 'B', 1 UNION ALL
SELECT 6, 'B', 3 UNION ALL
SELECT 7, 'C', 4 UNION ALL
SELECT 8, 'C', 3 UNION ALL
SELECT 9, 'C', 3
SELECT * FROM @TB AS A WHERE NOT EXISTS(SELECT 1 FROM @TB WHERE MC=A.MC AND ID<A.ID)
/*
ID MC AGE
----------- ---- -----------
1 A 5
5 B 1
7 C 4
*/
from T A
inner join
(
select mc,min(age) as age
from t
group by mc
) B
on A.mc=B.mc and A.age=B.age
group by A.mc,A.age
Create table T (ID INT,MC VARCHAR(1),AGE INT)
INSERT T
SELECT 1, 'A', 5 UNION ALL
SELECT 2, 'A', 7 UNION ALL
SELECT 3, 'A', 8 UNION ALL
SELECT 4, 'A', 5 UNION ALL
SELECT 5, 'B', 1 UNION ALL
SELECT 6, 'B', 3 UNION ALL
SELECT 7, 'C', 4 UNION ALL
SELECT 8, 'C', 3 UNION ALL
SELECT 9, 'C', 3
Go
select min(A.id) as ID,A.mc,A.age
from T A
inner join
(
select mc,min(age) as age
from t
group by mc
) B
on A.mc=B.mc and A.age=B.age
group by A.mc,A.age
/*
id mc age
-----------------------
1 A 5
5 B 1
8 C 3*/
GO
drop table T
(
select *, px = (select count(1) from tb where mc = t.mc and (age < t.age or (age=t.age and id < t.id))) + 1 from tb t
) m
where px = 1
order by mc
INSERT tb
SELECT 1, 'A', 5 UNION ALL
SELECT 2, 'A', 7 UNION ALL
SELECT 3, 'A', 8 UNION ALL
SELECT 4, 'A', 5 UNION ALL
SELECT 5, 'B', 1 UNION ALL
SELECT 6, 'B', 3 UNION ALL
SELECT 7, 'C', 4 UNION ALL
SELECT 8, 'C', 3 UNION ALL
SELECT 9, 'C', 3select ID ,MC, AGE from
(
select *, px = (select count(1) from tb where mc = t.mc and (age < t.age or (age=t.age and id < t.id))) + 1 from tb t
) m
where px = 1
order by mcdrop table tb/*
ID MC AGE
----------- ---- -----------
1 A 5
5 B 1
8 C 3(所影响的行数为 3 行)
*/