这个查询有什么问题? [英] What is wrong with this query?

问题描述

我有一个包含以下字段的表:id |域名 |domain_certificate_no |键值

I have a table with the following fields: id | domainname | domain_certificate_no | keyvalue

select 语句的输出示例如下:

An example for the output of a select statement can be as:

'57092', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_1', '55525772666'
'57093', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_2', '22225554186'
'57094', '02a1fae.netsolstores.com', '02a1fae.netsolstores.com_3', '22444356259'
'97168', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_1', '55525772666'
'97169', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_2', '22225554186'
'97170', '02aa6aa.netsolstores.com', '02aa6aa.netsolstores.com_3', '22444356259’

我想编写一个查询来检索重复的keyvalue 的数量，以及与多个domain_certificate_no 关联的keyvalue证书_1.所以我写了这个:

I want to write a query to retrieve the number of repeated keyvalue, and which keyvalue that is associated with more than one domain_certificate_no with cert _1. So I wrote this:

select count(keyvalue), keyvalue from db.table group by (keyvalue) 
having count(keyvalue)>1 and domain_certificate_no like '%_1';

我收到错误编号 1054，说明未知列 domain_certificate_no.你能告诉我有什么问题吗?如何编写查询来实现我的目的?

I get an error number 1054 saying unknown column domain_certificate_no. Can you tell me what is wrong? How can I write query to achieve my purpose ?

推荐答案

关于逻辑:

SELECT COUNT(domain_certificate_no), keyvalue
FROM db.table 
WHERE domain_certificate_no LIKE '%_1'
GROUP BY keyvalue
HAVING COUNT(keyvalue)>1;

如果还有未知列错误，请显示DESC db.table;

If there's still your unknown column error, please show the result of DESC db.table;

干杯.

这篇关于这个查询有什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！