确定mysql中两个字段的唯一组合的计数 [英] Determining count of unique combination of two fields in mysql

问题描述

我有下表:

Company        webdomain
-------        ---------
IBM            ibm.com
IBM            ibm.co.uk
IBM            ibm.in
CSC            csc.com
Infosys        infy.com
Intel          intel.com
Intel          intel.co.in

问题:有多少公司拥有多个域名?

Question: how many companies have more than one webdomain?

如何将其表示为 SQL 查询?

How do I represent this as a SQL query?

我尝试了以下方法:

select count(distinct company, webdomain) 
from table 
where company = 'IBM';

这给出了 IBM 的 Web 域数量为 3，但是，当我想通过以下查询找到所有公司时创建相同的效果时:

This gave the number of web domains for IBM as 3, but however, when I want to create the same effect on finding out all companies with the following query:

select company, count(distinct company, webdomain) 
from table;

我得到一列，它是公司的空值和一些不相关的计数.

I get a single column, which is an empty value on company and some unrelated count.

我知道这会解决问题:

select company, count(distinct company, webdomain) 
from table 
where company in (select distinct company from table);

但是最后一个查询花费的时间太长了.有没有更好的表达方式.

But this last query takes way too long. Is there a better way to put it.

公司，网域组合可能不是唯一的.例如:IBM、ibm.com 的两条记录.

Company, webdomain combination may not be unique. Ex: Two records with IBM, ibm.com.

推荐答案

您可以在查询中附加Group by"以获得所需的结果:-

you can just append "Group by" in your query to get desired result as:-

SELECT 
  `company`, `webdomain`, COUNT(DISTINCT `company`, `webdomain`) 
AS 
  `count_of_unique_combination` FROM `info` 
GROUP BY 
  `company`;

输出是:-

COMPANY     WEBDOMAIN   COUNT OF UNIQUE COMBINATION
CSC         csc.com     1
IBM         ibm.com     3
Infosys     infy.com    1
Intel       intel.com   2 

这篇关于确定mysql中两个字段的唯一组合的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！