sql查询，根据计数和一些过滤器查找最大值

ccgok5k5 于 2021-06-25 发布在 Mysql

关注(0)|答案(3)|浏览(307)

表如下所示：

/* Create a table called test */
CREATE TABLE test(Id integer PRIMARY KEY,Col_Key text, Ng text, node text);

/* Create few records in this table */
INSERT INTO test VALUES(1,'key1','g1','n2');
INSERT INTO test VALUES(2,'key1','g2','n3');
INSERT INTO test VALUES(3,'key2','g3','n1');
INSERT INTO test VALUES(4,'key2','g4','n1');
INSERT INTO test VALUES(5,'key3','g5','n1');
INSERT INTO test VALUES(6,'key3','g6','n2');
INSERT INTO test VALUES(7,'key4','g7','n1');
INSERT INTO test VALUES(8,'key4','g8','n1');
INSERT INTO test VALUES(9,'key5','g8','n4');
INSERT INTO test VALUES(10,'key5','g9','n4');
INSERT INTO test VALUES(11,'key6','g10','n4');
INSERT INTO test VALUES(12,'key6','g11','n4');
INSERT INTO test VALUES(13,'key7','g11','n4');
INSERT INTO test VALUES(14,'key9','q11','n3');
INSERT INTO test VALUES(15,'key9','q11','n2');
INSERT INTO test VALUES(16,'key10','q12','n1');
COMMIT;

我试图从node列中获取值，该列具有最大计数，并且其ng值以g开头。
我做过这样的事：

SELECT TEMP.node,COUNT(TEMP.node) FROM (SELECT Col_Key,Ng,node 
from test WHERE (Ng LIKE 'g%')) TEMP GROUP BY TEMP.node;

结果如下：

但是，在结果中，我只希望结果中的n4和n1（仅节点列不计数），因为它们具有最大计数。我无法在查询中添加此部分。请帮忙。
上面的数据只是一小部分数据，但是我的sql表中有数千行，所以我的查询应该是高效的。
ps:-我试着做了以下工作：

SELECT TEMP2.node,TEMP2.CNT FROM (SELECT TEMP.node,COUNT(TEMP.node) AS CNT FROM (SELECT Col_Key,Ng,node 
from test WHERE (Ng LIKE 'g%')) TEMP GROUP BY TEMP.node) TEMP2 WHERE TEMP2.CNT = (SELECT MAX(TEMP2.CNT) FROM TEMP2);

但是查询的最后一部分where子句是错误的，因为它找不到表temp2。
但这是我最想知道的。
结果应为：

node 
n1
n4

sql mysql group-by count

来源：https://stackoverflow.com/questions/49804352/sql-query-to-find-max-values-based-upon-count-and-some-filters

3条答案

按热度按时间

3phpmpom1#

比如说：

SELECT
  t.node,
  COUNT(*)
FROM
  test t
WHERE
  t.Ng like 'g%'
GROUP BY
  t.node
HAVING
  COUNT(*) = (
    SELECT 
      MAX(a.count_of_nodes)
    FROM
    (
      SELECT
        node,
        COUNT(*) AS count_of_nodes
      FROM
        test
      WHERE
        Ng like 'g%'
      GROUP BY
        node
    ) a
  )
;

祝你好运！

赞(0）回复(0）举报 2021-06-26

zujrkrfu2#

您的查询不需要子查询：

select node, count(*) 
from test
where Ng LIKE 'g%'
group by node;

获得一个最大值很容易（使用 limit )，但你想要所有的。一种方法使用子查询和 having 条款：

select node, count(*) 
from test
where Ng LIKE 'g%'
group by node
having count(*) = (select count(*)
                   from test t2
                   where t2.Ng LIKE 'g%'
                   group by t2.node
                   order by count(*) desc
                   limit 1
                  );

这比需要的要复杂，因为mysql（8.0之前）既不支持窗口函数，也不支持cte。幸运的是，mysql 8+的语法更简单。

赞(0）回复(0）举报 2021-06-26

tjrkku2a3#

您可以使用一个诱人的方法轻松地处理这种情况，如下所示：

CREATE TEMPORARY TABLE temptable AS (
  SELECT TEMP.node,COUNT(TEMP.node) as cnt FROM (SELECT Col_Key,Ng,node 
  from test WHERE (Ng LIKE 'g%')) TEMP GROUP BY TEMP.node
)
select node, cnt
from temptable t
where cnt = (select max(cnt) from temptable)

请参见：http://www.sqlfiddle.com/#!9/e749de/1/0型

赞(0）回复(0）举报 2021-06-26

我来回答

sql查询，根据计数和一些过滤器查找最大值

3条答案

相关问题

热门标签

最新问答