mysql - 如何在 MySQL 的单个查询中获取平均数据和特定数据？

Question

我试图在单个查询中从表中获取平均数据和特定数据。 “Billing”表如下所示:

-------------------------------------------
| UserID | ZipCode  | Amount | Date       |
-------------------------------------------
| 14     | 55520    | 777    | 14/06/2016 |
| 14     | 55520    | 250    | 04/05/2016 |
| 15     | 55520    | 340    | 14/06/2016 |
| 15     | 55520    | 300    | 12/05/2016 |
-------------------------------------------

我可以用这个获取用户 14 的特定数据(不幸的是，我的日期列是一个字符串，因此是 STR_TO_DATE 函数):

select DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%m/%Y') as Date, Amount as 'Your Amount' from Billing where UserID = 14 order by Date asc;

结果如下:

-------------------------
| Date    | Your Amount |
-------------------------
| 05/2016 | 250         | 
| 06/2016 | 777         |
-------------------------

我可以用这个得到邮政编码 55520 的平均值:

select DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%m/%Y') as Date, avg(Amount) as 'Average Amount' from Billing where ZipCode = 55520 group by Date;

结果是:

----------------------------
| Date    | Average Amount |
----------------------------
| 05/2016 | 275.00         | 
| 06/2016 | 558.50         |
----------------------------

但我需要在单个查询中使用它才能在图表中使用它。所以我尝试以下操作:

select DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%m/%Y') as Date, Amount as 'Your Amount', (select avg(Amount) from Billing where ZipCode = 55520 group by Date) as 'Average Amount'from Billing where UserID = 14 group by Date order by Date asc;

但我现在收到“子查询返回多于 1 行”错误。我怎样才能解决这个问题？还是有更好的方法来获取我想要的数据？基本上我需要这样的结果:

------------------------------------------
| Date    | Your Amount | Average Amount |
------------------------------------------
| 05/2016 | 250         |  275.00        | 
| 06/2016 | 777         |  558.50        |
------------------------------------------

Answer 1

您必须在子查询中使用关联:

select DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%m/%Y') as Date, 
       Amount as 'Your Amount',
       (select avg(Amount) as 'Average Amount' 
        from Billing as t2
        where DATE_FORMAT(STR_TO_DATE(t2.`Date`, '%d/%m/%Y'), '%m/%Y') =
              DATE_FORMAT(STR_TO_DATE(t1.`Date`, '%d/%m/%Y'), '%m/%Y')
              AND ZipCode = 55520 
        group by DATE_FORMAT(STR_TO_DATE(`Date`, '%d/%m/%Y'), '%m/%Y')) as 'Average Amount'
from Billing as t1
where UserID = 14 
order by Date asc;

Demo here