mysql数据计算业绩_mysql 经典业绩查询语句面试题

一个很经典的mysql的查询语句面试题

t1

t2：

问：请写出SQL语句得到两个月的业绩，期望得到以下结果，请问SQL语句如何写？？

结果图：

SQL：select c.dname, a.yj '一月份',b.yj '二月份' from t1 a INNER JOIN t1 b on a.dep = b.dep INNER JOIN t2 c on a.dep = c.dep where a.mon='一月份' and b.mon='二月份'

题2：

SELECT

a.`year` as `year`,

a.amount AS m1,

b.amount AS m2,

c.amount AS m3,

d.amount AS m4

FROM

`year` AS a

INNER JOIN `year` b ON b.year = a.year

INNER JOIN `year` AS c ON c. year = a. year

INNER JOIN `year` d ON d. year = a. year

WHERE

a.`month` = 1

AND b.`month` = 2

AND c.`month` = 3

AND d.`month` = 4

3. 根据下面数据库，写出 挂科两门以及两门以上的学员平均分。

4.  ID  主键    CAT_id 栏目ID  good_name 商品名

把每个栏目下ID最大的商品取出来？

select * from ( select * from good order by cat_id,id desc) as tmp GROUP by cat_id;