数据结构大概是这样:日期 部门 营业额
09-20 部门1 100
09-20 部门2 200
09-20 部门3 300
09-19 部门2 400
09-19 部门4 500
……大概是这么个样子。需要计算出每天营业额最高的部门及其营业额。以前好象写过这种东西,不过后来因为被SQL SERVER的排名函数惯坏了,现在完全不记得了。请各位指点指点小弟,不尽感激
09-20 部门1 100
09-20 部门2 200
09-20 部门3 300
09-19 部门2 400
09-19 部门4 500
……大概是这么个样子。需要计算出每天营业额最高的部门及其营业额。以前好象写过这种东西,不过后来因为被SQL SERVER的排名函数惯坏了,现在完全不记得了。请各位指点指点小弟,不尽感激
where not exists (select 1 from yourTable where 日期=a.日期 and 营业额>a.营业额);
select 部门,sum(营业额) 总营业额
from 表名
group by 部门
order by 总营业额 desc
limit 1;
from yourTable a inner join (select 日期,max(营业额) as max_营业额 from yourTable group by 日期) b
on a.日期=b.日期 and a.营业额=b.max_营业额
mysql> select * from t;
+------------+-------+----------+
| dd | dept | business |
+------------+-------+----------+
| 2009-09-20 | 部门1 | 100 |
| 2009-09-20 | 部门2 | 200 |
| 2009-09-20 | 部门3 | 300 |
| 2009-09-19 | 部门2 | 400 |
| 2009-09-19 | 部门4 | 500 |
+------------+-------+----------+
5 rows in set (0.00 sec)mysql> select dept,sum(business) sum_buss
-> from t
-> group by dept,dd
-> order by sum_buss desc
-> limit 1;
+-------+----------+
| dept | sum_buss |
+-------+----------+
| 部门4 | 500 |
+-------+----------+
1 row in set (0.00 sec)
select dept,sum(business) sum_buss from tt group by dept,dd order by sum_buss desc limit 1;
2、
select a.日期,a.部门,a.营业额 from tt a
left join tt b on a.日期=b.日期 and a.营业额<=b.营业额
group by a.日期,a.部门,a.营业额 having count(b.日期)=13
select a.* from tt a inner join (select 日期,max(营业额) as ma from tt group by 日期) b
on a.日期=b.日期 and a.营业额=b.ma
mysql> select * from t t1
-> inner join
-> (select dd,business from t t2 where not exists
-> (select 1 from t where business>t2.business and dd=t2.dd)
-> ) t3
-> on t1.dd=t3.dd and t1.business=t3.business;
+------------+-------+----------+------------+----------+
| dd | dept | business | dd | business |
+------------+-------+----------+------------+----------+
| 2009-09-20 | 部门3 | 300 | 2009-09-20 | 300 |
| 2009-09-19 | 部门4 | 500 | 2009-09-19 | 500 |
+------------+-------+----------+------------+----------+
2 rows in set (0.00 sec)
大部分人都能做到这一步 关键是怎么查找对应的部门
left join tt b on a.日期=b.日期 and a.营业额 <=b.营业额
group by a.日期,a.部门,a.营业额 having count(b.日期)=1 select a.* from tt a inner join (select 日期,max(营业额) as ma from tt group by 日期) b
on a.日期=b.日期 and a.营业额=b.ma
是比较通用的方法
+------------+-------+-----+
| dtm | dept | biz |
+------------+-------+-----+
| 2009-09-20 | 部门1 | 100 |
| 2009-09-20 | 部门2 | 200 |
| 2009-09-20 | 部门3 | 300 |
| 2009-09-19 | 部门2 | 400 |
| 2009-09-19 | 部门4 | 500 |
+------------+-------+-----+
5 rows in set (0.03 sec)mysql> select * from t_caibird1984 where concat(dtm,biz) in (select concat(dtm,m
ax(biz)) from t_caibird1984 group by dtm);
+------------+-------+-----+
| dtm | dept | biz |
+------------+-------+-----+
| 2009-09-20 | 部门3 | 300 |
| 2009-09-19 | 部门4 | 500 |
+------------+-------+-----+
2 rows in set (0.00 sec)
where a.营业额=
(select 营业额 from tt order by 营业额 limit 1
where a.日期=日期)
09-19 部门4 500
09-20 部门3 300用楼主数据得到的结果
用
select a.日期,a.部门,a.营业额 from tt a
left join tt b on a.日期=b.日期 and a.营业额 <=b.营业额
group by a.日期,a.部门,a.营业额 having count(b.日期)=1
,你认为正确结果是什么?
WHERE a.营业额=
(SELECT 营业额 FROM tyq WHERE a.日期=日期 ORDER BY 营业额 DESC LIMIT 1)
这种方法也可以,不过要求营业额没有重复
(SELECT 日期 r,MAX(营业额) Y FROM a GROUP BY 日期) a,
(SELECT 日期 r,部门 b,MAX(营业额) Y FROM a GROUP BY 日期,部门) b
WHERE a.r=b.r
AND a.y=b.y;
这个sql语句即使部门有重复的 或者营业额有重复的都可以实现
mysql> select * from t;
+------------+-------+--------+
| dd | dept | income |
+------------+-------+--------+
| 2009-09-20 | 部门1 | 100 |
| 2009-09-20 | 部门2 | 200 |
| 2009-09-20 | 部门1 | 300 |
| 2009-09-20 | 部门2 | 500 |
| 2009-09-19 | 部门1 | 300 |
| 2009-09-19 | 部门2 | 200 |
| 2009-09-19 | 部门1 | 200 |
+------------+-------+--------+
7 rows in set (0.00 sec)mysql> select * from
-> (select dd,dept,sum(income) sum_income
-> from t group by dd,dept)t1
-> where not exists
-> (select 1 from
-> (select dd,sum(income) sum_income
-> from t group by dd,dept)t2
-> where t2.dd=t1.dd and t2.sum_income>t1.sum_income)
-> ;
+------------+-------+------------+
| dd | dept | sum_income |
+------------+-------+------------+
| 2009-09-19 | 部门1 | 500 |
| 2009-09-20 | 部门2 | 700 |
+------------+-------+------------+
2 rows in set (0.02 sec)
+------------+-----------+--------+
| date | deparment | income |
+------------+-----------+--------+
| 2009-09-20 | 1 | 100 |
| 2009-09-20 | 2 | 200 |
| 2009-09-20 | 2 | 300 |
| 2009-09-19 | 1 | 400 |
| 2009-09-19 | 2 | 200 |
| 2009-09-19 | 3 | 500 |
| 2009-09-19 | 4 | 500 |
+------------+-----------+--------+
select A.* from income as A,(select date,max(income) as max from income group by date) as B where A.date=B.date and A.income=B.max;orselect A.* from income as A,(select date,max(income) as max from income group by date) as B where A.date=B.date and A.income=B.max;