假设有一个门户网站，在一个页面上显示10个产品。我们的目标是在首页显示最多3个随机选择的“is_featured”产品，以及其他产品（如果存在）。如果没有，仅显示10个常规产品。查询应该包含LIMIT和OFFSET，因为有分页。浏览门户时不应跳过任何行，也不应重复任何行。
第一页上的结果可能与此类似（前3行是“is_featured”，并且是任意选择的），其余的是按item_id排序的。

item_id | author              | is_featured
129     | Bohumil Peterka     | 1
102     | Aneta Šebková       | 1
150     | Jakub Šustr         | 1
100     | Richard Kovář       | 0
101     | Růžena Staňková     | 0
103     | Lubomír Hladík      | 0
104     | Ing. Dalibor Lang   | 0
105     | Miloš Formánek      | 0
106     | Michal Hlaváček     | 0
107     | Jarmila Seidlová    | 0

然后，第二页将继续item_id 108…
目前为止我所尝试的

(
  SELECT *
  FROM item
  WHERE is_featured = 1
    ORDER BY RAND()
  LIMIT 3 
)
UNION ALL
(
  SELECT *
  FROM item
  ORDER BY item_id
  LIMIT 7 
  OFFSET 0
)

我的查询存在多个问题
1.结果集有时会返回重复项，我想我可以使用selectdistict/temporary table来解决这个问题
1.如果没有“is_featured”项，则结果集将仅返回7行而不是10行
创建表

CREATE TABLE `item` (
    `item_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `author` TEXT NULL DEFAULT NULL,
    `is_featured` TINYINT(3) UNSIGNED NULL DEFAULT '1'
);

样本数据

INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (100, 'Richard Kovář', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (101, 'Růžena Staňková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (102, 'Aneta Šebková', 1);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (103, 'Lubomír Hladík', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (104, 'Ing. Dalibor Lang', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (105, 'Miloš Formánek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (106, 'Michal Hlaváček', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (107, 'Jarmila Seidlová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (108, 'René Sehnal', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (109, 'Jarmila Kvapilová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (110, 'Zdeňka Hanušová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (111, 'Peter Štefek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (112, 'Veronika Pšeničková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (113, 'Ivan Hrabal', 1);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (114, 'Vladimíra Pavlíčková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (115, 'Lenka Dušková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (116, 'Hana Bendová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (117, 'Radim Horváth', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (118, 'Dana Smržová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (119, 'Romana Divišová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (120, 'Ondřej Kropáček', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (121, 'Alena Šebestová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (122, 'Matěj Kurka', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (123, 'Andrea Rambousková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (124, 'Alena Kaňová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (125, 'Vladimír Sládek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (126, 'Šárka Smrčková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (127, 'Jiřina Papežová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (128, 'Radomír Martínek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (129, 'Bohumil Peterka', 1);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (130, 'Bc. Karel Vejvoda', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (131, 'Jiří Hladík', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (132, 'Miluše Holečková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (133, 'Jaromír Mareš', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (134, 'MVDr. Marcela Šafářová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (135, 'Rudolf Duda', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (136, 'Irena Husáková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (137, 'Simona Bednářová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (138, 'MUDr. Peter Landa', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (139, 'Kristýna Hynková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (140, 'Helena Kudrnová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (141, 'Tomáš Hájek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (142, 'Jindřich Ulrich', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (143, 'Vlastimil Sobek', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (144, 'Ivo Lacina', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (145, 'JUDr. Nela Králová', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (146, 'Alena Horká', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (147, 'Dalibor Žižka', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (148, 'Aneta Mráčková', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (149, 'Ondřej Holík', 0);
INSERT INTO `item` (`item_id`, `author`, `is_featured`) VALUES (150, 'Jakub Šustr', 1);

3条答案

按热度按时间

5cg8jx4n1#

您可以尝试使用exist逻辑排除功能重复项（如果存在）：

WITH cte AS (
    SELECT *
    FROM item
    WHERE is_featured = 1
    ORDER BY RAND()
    LIMIT 3 
)

SELECT item_id, author, is_featured
FROM
(
    (SELECT *, 1 AS pos FROM cte)
    UNION ALL
    (
        SELECT *, 2
        FROM item i1
        WHERE NOT EXISTS (
            SELECT 1
            FROM cte i2
            WHERE i2.item_id = i1.item_id
        )
    )
) t
ORDER BY pos, item_id
LIMIT 10;

上面的方法使用了一个计算列pos，它总是将（最多）3个特征记录放在后面的记录之前。如果有少于3个特征记录，则剩余部分将由联合查询的后半部分填充。

赞(0）回复(0）举报 2023-05-28

pkmbmrz72#

对于那些不能使用CTE的人来说，这是Tim的答案的替代解决方案。

CREATE TEMPORARY TABLE item_tmp(item_id INT);
CREATE TEMPORARY TABLE item_tmp2(item_id INT);
INSERT INTO item_tmp (item_id) SELECT item_id from item WHERE is_featured = 1 ORDER BY RAND() LIMIT 3;  
INSERT INTO item_tmp2 (item_id) SELECT item_id from item_tmp;  
    
SELECT item_id, author, is_featured
FROM
(
    (SELECT item.*, 1 AS pos FROM item INNER JOIN item_tmp ON item_tmp.item_id = item.item_id)
    UNION ALL
    (
        SELECT *, 2
        FROM item i1
        WHERE NOT EXISTS (
            SELECT 1
            FROM item_tmp2 i2
            WHERE i2.item_id = i1.item_id
        )
    )
) t
ORDER BY pos, item_id
LIMIT 0,10;
 
DROP TEMPORARY TABLE item_tmp;
DROP TEMPORARY TABLE item_tmp2;

iszxjhcz3#

您可以使用UNION ALL（SELECT * FROM item ORDER BY item_id LIMIT 7 OFFSET 0）代替
UNION ALL（SELECT * FROM item WHERE is_featured = 0 ORDER BY item_id LIMIT 10）
因为这将帮助您设置is_featured = 0并按item_id排序。通过使用LIMIT和ORDERBY，可以确保没有行被扫描，结果保持一致

如何选择N个“特殊”行，如果它们存在，其余的必须用常规行填充，行的总数不能超过MySQL中的X行？

3条答案

相关问题

热门标签

最新问答