mysql - SQL 查询分配

Question

1) 查询返回 3 月份销售额大于或等于 20 的所有代理商，以及 3 月份销售额大于或等于 20 的总和以及 3 月份销售额大于或等于 20 的数量。结果排序降序。冒号:名称、金额、计数

2) 查询返回 3 月份没有一笔销售额大于 20 的所有代理商。冒号:名称

数据库:

DB销售

ID ____ IDAgent _____ 日期 ____ 金额

1 _______ 1 ______ 01-03-16 _____ 15

2 _______ 2 ______ 01-03-16 _____ 25

3 _______ 1 ______ 01-03-16 _____ 17

4 _______ 3 ______ 04-03-16 _____ 55

5 _______ 4 ______ 04-03-16 _____ 12

6 _______ 5 ______ 05-03-16 _____ 13

7 _______ 5 ______ 06-03-16 _____ 11

8 _______ 4 ______ 11-03-16 _____ 20

9 _______ 4 ______ 12-03-16 _____ 21

10 ______ 3 ______ 15-03-16 _____ 45

11 ______ 2 ______ 17-03-16 _____ 45

12 ______ 1 ______ 19-03-16 _____ 11

13 ______ 1 ______ 25-03-16 _____ 19

14 ______ 1 ______ 01-04-13 _____ 25

15 ______ 1 ______ 04-04-16 _____ 65

16 ______ 3 ______ 15-04-16 _____ 10

17 ______ 5 ______ 16-04-16 _____ 41

数据库用户

ID ___ 姓名

1 ___ 马克·罗斯伯格

2 ___ 唐·史密斯

3 ___ 安娜·特克斯

4 ___ 安吉拉·里维斯

5 ___ 胡安·多维托索

这些是我的错误尝试:

1)

SELECT u.name SUM(s.amount) AS amount, COUNT(s.amount) AS count
FROM dbuser AS u JOIN dbsale AS s
ON u.id = s.idagent
ORDER BY s.amount ASC
HAVING s.amount >= 20 AND MONTH(s.date)='3';

2)

SELECT DISTINCT u.name
FROM dbuser AS u INNER JOIN dbsale AS s
ON u.id = s.idagent
WHERE s.amount < 20 AND MONTH(s.date)='3' ;

Answer 1

您的查询 1 将是 -

SELECT 
A.`name`, 
SUM(B.amount) AS amount, 
COUNT(B.amount) AS count 
FROM DBUser A
INNER JOIN DBSale B ON A.ID = B.IDAgent
WHERE MONTH(CAST(B.date AS DATE))=3
AND B.amount >= 20
GROUP BY A.`name`

查询 2 将是 -

SELECT A.`name`
FROM DBUser A
INNER JOIN DBSale B 
ON A.ID = B.IDAgent
WHERE MONTH(CAST(B.date AS DATE))=3
GROUP BY A.`name`,A.ID
HAVING SUM(CASE WHEN B.Amount > 20 THEN 1 ELSE 0 END) > 0

根据您的要求添加订单。