MySQL - 选择计数大于 1 的数据

Question

我有下表:

+-------------------------------------+----------------------------------------------------------------+
| keyword                             | landing_page                                                   |
+-------------------------------------+----------------------------------------------------------------+
| orange                              | https://www.example.co.uk/                               |
| apple                               | https://www.example.co.uk/                               |
| pear                                | https://www.example.co.uk/                               |
| apple                               | https://www.example.co.uk/                               |
| apple                               | https://www.example.co.uk/landing-page |
+-------------------------------------+----------------------------------------------------------------+

我想选择任何具有多个不同着陆页的关键字，因此在本例中，我们将返回:

apple, https://www.example.co.uk
apple, https://www.example.co.uk/landing-page

如何使用 MySQL 实现此目的？

更新:我尝试了以下方法，但似乎没有用:

select keyword, count(landing_page) 
from search_data 
group by keyword 
having count(distinct landing_page) > 1;

Answer 1

您尝试的查询是解决方案的一部分。使用该查询作为内联 View ，以识别具有多个着陆页的关键字。将该查询的结果连接回原始表。

SELECT t.keyword
     , t.landing_page
 FROM ( -- keyword with more than one landing page 
        SELECT r.keyword
          FROM search_data r 
         GROUP BY r.keyword
        HAVING COUNT(DISTINCT r.landing_page) > 1
      ) s
 JOIN search_data t
   ON t.keyword = s.keyword
GROUP BY t.keyword, t.landing_page
ORDER BY t.keyword, t.landing_page

这不是唯一的方法。还有其他查询模式也将返回等效结果。作为另一种方法的示例，使用相关子查询来检查表中是否存在具有相同关键字但不同 landing_page 的另一行:

SELECT DISTINCT t.keyword, t.landing_page
  FROM search_data t
 WHERE EXISTS ( SELECT 1
                  FROM search_data r
                 WHERE r.keyword = t.keyword
                   AND NOT ( r.landing_page <=> t.landing_page )
              )
 ORDER BY t.keyword, t.landing_page

跟进

演示设置:

CREATE TABLE search_data (keyword VARCHAR(10), landing_page VARCHAR(80))
;
CREATE INDEX search_data_IX1 ON search_data (keyword, landing_page)
;
INSERT INTO search_data (keyword, landing_page) VALUES
 ('orange','https://www.example.co.uk/')
,('apple','https://www.example.co.uk/')
,('pear','https://www.example.co.uk/')
,('apple','https://www.example.co.uk/')
,('apple','https://www.example.co.uk/landing-page')
;

解释查询 1

EXPLAIN    
SELECT t.keyword
     , t.landing_page
 FROM ( -- keyword with more than one landing page 
        SELECT r.keyword
          FROM search_data r 
         GROUP BY r.keyword
        HAVING COUNT(DISTINCT r.landing_page) > 1
      ) s
 JOIN search_data t
   ON t.keyword = s.keyword
GROUP BY t.keyword, t.landing_page
ORDER BY t.keyword, t.landing_page

--     id  select_type  table       type    possible_keys    key              key_len  ref       rows  Extra
-- ------  -----------  ----------  ------  ---------------  ---------------  -------  ------  ------  ------------------------
--      1  PRIMARY      <derived2>  system  (NULL)           (NULL)           (NULL)   (NULL)       1  
--      1  PRIMARY      t           ref     search_data_IX1  search_data_IX1  13       const        2  Using where; Using index
--      2  DERIVED      r           index   (NULL)           search_data_IX1  96       (NULL)       5  Using index

执行查询 1

SELECT t.keyword
     , t.landing_page
 FROM ( -- keyword with more than one landing page 
        SELECT r.keyword
          FROM search_data r 
         GROUP BY r.keyword
        HAVING COUNT(DISTINCT r.landing_page) > 1
      ) s
 JOIN search_data t
   ON t.keyword = s.keyword
GROUP BY t.keyword, t.landing_page
ORDER BY t.keyword, t.landing_page

-- keyword  landing_page
-- -------  --------------------------------------
-- apple    https://www.example.co.uk/
-- apple    https://www.example.co.uk/landing-page

解释查询 2

EXPLAIN
SELECT DISTINCT t.keyword, t.landing_page
  FROM search_data t
 WHERE EXISTS ( SELECT 1
                  FROM search_data r
                 WHERE r.keyword = t.keyword
                   AND NOT ( r.landing_page <=> t.landing_page )
              )
 ORDER BY t.keyword, t.landing_page

--     id  select_type         table   type    possible_keys    key              key_len  ref               rows  Extra
-- ------  ------------------  ------  ------  ---------------  ---------------  -------  --------------  ------  -------------------------------------
--      1  PRIMARY             t       range   (NULL)           search_data_IX1  96       (NULL)               6  Using where; Using index for group-by
--      2  DEPENDENT SUBQUERY  r       ref     search_data_IX1  search_data_IX1  13       test.t.keyword       1  Using where; Using index

执行查询 2

SELECT DISTINCT t.keyword, t.landing_page
  FROM search_data t
 WHERE EXISTS ( SELECT 1
                  FROM search_data r
                 WHERE r.keyword = t.keyword
                   AND NOT ( r.landing_page <=> t.landing_page )
              )
 ORDER BY t.keyword, t.landing_page

-- keyword  landing_page
-- -------  --------------------------------------
-- apple    https://www.example.co.uk/
-- apple    https://www.example.co.uk/landing-page