时间戳

首页 > 解决方案 > 如何在mysql中使用带有group by的子查询？

php - 如何在mysql中使用带有group by的子查询？

问题描述

我有一张针对特定用户的多行表。我有很多年的数据，例如 2019、2018、2016 年等。我有两种情况：

1.我想要特定 INVOICE_YEAR 的数据。

2，但同时我想要为特定用户生成发票的第一日期。

我的 sql 查询：

$yearOf这是动态年份输入变量。

$yearOf= 2019;

$Records = "SELECT MIN(inv.INVOICE_DATE) AS MIN_INVOICE_DATE
                    FROM invoices as inv
                    LEFT JOIN customers as cm ON cm.CUSTOMER_ID = inv.CUSTOMER_ID 
                    where inv.INVOICE_YEAR IN (".$yearOf.")
                    group by inv.CUSTOMER_ID ORDER BY cm.CUSTOMER_NAME ASC";

如您所见，如果我想要 INVOICE_YEAR IN ('2019') 的所有用户的数据。我的第一个条件将满足，我将获得 INVOICE_YEAR = 2019 的用户的所有数据。

但同时我想要第一个发票日期，所以MIN(inv.INVOICE_DATE)我使用了这个，但是因为我
where inv.INVOICE_YEAR IN (".$yearOf.")在 where 条件下使用了这个，所以它给了我特定年份的第一个发票日期。

但我想要所有用户的整个表中的第一个发票日期。

我尝试使用子查询，但它显示子查询返回超过 1 行的错误

我的子查询查询：

$Records = "SELECT 
                    (
                    SELECT MIN(inv.INVOICE_DATE) AS MIN_INVOICE_DATE FROM invoices AS inv GROUP BY inv.CUSTOMER_ID) AS MIN_INVOICE_DATE
                    FROM invoices as inv 
                    LEFT JOIN customers as cm ON cm.CUSTOMER_ID = inv.CUSTOMER_ID 
                    where inv.INVOICE_YEAR IN (".$yearOf.")
                    group by inv.CUSTOMER_ID ORDER BY cm.CUSTOMER_NAME ASC";

例如：

有 3 位客户 101,102,103

表中的数据如下：

id | customer_id | invoice_date | invoice_year
1  | 101         | 2019-01-01   |  2019
2  | 101         | 2016-01-01   |  2016
3  | 101         | 2017-01-01   |  2017
4  | 101         | 2016-01-02   |  2016
5  | 102         | 2019-01-02   |  2019
6  | 103         | 2018-01-02   |  2018
7  | 103         | 2019-01-07   |  2019
8  | 102         | 2015-01-02   |  2015

当我请求查询以获取具有特定用户的第一个发票日期的 INVOICE_YEAR 2019 数据时，它应该提供如下输出：

id | customer_id | invoice_date | invoice_year | min_invoice_date
1  | 101         | 2019-01-01   |  2019        | 2016-01-01
5  | 102         | 2019-01-02   |  2019        | 2015-01-02
7  | 103         | 2019-01-07   |  2019        | 2019-01-07

因为我想要所有用户的第一个发票日期 IN COLUMN MIN_INVOICE_DATE。

但它向我显示了如下数据：

id | customer_id | invoice_date | invoice_year | min_invoice_date
1  | 101         | 2019-01-01   |  2019        | 2019-01-01
5  | 102         | 2019-01-02   |  2019        | 2019-01-02
7  | 103         | 2019-01-07   |  2019        | 2019-01-07

标签： phpmysqlgroup-bysubquerymin

解决方案

您想要提取每个客户在 2019 年的最早发票，以及他们在整个表格中最早发票的日期。

在 MySQL 8.0 中，您可以使用窗口函数解决此问题：

SELECT id, customer_id, invoice_date, invoice_year, min_invoice_date
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER(PARTITION BY customer_id, invoice_year ORDER BY invoice_date) rn,
        MIN(invoice_date) OVER(PARTITION BY customer_id) min_invoice_date
    FROM mytable t
) x
WHERE invoice_year = 2019 AND rn = 1

在早期版本中，您可以：

  • JOINinvoice_date带有计算每个客户的总体最小值的查询的表
  • 使用带NOT EXISTS条件的相关子查询过滤 2019 年最早的发票面值客户

询问：

SELECT t.id, t.customer_id, t.invoice_date, t.invoice_year, m.min_invoice_date
FROM mytable t
INNER JOIN (
    SELECT customer_id, MIN(invoice_date) min_invoice_date 
    FROM mytable 
    GROUP BY customer_id
) m ON m.customer_id = t.customer_id
WHERE 
    t.invoice_year = 2019
    AND NOT EXISTS (
        SELECT 1 
        FROM mytable t1
        WHERE t1.invoice_year = 2019 
        AND t1.customer_id = t.customer_id
        AND t1.invoice_date < t.invoice_date
    )

在这个关于 DB Fiddle 的演示中，两个查询都返回：

| id  | customer_id | invoice_date | invoice_year | min_invoice_date |
| --- | ----------- | ------------ | ------------ | ---------------- |
| 1   | 101         | 2019-01-01   | 2019         | 2016-01-01       |
| 5   | 102         | 2019-01-02   | 2019         | 2015-01-02       |
| 7   | 103         | 2019-01-07   | 2019         | 2018-01-02       |

推荐阅读