MySQL题目及解答

最新推荐文章于 2024-05-31 10:33:21 发布

原创最新推荐文章于 2024-05-31 10:33:21 发布 · 429 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

5 篇文章

订阅专栏

本文通过四个具体的MySQL查询实例，详细解析了如何处理包括找出票数最多候选人的选举结果、获取物品历史价格、计算部门与公司平均工资对比以及查询产品销售首年详情等问题。这些例子展示了SQL在数据处理中的强大功能和灵活性。

1、有如下两张表Candidate(候选人）和vote(选票表），CandidateID是表Candidate表的id，查询出当选者的名字，票数最多的为当选者，有且只有一位当选者

先使用子查询查询出票数最多的候选者id ，在通过关联查询查询出id得出候选者名字，由于有且只有一位候选者使用了limit

SELECT a.`NAME`
FROM
(SELECT CandidateID,COUNT(CandidateID)
FROM vote
GROUP BY CandidateID
LIMIT 1)as b
LEFT JOIN candidate a on a.ID=b.CandidateID

2、有一个表T2(如下图）依次得出最新单价，上次单价，上上次单价，若不存在则用0替代

SELEC a.wuping AS '物品',
a.danjia AS '最新单价',
b.danjia AS '上一次价格',
c.danjia AS '上上次'
FROM
(
SELECT *
from t2 t1
WHERE not EXISTS
(SELECT 1 FROM t2 WHERE t2.wuping=t1.wuping AND t2.riqi>t1.riqi))AS a
inner JOIN t2 as b ON a.wuping=b.wuping AND a.riqi>b.riqi
inner JOIN t2 as c ON a.wuping=c.wuping AND b.riqi>c.riqi
UNION
SELECT a.wuping AS '物品',
a.danjia AS '最新单价',
b.danjia AS '上一次价格',
0
FROM
(
SELECT *
from t2 t1
WHERE not EXISTS
(SELECT 1 FROM t2 WHERE t2.wuping=t1.wuping AND t2.riqi>t1.riqi))AS a
inner JOIN t2 as b ON a.wuping=b.wuping AND a.riqi>b.riqi AND b.wuping='B'

3、有如下两个表salary 和exployee1 ，其中salary 表中的employee_id 是employee1表的外键，查询出每月工资发放，每个部门的平均工资与公司平均工资的比较结果（高/低/相同）

SELECT dep_avg.pay_date as pay_month,
       dep_avg.department_id,
     -- dep_amout,avg_amout
       case
           WHEN dep_amout >avg_amout then '高'
           WHEN dep_amout =avg_amout then '相等'
           ELSE '低'
       end as comparison
FROM
-- 部门每个月平均工资
(SELECT DATE_FORMAT(pay_date,'%Y-%m')as pay_date,
        employee1.department_id,
        avg(amount) as dep_amout
FROM salary
JOIN employee1 ON salary.employee_id= employee1.employee_id
GROUP BY DATE_FORMAT(pay_date,'%Y-%m'),employee1.department_id) dep_avg
LEFT JOIN
-- 整个公司每个月平均工资
(SELECT DATE_FORMAT(pay_date,'%Y-%m') as pay_date,
         AVG(amount) as avg_amout
FROM salary
GROUP BY DATE_FORMAT(pay_date,'%Y-%m')) all_avg
ON dep_avg.pay_date =all_avg.pay_date
ORDER BY dep_avg.pay_date,dep_avg.department_id

4、有表sales_table 和表product_table ,查询每种销售产品第一年的id，年份，数量，价格，名称。

SELECT a.product_id,MIN(`year`) AS first_year,quantity,price,b.product_name
FROM sales_table a
LEFT JOIN product_table b ON a.product_id=b.product_id
GROUP BY a.product_id