销售部员工小钱要对本公司2023年销售情况进行分析统计,需要作以下查询: ① 销售部男女员工人数及总人数; ② 2023年销售部各员工的销售业绩,并以降序排序,标题包括:姓名、性别、销售金额; ③ 2023年销售最好的10种商品(以销售金额为准),标题包括:商品名称、价格、数量、销售金额。
时间: 2024-01-12 13:05:00 浏览: 59
针对您的问题,您可以使用以下的SQL语句来进行查询:
① 销售部男女员工人数及总人数:
```
-- 销售部总人数
SELECT COUNT(*) AS 总人数 FROM employee WHERE departmentid = 1;
-- 销售部男女员工人数
SELECT sex AS 性别, COUNT(*) AS 人数 FROM employee WHERE departmentid = 1 GROUP BY sex;
```
② 2023年销售部各员工的销售业绩,并以降序排序,标题包括:姓名、性别、销售金额:
```
SELECT employee.employeename AS 姓名, employee.sex AS 性别, SUM(sale.amount) AS 销售金额
FROM employee
JOIN sale ON employee.employeeid = sale.employeeid
WHERE employee.departmentid = 1 AND YEAR(sale.date) = 2023
GROUP BY employee.employeeid
ORDER BY 销售金额 DESC;
```
③ 2023年销售最好的10种商品(以销售金额为准),标题包括:商品名称、价格、数量、销售金额:
```
SELECT product.productname AS 商品名称, product.price AS 价格, SUM(sale.quantity) AS 数量, SUM(sale.amount) AS 销售金额
FROM sale
JOIN product ON sale.productid = product.productid
WHERE YEAR(sale.date) = 2023
GROUP BY sale.productid
ORDER BY 销售金额 DESC
LIMIT 10;
```
请确保表名、字段名、数据类型、数据值的正确性并根据实际情况修改。
阅读全文