MySQL – 选择count大于1的数据

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL – 选择count大于1的数据，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4952字，纯文字阅读大概需要8分钟。

内容图文

我有下表：

+-------------------------------------+----------------------------------------------------------------+
| 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;

解决方法:

您尝试的查询是解决方案的一部分.将该查询用作内联视图,以标识具有多个目标网页的关键字.将该查询的结果连接回原始表.

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')
;

EXPLAIN查询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

内容总结

以上是互联网集市为您收集整理的MySQL – 选择count大于1的数据全部内容，希望文章能够帮你解决MySQL – 选择count大于1的数据所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端