查询不完整 - MYSQL [英] Query not complete - MYSQL

问题描述

我的查询到目前为止：

SELECT CD_no, purchasedate, COUNT(*) mostsales
FROM Sales A
GROUP BY CD_no, purchasedate
HAVING COUNT(*) =
        (SELECT MAX(mostsales)
         FROM
            (SELECT CD_no, purchasedate, COUNT(*) AS mostsales
                FROM Sales
            GROUP BY CD_no, purchasedate) B
            WHERE CD_no = A.CD_no)

b $ b

到目前为止，我的查询是从CD_no和purchase的输入生成的，发现日期对该特定CD的销售额最大。但是，这只会为每个CD号返回一个值。然而，可能存在CD_no在两个不同日期可能具有完全相同的最大购买的可能性，因此应当相对于CD号显示两个日期。

My query so far generates from an input of CD_no's and purchase's, finds date had the most sales for that specific CD. However this only returns one value for each CD number. However there may be a possibility that a CD_no may have the exact same maximal purchases on two different dates, and therefore should display both dates against the CD number.

当前输出：

CD_NO           mostsales
1             2011-12-30
2             2012-03-22
3             2012-04-24

所需输出：

CD_NO           mostsales
1             2011-12-30
2             2012-03-22
3             2012-04-24
3             2012-04-22

从以下输入：

CD_NO         purchase_date
1              2011-12-30
1              2011-12-30
1              2011-12-29
1              2011-12-28
2              2012-03-22
2              2012-03-22
2              2012-03-21
3              2012-04-24
3              2012-04-24
3              2012-04-22
3              2012-04-22              
3              2012-04-21

推荐答案

听起来你需要添加一个聚合函数：

Sounds like you need to add an aggregate function:

SELECT CD_NO, purchase_date, Count(purchase_date) totalSales
FROM Artist
GROUP BY purchase_date
ORDER BY CD_NO

MySQL允许您限制 GROUP BY 的字段数，在 purchase_date 上分组。

MySQL allows you to limit the number of fields that you GROUP BY so this above is only grouping on the purchase_date.

在其他RDBMS中，您可以使用子查询：

In other RDBMS, you could use a subquery:

SELECT a1.CD_NO, a1.purchase_date, a2.TotalSalesByDate
FROM Artist a1
INNER JOIN
(
    SELECT count(purchase_date) TotalSalesByDate, purchase_date
    FROM Artist
    GROUP BY purchase_date
) a2
   on a1.purchase_date= a2.purchase_date
ORDER BY a1.CD_NO

对于您的更新，您应该能够使用类似于以下的东西：

Based on your updates, you should be able to use something similar to this:

SELECT a1.CD_NO, date_format(a1.purchase_date, '%Y-%m-%d') MostSales
FROM Artist a1
INNER JOIN
(
    SELECT count(purchase_date) TotalSalesByDate, purchase_date
    FROM Artist
    GROUP BY purchase_date
) a2
   on a1.purchase_date= a2.purchase_date
group by a1.CD_NO, a1.purchase_date
having max(TotalSalesByDate) = (select max(Total) totalsales
                                 from
                                 (
                                    SELECT cd_no, purchase_date, Count(purchase_date) Total
                                    FROM Artist a1
                                    GROUP BY cd_no, purchase_date
                                 ) src
                                 where a1.cd_no = src.cd_no
                                 GROUP BY cd_no);

查看 SQL Fiddle with Demo

结果：

| CD_NO |  MOSTSALES |
----------------------
|     1 | 2011-12-30 |
|     2 | 2012-03-22 |
|     3 | 2012-04-22 |
|     3 | 2012-04-24 |

这篇关于查询不完整 - MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！