mysql - 我的sql，迭代限制语句中的变量

Question

我在 c9.io 上使用 mysql，我想在限制语句中检查多个变量我本质上想要的是

While (x<count(SELECT DISTINCT category FROM Products){

SELECT * FROM Products WHERE category=(SELECT DISTINCT category FROM Products LIMIT x, 1) LIMIT 6;

}

当我将 x 设置为 0-3 之间的任何数字时，while 循环内的查询工作正常。前任。mysql> SELECT * FROM 产品 WHERE 类别=(SELECT DISTINCT 类别 FROM

Products LIMIT 2, 1) LIMIT 6;                                                
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------------------+
| imageLocation                        | productName                        | manufacturer | price  | availability | category                  |
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------------------+
| ./images/Strawberry_Pi_Zero.jpg      |  Strawberry Pi Extension Kit AU252 | Gorella      | 194.99 |            1 | Strawberry Pi Accessories |
| ./images/Strawberry_Pi_2_Model_B.jpg |  Compute Module Kit GP664          | Corona       |  16.99 |            1 | Strawberry Pi Accessories |
| ./images/Strawberry_Pi_3_Model_B.jpg |  Camera Module CL638               | Apollo       | 256.99 |            7 | Strawberry Pi Accessories |
| ./images/Strawberry_Pi_Case.jpg      |  Strawberry Pi LG178               | Tufts        |  26.99 |           10 | Strawberry Pi Accessories |
| ./images/Sense_Hat.jpg               |  Strawberry Pi OW299               | Darstar      |  35.99 |            4 | Strawberry Pi Accessories |
| ./images/Compute_Module.jpg          |  Compute Module Kit QR216          | Confitura    |  41.99 |            6 | Strawberry Pi Accessories |
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------------------+
6 rows in set (0.00 sec)

我希望查询在单个查询中当 x=0、x=1、x=2 和 x=3 时选择 6 条记录。

我尝试过的 while 循环是

delimiter // CREATE PROCEDURE wholeblock:while_loop() BEGIN DECLARE x INT; SET x = 0; WHILE x < COUNT(SELECT DISTINCT category FROM Products) DO SELECT * FROM Products WHERE category=(SELECT DISTINCT category FROM Products LIMIT x, 1) LIMIT 6; SET x = x + 1; END WHILE; END//

什么也不返回。

..

..

..

编辑

我想要 1 个查询的结果输出此输出。没有 4 个查询

mysql> SELECT * FROM Products WHERE category=(SELECT DISTINCT category FROM Products LIMIT 0, 1) LIMIT 6;+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------+
| imageLocation                        | productName                        | manufacturer | price  | availability | category      |
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------+
| ./images/Strawberry_Pi_3_Model_B.jpg | Camera Module XF181                | Toro         |  35.99 |           10 | Strawberry Pi |
| ./images/Camera_Module_V2.jpg        |  Compute Module Kit GX416          | Belrubi      |  98.99 |            1 | Strawberry Pi |
| ./images/Sense_Hat.jpg               |  Strawberry Pi Extension Kit JJ556 | Toro         |  92.99 |            1 | Strawberry Pi |
| ./images/Strawberry_Pi_Zero.jpg      |  Camera Module FI378               | Belrubi      |  44.99 |            5 | Strawberry Pi |
| ./images/Compute_Module.jpg          |  Compute Module Kit HP564          | Elsanta      | 239.99 |            5 | Strawberry Pi |
| ./images/Strawberry_Pi_1_Model_A.jpg |  Compute Module UZ736              | Revada       |  24.99 |           10 | Strawberry Pi |
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM Products WHERE category=(SELECT DISTINCT category FROM Products LIMIT 1, 1) LIMIT 6;                                                
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------+
| imageLocation                        | productName                        | manufacturer | price  | availability | category      |
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------+
| ./images/Strawberry_Pi_3_Model_B.jpg |  Strawberry Pi Extension Kit SG218 | Apollo       |  22.99 |            4 | Popular Items |
| ./images/Strawberry_Pi_2_Model_B.jpg |  Strawberry Pi Case ZM942          | Corona       | 182.99 |            7 | Popular Items |
| ./images/placeholder.png             |  Compute Module VO511              | Darstar      | 188.99 |            3 | Popular Items |
| ./images/Strawberry_Pi_2_Model_B.jpg |  Strawberry Pi DB112               | Tufts        |  79.99 |            1 | Popular Items |
| ./images/Compute_Model_Kit.jpg       |  Compute Module DX828              | Aliso        |  83.99 |            3 | Popular Items |
| ./images/Strawberry_Pi_Zero.jpg      |  Camera Module SZ841               | Glasso       | 115.99 |            6 | Popular Items |
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM Products WHERE category=(SELECT DISTINCT category FROM Products LIMIT 2, 1) LIMIT 6;                                                
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------------------+
| imageLocation                        | productName                        | manufacturer | price  | availability | category                  |
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------------------+
| ./images/Strawberry_Pi_Zero.jpg      |  Strawberry Pi Extension Kit AU252 | Gorella      | 194.99 |            1 | Strawberry Pi Accessories |
| ./images/Strawberry_Pi_2_Model_B.jpg |  Compute Module Kit GP664          | Corona       |  16.99 |            1 | Strawberry Pi Accessories |
| ./images/Strawberry_Pi_3_Model_B.jpg |  Camera Module CL638               | Apollo       | 256.99 |            7 | Strawberry Pi Accessories |
| ./images/Strawberry_Pi_Case.jpg      |  Strawberry Pi LG178               | Tufts        |  26.99 |           10 | Strawberry Pi Accessories |
| ./images/Sense_Hat.jpg               |  Strawberry Pi OW299               | Darstar      |  35.99 |            4 | Strawberry Pi Accessories |
| ./images/Compute_Module.jpg          |  Compute Module Kit QR216          | Confitura    |  41.99 |            6 | Strawberry Pi Accessories |
+--------------------------------------+------------------------------------+--------------+--------+--------------+---------------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM Products WHERE category=(SELECT DISTINCT category FROM Products LIMIT 3, 1) LIMIT 6;                                                
+--------------------------------------+---------------------------+--------------+--------+--------------+--------------------------+
| imageLocation                        | productName               | manufacturer | price  | availability | category                 |
+--------------------------------------+---------------------------+--------------+--------+--------------+--------------------------+
| ./images/Strawberry_Pi_Zero.jpg      |  Strawberry Pi DS812      | Tufts        | 172.99 |            6 | Industrial Compute Model |
| ./images/Strawberry_Pi_Case.jpg      |  Compute Module Kit CL851 | Confitura    | 219.99 |            5 | Industrial Compute Model |
| ./images/placeholder.png             |  Compute Module Kit CM175 | Tago         | 298.99 |            3 | Industrial Compute Model |
| ./images/Strawberry_Pi_3_Model_B.jpg |  Camera Module MY484      | Tago         |  33.99 |            8 | Industrial Compute Model |
| ./images/Sense_Hat.jpg               |  Compute Module Kit EB177 | Glasso       |  66.99 |            5 | Industrial Compute Model |
| ./images/Strawberry_Pi_3_Model_B.jpg |  Compute Module PW477     | Elsanta      |  29.99 |            5 | Industrial Compute Model |
+--------------------------------------+---------------------------+--------------+--------+--------------+--------------------------+
6 rows in set (0.01 sec)

Answer 1

您尝试构建的查询类型称为“每组 top-n”，通常在其他 RDMBS 中通过使用 ROW_NUMBER 窗口函数有效完成，但遗憾的是 MySQL 中仍然缺少该函数。好消息是 MySQL 中的 session 变量可以用来模拟它。

SELECT *
  FROM (
    SELECT p.*,        
           @i := IF(@p = p.category, @i + 1, 1) rn, -- increment a row number if it's still the same category
           @p = p.category -- preserve the category
      FROM products p JOIN (
        SELECT DISTINCT category
          FROM products
         ORDER BY category -- always explicitly set an order when using limit
         LIMIT 4
      ) c ON p.category = c.category CROSS JOIN (
        SELECT @i := 0, @p := NULL -- Initialize vars in-line
      ) i
     ORDER BY p.category, id -- order by category first, then by something else i.e. id, productname
) q
 WHERE rn < 7 -- return only top 6 per group (category in your case)