一张表
字段如下
1.姓名
2.考试时间 DATE
3.语文分数
4.数学分数
5.外语分数
例子
张三 2009-01-01 96 93 77
张三 2009-01-02 95 96 67
张三 2009-01-03 78 45 80
李四 2009-01-01 76 73 97
李四 2009-01-02 93 96 46
李四 2009-01-03 98 85 80
现在要求出来的格式
姓名 语文最高分数 出现时间 语文最低分数 出现时间 数学最高分数 出现时间 数学最低分数 出现时间 外语。。。
外语也是一样的
[code="sql"]
create table langhua
(
NAME varchar2(20),
KSDate date,
CH number,
MA number,
EN number
);
insert into langhua values('张三',to_date('2009-01-01','yyyy-mm-dd'),96,93,77);
insert into langhua values('张三',to_date('2009-01-02','yyyy-mm-dd'),95,96,67);
insert into langhua values('张三',to_date('2009-01-03','yyyy-mm-dd'),78,45,80);
insert into langhua values('李四',to_date('2009-01-01','yyyy-mm-dd'),76,73,97);
insert into langhua values('李四',to_date('2009-01-02','yyyy-mm-dd'),93,96,46);
insert into langhua values('李四',to_date('2009-01-03','yyyy-mm-dd'),98,85,80);
[/code]
以下SQL已在Oracle测试通过:
[code="sql"]select t.name,
t.ch1,
(select min(KSDate) from langhua where CH=t.ch1 and name=t.name) dch1,
t.ch2,
(select min(KSDate) from langhua where CH=t.ch2 and name=t.name) dch2,
t.ma1,
(select min(KSDate) from langhua where MA=t.ma1 and name=t.name) dma1,
t.ma2,
(select min(KSDate) from langhua where MA=t.ma2 and name=t.name) dma2,
t.en1,
(select min(KSDate) from langhua where EN=t.en1 and name=t.name) den1,
t.en2,
(select min(KSDate) from langhua where EN=t.en2 and name=t.name) den2
from (select name,max(CH) ch1,min(CH) ch2,max(MA) ma1,min(MA) ma2,max(EN) en1,Min(EN) en2 from langhua group by name order by name) t[/code]
如果出现相同的最高或最低分,时间取最早,也就是最小时间(min(KSDate))
整理一下要输出的格式才能知道你需要什么数据呀！！
建议写个小的存储过程，或者用多次的检索结果存入变量合成一条记录
如果非要写成一句。。。。呃。。。抛砖引玉 ~ 您肯定能想到比我好的方法
。。。。。。。
select distinct c.name,c.yMax,b.KSDate,c.yMin,b1.KSDate from langhua b ,langhua b1 ,
(select a.name, max(a.CH) as yMax,min(a.CH) as yMin
from langhua a
group by a.name) c
where b.agentNo = c.agentNo and b.CH= c.yMax
and b1.agentNo = c.agentNo and b1.CH= c.yMin;
没想到简单的方法
我先创建临时表，然后将所有人员加到临时表里面，再将各个分数更新到临时表里面去
实现如下
select t.*,t.rowid from langhua_temp t
create global temporary table langhua_temp (
name varchar2(20),
ch_date date,
ch_point int,
ma_date date,
ma_point int,
en_date date,
en_point int
)
CREATE OR REPLACE PROCEDURE GET_POINT
AS
temp_name varchar2(20);
temp_date date;
temp_point int;
CURSOR POINT_CH IS SELECT t.name,t.ksdate,t.ch from langhua t where t.ch>=( select max(t1.ch) from langhua t1 where t.name=t1.name);
CURSOR POINT_MA IS SELECT t.name,t.ksdate,t.ma from langhua t where t.ma>=( select max(t1.ma) from langhua t1 where t.name=t1.name);
CURSOR POINT_EN IS SELECT t.name,t.ksdate,t.en from langhua t where t.en>=( select max(t1.en) from langhua t1 where t.name=t1.name);
BEGIN
INSERT INTO langhua_temp(NAME) SELECT DISTINCT NAME FROM langhua;
FOR POINT IN POINT_CH LOOP
UPDATE langhua_temp SET ch_date = POINT.ksdate,ch_point=point.ch WHERE name=point.name;
END LOOP;
FOR POINT IN POINT_MA LOOP
UPDATE langhua_temp SET ma_date = POINT.ksdate,ma_point=point.ma WHERE name=point.name;
END LOOP;
FOR POINT IN POINT_EN LOOP
UPDATE langhua_temp SET en_date = POINT.ksdate,en_point=point.en WHERE name=point.name;
END LOOP;
END;
begin
GET_POINT;
end;
在oracle中可以如下实现，sql如下:
[code="sql"]
SELECT * FROM langhua;
SELECT NAME,
MAX(ch) ch,
MAX(decode(ch_seq, 1, ksdate, NULL)) ch_ksdate,
MAX(ma) ma,
MAX(decode(ma_seq, 1, ksdate, NULL)) ma_ksdate,
MAX(en) en,
MAX(decode(en_seq, 1, ksdate, NULL)) en_ksdate
FROM (SELECT t.NAME,
ksdate,
ch,
row_number() over(PARTITION BY NAME ORDER BY ch DESC) ch_seq,
ma,
row_number() over(PARTITION BY NAME ORDER BY ma DESC) ma_seq,
en,
row_number() over(PARTITION BY NAME ORDER BY en DESC) en_seq
FROM langhua t)
WHERE ch_seq = 1
OR ma_seq = 1
OR en_seq = 1
GROUP BY NAME;[/code]
输出：
张三 96 2009/1/1 96 2009/1/2 80 2009/1/3
李四 98 2009/1/3 96 2009/1/2 97 2009/1/1
select a.NAME,a.CH as '語文最高分',a.KSDate as'語文最高分出現時間',b.MA as '數學最高分',b.KSDate as '數學最高分出現時間',c.EN as '英語最高分',c.KSDate as '英語最高分出現時間'
from
(select name,KSDate,CH from langhua a where CH=(select MAX(CH) from langhua b where a.name=b.name))a
join
(select name,KSDate,MA from langhua a where MA=(select MAX(MA) from langhua b where a.name=b.name))b
on a.NAME=b.NAME
join
(select name,KSDate,EN from langhua a where EN=(select MAX(EN) from langhua b where a.name=b.name))c
on a.NAME=c.NAME
order by NAME
[code="java"]select a.NAME,a.CH as '語文最高分',a.KSDate as'語文最高分出現時間',b.MA as '數學最高分',b.KSDate as '數學最高分出現時間',c.EN as '英語最高分',c.KSDate as '英語最高分出現時間'
from
(select name,KSDate,CH from langhua a where CH=(select MAX(CH) from langhua b where a.name=b.name))a
join
(select name,KSDate,MA from langhua a where MA=(select MAX(MA) from langhua b where a.name=b.name))b
on a.NAME=b.NAME
join
(select name,KSDate,EN from langhua a where EN=(select MAX(EN) from langhua b where a.name=b.name))c
on a.NAME=c.NAME
order by NAME[/code]
http://www.iteye.com/topic/1112015?page=2
基本上就是几个嵌套的子查询再合并：先找出 语文最高分 -> 语文最高分记录 ,类似地，语文最低分 -> 语文最低分记录 等等依次类推，然后合并所有的子查询。
如果是oracle,以上的步骤可以用rank函数来完成，但是原理是一样的。
[code="java"]select distinct c.name,
b1.KSDate CHMaxDate,c.CHMax,b2.KSDate,c.CHMin CHMinDate ,
b3.KSDate MAMaxDate,c.MAMax,b4.KSDate,c.MAMin MAMinDate ,
b5.KSDate ENMaxDate,c.ENMax,b6.KSDate,c.ENMin ENMinDate
from tb3 b1 ,tb3 b2 , tb3 b3 ,tb3 b4 , tb3 b5 ,tb3 b6,
(select a.name,
max(a.CH) CHMax,min(a.CH) CHMin,
max(a.MA) MAMax,min(a.MA) MAMin,
max(a.EN) ENMax,min(a.EN) ENMin
from tb3 a
group by a.name) c
where b1.CH= c.CHMax and b2.CH= c.CHMin
and b3.MA= c.MAMax and b4.MA= c.MAMin
and b5.EN= c.ENMax and b6.EN= c.ENMin[/code]
我把lanhua改成tb3了
select distinct c.name,c.yMax,b.KSDate,c.yMin,b1.KSDate from langhua b ,langhua b1 ,
(select a.name, max(a.CH) as yMax,min(a.CH) as yMin
from langhua a
group by a.name) c
where b.agentNo = c.agentNo and b.CH= c.yMax
and b1.agentNo = c.agentNo and b1.CH= c.yMin;
[code="java"]
select k0.name,k0.max_ch,k0.ksdate,k1.min_ch,k1.ksdate,k2.max_ma,k2.ksdate,k3.min_ma,k3.ksdate from
(select t.name,max_ch,ksdate from langhua t,
(select name , max(ch) max_ch from langhua group by name) t0
where t.name=t0.name and t.ch=t0.max_ch) k0,
(select t.name,min_ch,ksdate from langhua t,
(select name , min(ch) min_ch from langhua group by name) t1
where t.name=t1.name and t.ch=t1.min_ch) k1,
(select t.name,max_ma,ksdate from langhua t,
(select name , max(ma) max_ma from langhua group by name) t2
where t.name=t2.name and t.ma=t2.max_ma) k2,
(select t.name,min_ma,ksdate from langhua t,
(select name , min(ma) min_ma from langhua group by name) t3
where t.name=t3.name and t.ma=t3.min_ma) k3
where k0.name =+k1.name and k0.name =+k2.name and k0.name =+k3.name
[/code]
实现倒是能实现。但是总觉得这表设计的有问题，有个单一主键就好办多了
看了楼上各位的解答都能很好的实现楼主的需求，但是这些都是牺牲数据库性能为代价的。小数据量数据库可以承受，但是数据量大稍微大点费劲奥！
[b]问题一:[/b]题目中表字段以姓名为字段在表设计方面是不可取的，最简单的问题就是班级中出现同名如何处理呢？ 这个设计者要好好考虑
[b]问题二：[/b]各位的方法都是做连接处理，数据库的连接查询是做笛卡尔积处理，每个表连接都会将表数据做一个笛卡尔积，这样会导致数据库压力很大。
[quote]1.select distinct c.name,
2.b1.KSDate CHMaxDate,c.CHMax,b2.KSDate,c.CHMin CHMinDate ,
3.b3.KSDate MAMaxDate,c.MAMax,b4.KSDate,c.MAMin MAMinDate ,
4.b5.KSDate ENMaxDate,c.ENMax,b6.KSDate,c.ENMin ENMinDate
5.from tb3 b1 ,tb3 b2 , tb3 b3 ,tb3 b4 , tb3 b5 ,tb3 b6,
6.(select a.name,
7.max(a.CH) CHMax,min(a.CH) CHMin,
8.max(a.MA) MAMax,min(a.MA) MAMin,
9.max(a.EN) ENMax,min(a.EN) ENMin
10.from tb3 a
11.group by a.name) c
12.where b1.CH= c.CHMax and b2.CH= c.CHMin
13.and b3.MA= c.MAMax and b4.MA= c.MAMin
14.and b5.EN= c.ENMax and b6.EN= c.ENMin [/quote]
这个语句中，如果数据库表中只有100条数据，在内存中将要产生10的6次方的内存开销。
对于本题我的解题思路分2步，
第一步是查出姓名，
1.[quote]select distinct c.name from table [/quote]
第二步根据姓名进行循环处理。
如果需要在数据库中处理的，可以用存储过程进行处理。
如果设计者还需要考虑分页的话，就对第一步进行分页就可以了。