我是 MySQL 的新手，我对如何构建以下查询有些迷茫。

下表显示了我想要的预期输出。

+---------+------------+-----------------+-------+----------+------------+
| Account |  Inv Date  |   Description   | Value | Inv Item |  Eff From  |
+---------+------------+-----------------+-------+----------+------------+
|      12 | 2018-08-14 | Unlimited (SV4) | 5.99  |  3056746 | 2018-08-02 |
|      12 | 2018-08-14 | Unlimited (SV4) | 5.99  |  3056748 | 2018-08-02 |
|      15 | 2018-09-14 | Unlimited (GB2) | 7.99  |  3187748 | 2018-09-07 |
|      15 | 2018-09-14 | Unlimited (GB1) | 7.26  |  3187749 | 2018-09-07 |
+---------+------------+-----------------+-------+----------+------------+

我想查看具有多个描述(例如“Unlimited”)的帐户，“Inv Date”需要仅显示每个帐户的最早日期。而“Eff from”必须是“状态”在帐户上首次“激活”时的时间。

这是我目前的代码，我还包含了我要加入的表格。

SELECT
    AA.Account,
    MIN(II.InvDate),
    ST.Description,
    ST.Value,
    ST.InvItem,
    MIN(AA.EffFrom)
FROM db.tblAccountStatus AA
    INNER JOIN db.tblInvoiceID II ON II.Account = AA.Account
    INNER JOIN db.tblInvoiceStatus ST ON ST.InvID = II.InvID
WHERE AA.Status = 'Active'
    AND ST.Description LIKE '%Unlimited%'
        GROUP BY ST.InvItem HAVING COUNT(II.InvDate) >1

.

db.tblInvoiceStatus
+--------+------------------+-------+----------+
| Inv ID |   Description    | Value | Inv Item |
+--------+------------------+-------+----------+
|   1030 | Unlimited (SV4)  | 5.99  |  3056746 |
|   1030 | Unlimited (SV4)  | 5.99  |  3056748 |
|   1030 | Extras (PB1)     | 0.99  |  3056751 |
|   1045 | Unlimited (SLV2) | 5.99  |  3166769 |
|   1045 | Extras (PA1)     | 1.99  |  3166770 |
|   1078 | Unlimited (GB2)  | 7.99  |  3187748 |
|   1078 | Unlimited (GB1)  | 7.26  |  3187749 |
|   1091 | Unlimited (SV1)  | 5.99  |  3186788 |
|   1098 | Unlimited (BA2)  | 5.49  |  3209899 |
+--------+------------------+-------+----------+

.

db.tblInvoiceID
+--------+---------+------------+
| Inv ID | Account |  Inv Date  |
+--------+---------+------------+
|   1030 |      12 | 2018-08-14 |
|   1045 |      12 | 2018-09-14 |
|   1078 |      15 | 2018-09-14 |
|   1091 |      17 | 2018-09-15 |
|   1098 |      17 | 2018-10-15 |
|   1099 |      19 | 2018-10-20 |
+--------+---------+------------+

.

db.tblAccountStatus
+---------+--------+------------+------------+
| Account | Status |  Eff From  |   Eff To   |
+---------+--------+------------+------------+
|      12 | Active | 2018-08-02 | 2018-09-16 |
|      12 | Active | 2018-09-17 | 2018-09-28 |
|      12 | Active | 2018-09-29 | NULL       |
|      15 | Active | 2018-09-07 | 2018-09-16 |
|      15 | Closed | 2018-09-17 | NULL       |
|      17 | Active | 2016-04-28 | NULL       |
|      19 | Active | 2015-05-05 | NULL       |
+---------+--------+------------+------------+

最佳答案

内部加入 db.tblInvoiceID 似乎很好，它有最旧的日期记录，如下 sql 所示。

SELECT
    AA.Account,
    II.InvDate,
    ST.Description,
    ST.Value,
    ST.InvItem,
    AA.EffFrom
FROM db.tblAccountStatus AA
    INNER JOIN (
        SELECT InvID, Account, MIN(InvDate) AS InvDate FROM db.tblInvoiceID
        GROUP BY Account
    ) II ON II.Account = AA.Account
    INNER JOIN db.tblInvoiceStatus ST ON ST.InvID = II.InvID
WHERE AA.Status = 'Active'
    AND ST.Description LIKE '%Unlimited%'
        GROUP BY ST.InvItem

以下是我的输出。
尽管您的预期输出不包括 Account = 17，但我无法从您的问题中理解条件。有补充条件的请教教我。

+---------+------------+------------------+-------+---------+------------+
| Account | InvDate    | Description      | Value | InvItem | EffFrom    |
+---------+------------+------------------+-------+---------+------------+
|      12 | 2018-08-14 | Unlimited (SV4)  |  5.99 | 3056746 | 2018-08-02 |
|      12 | 2018-08-14 | Unlimited (SV4)  |  5.99 | 3056748 | 2018-08-02 |
|      17 | 2018-09-15 | Unlimited (SV1)  |  5.99 | 3186788 | 2016-04-28 |
|      15 | 2018-09-14 | Unlimited (GB2)  |  7.99 | 3187748 | 2018-09-07 |
|      15 | 2018-09-14 | Unlimited (GB1)  |  7.26 | 3187749 | 2018-09-07 |
+---------+------------+------------------+-------+---------+------------+
5 rows in set (0.00 sec)

关于MySQL，计数 LIKE 语句和最旧日期问题，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/52914067/