mysql - 程序限制

Question

我需要帮助!我尝试寻找答案，但一无所获。

我正在使用 MYSQL6。

我所做的是创建 2 个 View ，我想限制这些 View 以获得通过函数计算的特定数量的结果。

功能:

DELIMITER $$

    CREATE FUNCTION `get_total_customers`() RETURNS int(11)
BEGIN

DECLARE TotalCustomers INT;

SELECT (count(customer_id)*0.2) INTO TotalCustomers FROM customer;

RETURN TotalCustomers;
END

---

View 1:

    CREATE VIEW PreferredStatus AS 
SELECT customer_id, SUM(amount) AS TotalAmount, get_total_customers() AS Total FROM payment
GROUP BY customer_id 
ORDER BY TotalAmount DESC;

---

View 2:

    CREATE VIEW RegularStatus AS
SELECT customer_id, SUM(amount) AS TotalAmount, get_total_customers() AS Total FROM payment
GROUP BY customer_id 
ORDER BY TotalAmount ASC
LIMIT Total;

这是使用 View 的过程。

 > DELIMITER $$

CREATE PROCEDURE `CustomerStatus`(in custID int, out status varchar(200))
BEGIN

declare number1 int;
declare totalvar int;
declare limit2 int;

IF custID in (SELECT customer_id FROM PreferredStatus) THEN 
    set status = "preferred";

ELSEIF custID in (SELECT customer_id FROM RegularStatus) THEN  
    set status = "casual";

ELSE
    set status = "regular";

End If; 
END

---

请帮忙!

-谢谢。

Answer 1

您可以将 LIMIT 添加到创建 VIEW 的 SELECT:

CREATE VIEW PreferredStatus AS 
SELECT customer_id, 
       SUM(amount) AS TotalAmount, 
       get_total_customers() AS Total 
FROM payment
GROUP BY customer_id 
ORDER BY TotalAmount DESC
LIMIT 20;

sqlfiddle demo

来自 the docs 的警告:

If a view definition includes a LIMIT clause, and you select from the view using a statement that has its own LIMIT clause, it is undefined which limit applies.