实现这个功能的时候出现了点问题,从一个消费表里面查找出同一个人(pid)消费钱数最多的记录他的费用,例如下表id pid fei time
1 1 111 1983-02-11 00:00:00.000
2 1 222 1983-02-11 00:00:00.000
3 2 3333 1983-02-14 00:00:00.000
4 4 55 1983-08-14 00:00:00.000
5 2 10000 1954-09-08 00:00:00.000
6 3 400 1954-09-08 00:00:00.000
7 4 2000 1954-09-14 00:00:00.000需要取出 id 2 5 6 7 这几条这条语句执行错误 select id,pid,max(fei) from abc group by pid
sql水平实在一般,希望来个高人好好指点指点为什么聚合了后只能察看聚合那一列。
1 1 111 1983-02-11 00:00:00.000
2 1 222 1983-02-11 00:00:00.000
3 2 3333 1983-02-14 00:00:00.000
4 4 55 1983-08-14 00:00:00.000
5 2 10000 1954-09-08 00:00:00.000
6 3 400 1954-09-08 00:00:00.000
7 4 2000 1954-09-14 00:00:00.000需要取出 id 2 5 6 7 这几条这条语句执行错误 select id,pid,max(fei) from abc group by pid
sql水平实在一般,希望来个高人好好指点指点为什么聚合了后只能察看聚合那一列。
select * from abc a where not exists(select 1 from abc where pid=a.pid and id>a.id)
where
not exists(select 1 from abc where pid=A.pid and fei>A.fei)
a.*
from
表 a,
(select pid,max(fei) as fei from 表 group by pid) b
where
a.pid=b.pid and a.fei=b.fei
create table tt(id int identity,pid int,fei money,time datetime)
insert into tt select 1,111,'1983-02-11'
insert into tt select 1,222,'1983-02-11'
insert into tt select 2,3333,'1983-02-14'
insert into tt select 4,55, '1983-08-14'
insert into tt select 2,10000, '1954-09-08'
insert into tt select 3,400, '1954-09-08'
insert into tt select 4,2000, '1954-09-14'--执行查询语句
select a.*
from tt a,
(select pid,max(fei) as fei from tt group by pid) b
where a.pid=b.pid and a.fei=b.fei
order by a.fei asc--学习中...
select a.id,a.pid,a.fei,b.pid,b.maxfei from abc a,(select distinct pid,max(fei) as maxfei from abc group by pid) b where a.pid=b.pid and a.fei =b.maxfei
楼上的做法都够变态,我慢慢研究一下