帶有LEFTJOIN和MAX的LibreOfficeHSQLDBWHERE子句？

我運行的是 macOS 11.6、LibreOffice 7.2.2.2、HSQLDB（我的理解是這是 v.1.8，但不知道如何驗證）

我是 SQL 的新手，我正在嘗試撰寫一個資料庫來維護俱樂部會員名冊。我試圖在資料庫中找到應該向其發送續訂信件的每個人。奇怪的是，如果一個人過去從未付款，他們應該收到續訂信。最近沒有更新的老會員沒有更新，顯然每個人應該只能得到一封信。我創建了一個玩具示例來顯示我遇到的問題...

Members table:
Key (Integer, Primary key, Autoincrement)
Name (Varchar)
 ----- ---------- 
| Key | Name     |
 ----- ---------- 
|  0  | Abby     |
|  1  | Bob      |
|  2  | Dave     |
|  3  | Ellen    |
 ----- ---------- 

Payments table:
Key (Integer, Primary Key, autoincrement)
MemberKey (Integer, foreign key to Member table)
Payment Date (Date)
 ----- ----------- -------------- 
| Key | MemberKey | Payment Date |
 ----- ----------- -------------- 
|  0  |     0     | 2020-05-23   |
|  1  |     0     | 2021-06-12   |
|  2  |     1     | 2016-05-28   |
|  3  |     2     | 2020-07-02   |
 ----- ----------- -------------- 

我發現包含每個人的唯一方法是使用 LEFT JOIN。我發現選擇最近付款的唯一方法是使用 MAX。以下查詢生成每個人最近付款的串列，包括從未付款的人：

SELECT "Members"."Key", "Members"."Name", MAX( "Payments"."Payment Date" ) AS "Last Payment"
FROM { oj "Members" LEFT OUTER JOIN "Payments" ON "Members"."Key" = "Payments"."MemberKey" }
GROUP BY "Members"."Key", "Members"."Name"

它回傳下面的結果，其中只包括所有成員一次（Abby 有 2 次付款，但只出現一次與最近的付款）。不幸的是，它仍然包括像鮑勃這樣的人，他們離開俱樂部太久了，我們不想向他們發送續約通知。

 ----- ---------- -------------- 
| Key | Name     | Last Payment |
 ----- ---------- -------------- 
|  0  | Abby     | 2021-06-12   |
|  1  | Bob      | 2016-05-28   |
|  2  | Dave     | 2020-07-02   |
|  3  | Ellen    |              |
 ----- ---------- -------------- 

當我嘗試對上次付款執行任何型別的條件操作時，我遇到了障礙，以確定它是否足夠新以包含在續訂通知串列中。例如，在 HSQLDB 中，下面的查詢回傳錯誤“無法加載資料內容。不是條件”。此查詢與第一個查詢的唯一變化是添加了 WHERE 子句。

SELECT "Members"."Key", "Members"."Name", MAX( "Payments"."Payment Date" ) AS "Last Payment"
FROM { oj "Members" LEFT OUTER JOIN "Payments" ON "Members"."Key" = "Payments"."MemberKey" }
WHERE "Last Payment" >= '2020-01-01'
GROUP BY "Members"."Key", "Members"."Name"

所需的輸出應如下所示：

 ----- ---------- -------------- 
| Key | Name     | Last Payment |
 ----- ---------- -------------- 
|  0  | Abby     | 2021-06-12   |
|  2  | Dave     | 2020-07-02   |
|  3  | Ellen    |              |
 ----- ---------- -------------- 

I've been digging around the web trying anything that looks relevant. I've tried "HAVING" clauses--I can make them work with a COUNT(*) function, but I can't make them work with a MAX(*) function. I've tried using my 1st query as a subquery, and applying the WHERE clause on "Last Payment" in the main query. I've tried solutions people say work in MySQL, but I can't get them to work in HSQLDB. I tried using the 1st query as a View, and writing a query against the View. I've tried a dozen other things I don't even remember. Everything past the 1st query above throws an error. I wanted to include my toy DB, but can't find a way to attach it to the post.

Can anyone help please?

uj5u.com熱心網友回復：

這對我有用。

SELECT "Members"."Key", "Members"."Name", MAX( "Payments"."Payment Date" ) AS "Last Payment"
FROM {oj "Members" LEFT OUTER JOIN "Payments" ON "Members"."Key" = "Payments"."MemberKey"
    WHERE "Payments"."Payment Date" >= '2020-01-01'
    OR "Payments"."Payment Date" IS NULL}
GROUP BY "Members"."Key", "Members"."Name"

結果：

這也有效。

SELECT "Members"."Key", "Members"."Name", MAX( "Payments"."Payment Date" ) AS "Last Payment"
FROM { oj "Members" LEFT OUTER JOIN "Payments" ON "Members"."Key" = "Payments"."MemberKey" }
WHERE "Payments"."Payment Date" >= '2020-01-01'
    OR "Payments"."Payment Date" IS NULL
GROUP BY "Members"."Key", "Members"."Name"

也許您遇到的問題是“上次付款”只是一個列標題，而不是任何列的實際名稱。

標籤：join max where-clause hsqldb libreoffice

上一篇：SQL選擇由Id標識并由另一個表中的特定型別過濾的行串列中的所有第一項

下一篇：獲取SQL中每行d天內過去x個事件的平均值的有效方法（大資料）