mysql检索所有行，同时也使用摘要 [英] mysql retrieving all rows while also using summaries

问题描述

我有一个查询:

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;

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

What I need out of this is for EACH row to be output, but I can't figure out how to do that. Right now, I'm getting this:

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的摘要，但是如果我不使用任何group by语句，则结果中仅得到1行.

Because of the group by, I'm getting a summary of qh.APN, but if I don't use any group by statement, I get 1 line only in my result.

我希望有这个APN Count列，而在同一时间中显示所有行-没有汇总任何值.像这样:

I am looking to have this APN Count column, while at the same time showing all the rows - not having any values summarized. Like this:

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编写此代码，因为看到汇总数据比没什么更好，并且因为我不知道在这里使用正确的语法来获得想要的结果.

I need to see each individual record that makes up the APN Count column because I need to see each BPN, not just one summary row. I am writing this with a group by right now because seeing the summarized data is better than nothing, and because I don't know the correct syntax to use here to get the result I want.

推荐答案

使用汇总查询作为子查询将表1加入.

Join table 1 with your aggregate query as a subquery.

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

这篇关于mysql检索所有行，同时也使用摘要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！