MySQL 存储过程 SELECT、三个 WHERE CLAUSE 值和 ORDER BY

Question

我的存储函数似乎无法正常工作，我返回了 10 行 NULL 和 40 条警告。

我想编写一个存储函数，返回开始日期 D1 和结束日期 D2 之间 customer_id X 的最高支付金额。

表格结构如下:

sales(customer_id CHAR(1), date DATE, paid INT)

示例行:

sales(A, 2014-12-20, 3456)

我的存储函数:

DELIMITER $$
    CREATE FUNCTION highest_monthly_paid(x CHAR(1), D1 DATE, D2 DATE) 
      RETURNS INT
      DETERMINISTIC
    BEGIN
        DECLARE highest_fee INT;

        SELECT paid INTO highest_fee
        FROM sales
        WHERE customer_id = x 
        AND date > D1
        AND date < D2
        ORDER BY paid DESC LIMIT 1;
        RETURN highest_fee;
    END
    $$

这行不通还是我最好使用某种子查询？如果这两种方法都有效，我应该使用哪种？

我试过 MAX(paid) 而不是 LIMIT。

我调用函数的查询:

SELECT highest_monthly_paid(A,  2013-12-10,  2013-12-25)
FROM sales;

数据库中有两行customer_id为A:

+-------------+-------+----------+------------+--------+
| customer_id | model | quantity | day        | paid   |
+-------------+-------+----------+------------+--------+
| A           | 2002  |        2 | 2013-12-19 |   1898 |
| A           | 3002  |        1 | 2013-12-18 |    239 |
+-------------+-------+----------+------------+--------+

我的结果:

mysql> SELECT highest_monthly_paid(A,  2013-12-10,  2013-12-25)
    -> FROM sales;
+------------------------------------------------------------+
| highest_monthly_paid(A,  2013-12-10,  2013-12-25) |
+------------------------------------------------------------+
|                                                       NULL |
|                                                       NULL |
|                                                       NULL |
|                                                       NULL |
|                                                       NULL |
|                                                       NULL |
|                                                       NULL |
|                                                       NULL |
|                                                       NULL |
|                                                       NULL |
+------------------------------------------------------------+
10 rows in set, 30 warnings (0.00 sec)

Answer 1

我认为以下查询应该足以获取每个客户在给定日期范围内的 highest_fee。如果您有其他要求，请告诉我们。

SELECT customer_id, MAX(paid) AS highest_fees
FROM sales
WHERE date BETWEEN '2013-12-10' AND '2013-12-25'
GROUP BY customer_id