如何打印由 group by 语句产生的每个项目 [英] How to print every item resulted from group by statement

问题描述

我做了一个使用 group by 的声明.现在，我需要知道每条记录的详细信息，但我需要在一个语句中执行此操作.

I have made a statement that used group by. Now, I need to know the details for every record but I need to perform this at one statement.

例如:我做了以下查询来查询以_1结尾并共享相同number的文件:

For example: I made the following query to query about the files that ends with _1 and share the same number:

SELECT number, COUNT(*) AS sum domain从表
WHERE 文件喜欢%_1"GROUP BY number HAVING sum domain > 1

SELECT number, COUNT(*) AS sum domains FROM table
WHERE file LIKE '%_1' GROUP BY number HAVING sum domains > 1

所以，如果我有下表:

domain      |   file      | Number 
------------------------------------     
aaa.com     | aaa.com_1   | 111
bbb.com     | bbb.com_1   | 222
ccc.com     | ccc.com_2   | 111
ddd.com     | ddd.com_1   | 222
eee.com     | eee.com_1   | 333
qqq.com     | qqq.com_1   | 333

查询的结果是(被多个file共享的number和file(s)的计数以 _1 结尾并共享此号码):

The result of the query is (the number that is shared by more than file and the count of the file(s) that ends with _1 and shared this number):

number | sum domains
------------------------
222    | 2
333    | 2

我需要做的是打印出文件名.我需要:

What I need to do is to print out the file names. I need:

number | file
------------------------
222    | bbb.com_1 
222    | ddd.com_1
333    | eee.com_1
333    | qqq.com_1

由于 group by 子句不允许我打印 file(s)，我该怎么做?

How can I do this since group by clause does not allow me to print the file(s) ?

推荐答案

您可以JOIN将您的查询作为子查询返回到主表，以获取原始行和文件名:

You can JOIN your query back against the main table as a subquery, to get the original rows and filenames:

SELECT 
  main.number, 
  main.file
FROM 
  table AS main
  /* Joined against your query as a derived table */
  INNER JOIN (
    SELECT number, COUNT(*) AS sum domains 
    FROM table
    WHERE RIGHT(file, 2) = '_1' 
    GROUP BY number 
    HAVING sum domains > 1
    /* Matching `number` against the main table, and limiting to rows with _1 */
  ) as subq ON main.number = subq.number AND RIGHT(main.file, 2) = '_1'

http://sqlfiddle.com/#!2/cb05b/6

请注意，我已将您的 LIKE '%_1' 替换为 RIGHT(file, 2) = '_1'.但是，如果没有基准测试，很难说哪个会更快.

Note that I have replaced your LIKE '%_1' with RIGHT(file, 2) = '_1'. Hard to tell which will be faster without a benchmark though.

这篇关于如何打印由 group by 语句产生的每个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！