mysql 检索所有行，同时还使用摘要

Question

我有一个问题:

SELECT name, APN, BPN, count(APN), min(aCost), min(bCost), ceil(avg(aQty)), 
max(aQty), sum(bShipped), 
CONCAT(truncate((avg(aResale)-avg(aCost))/avg(aResale),2) * 100,'%'), code FROM
(SELECT name, APN, BPN, aCost, aQty, code
    FROM table_1
    WHERE customer = '12345' AND
    aDate >= '2013-01-01' and
    aDate <= '2015-12-12') as qh
INNER JOIN (SELECT CPN, bCost, bResale, bShipped from table_2 
WHERE customer = '12345') as ih
ON qh.APN = ih.CPN
WHERE bShipped > 0
GROUP BY qh.APN;

我需要的是输出每一行，但我不知道该怎么做。现在，我得到这个:

NAME   |   APN  |  BPN   |  APN Count
asdf       001     555      3  /*summary of APN 001*/
qere       002     865      1  /*summary of APN 002*/
rtrt       003     123      2  /*summary of APN 003*/

由于 group by，我得到了 qh.APN 的summary，但是如果我不使用任何 group by 语句，我在结果中只得到 1 行。

我希望有这个 APN Count 列，同时 显示所有行 - 没有汇总任何值。像这样:

NAME   |   APN  |  BPN   |  APN Count
asdf       001     555      3  /*Individual record, with count too*/
asdf       001     862      3  /*Individual record, with count too*/
asdf       001     999      3  /*Individual record, with count too*/
qere       002     865      1  /*Individual record, with count too*/
rtrt       003     123      2  /*Individual record, with count too*/
rtrt       003     456      2  /*Individual record, with count too*/

我需要查看组成 APN Count 列的每条记录，因为我需要查看每个 BPN，而不仅仅是一个摘要行。我现在正在用 group by 写这篇文章，因为看到汇总数据总比什么都没有好，而且因为我不知道在这里使用正确的语法来获取我想要的结果。

Answer 1

将表 1 作为子查询与您的聚合查询联接。

SELECT t1.name, t1.apn, t1.bpn, t1.code, t2.*
FROM table_1 AS t1
JOIN (
    SELECT APN, count(APN) AS APN_count, min(aCost) AS min_aCost, min(bCost) AS min_bCost, ceil(avg(aQty)) AS avgQty, 
    max(aQty) AS maxQty, sum(bShipped) AS sum_bShipped, 
    CONCAT(truncate((avg(aResale)-avg(aCost))/avg(aResale),2) * 100,'%') AS avg_Margin FROM
    (SELECT name, APN, BPN, aCost, aQty, code
        FROM table_1
        WHERE customer = '12345' AND
        aDate >= '2013-01-01' and
        aDate <= '2015-12-12') as qh
    INNER JOIN (SELECT CPN, bCost, bResale, bShipped from table_2 
    WHERE customer = '12345') as ih
    ON qh.APN = ih.CPN
    WHERE bShipped > 0
    GROUP BY qh.APN) AS t2
ON t1.APN = t2.APN