無法在 MySql 中建構正確的 SELECT

請幫助我創建一個正確的 MySql 查詢，但我沒有這樣做。

我的數據庫中有 2 個表：

CREATE TABLE IF NOT EXISTS `user_audit` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(10) unsigned NOT NULL,
 `audit_event` varchar(128) NOT NULL,
 `data` varchar(256) NOT NULL DEFAULT '',
 `source_ip` varchar(256) NOT NULL DEFAULT '',
 `user_agent` varchar(256) NOT NULL,
 `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27181 ;

CREATE TABLE IF NOT EXISTS `user_balance_transactions` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(10) unsigned NOT NULL,
 `audit_event_id` int(10) unsigned NOT NULL,
 `private_tournament_id` int(10) unsigned DEFAULT NULL,
 `regular_tournament_id` int(10) unsigned DEFAULT NULL,
 `points` int(11) NOT NULL,
 `description` varchar(256) NOT NULL DEFAULT '',
 `create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `private_tournament_id` (`private_tournament_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1001 ;

我有 2 個我感興趣的審計事件：

user_audit.audit_event = "RGL_TOURNAMENT_ENTRANCE_PAID"
user_audit.audit_event = "RGL_TOURNAMENT_ENTRANCE_REFUNDED"

這些審計事件是相互對立的，即使用者可以支付，然後可以退款，然後再次使用支付等等。

在這兩個表之上，我必須建構一個 SQL 選擇語句，它將返回DISTINCT user_id, regular_tournament_id, create_timestamp所有使用者：

1. user_balance_transactions.regular_tournament_id不是NULL
2. user_balance_transactions.audit_event_id指向user_audit表 whereuser_audit.audit_event = 'RGL_TOURNAMENT_ENTRANCE_PAID' 並且它是按時間順序發生的最後一個事件（最重要的！）。

重要提示：在user_audit表 2 中，可能的審計事件彼此相關：'RGL_TOURNAMENT_ENTRANCE_PAID'和'RGL_TOURNAMENT_ENTRANCE_REFUNDED'，但我們user_id只需要最後一個事件的使用者 'RGL_TOURNAMENT_ENTRANCE_PAID'。 3. create_timestamp是最後一個事件的時間戳 - 'RGL_TOURNAMENT_ENTRANCE_PAID'

---

A. “user_balance_transactions”表的測試數據：

id, user_ID, audit_event_id, private_tournament_id, regular_tournament_id, points, description, create_timestamp

2, 23, 1711, null, 77, 10, "credit", "2015-06-12T17:23:44"

3, 23, 1712, null, 77, -10, "debit", "2015-06-12T17:41:44"

4, 23, 1713, null, 77, 10, "credit", "2015-06-12T18:11:44"

B. “user_audit”表的測試數據：

id, user_ID, audit_event, data, source_ip, user_agent, create_timestamp

1711, 23, "RGL_TOURNAMENT_ENTRANCE_PAID", "","","", "2015-06-12T17:23:44"

1712, 23, "RGL_TOURNAMENT_ENTRANCE_REFUNDED", "","","", "2015-06-12T17:41:44"

1713, 23, "RGL_TOURNAMENT_ENTRANCE_PAID", "","","", "2015-06-12T18:11:44"

C. 預期的 SQL 輸出：

user_id, regular_tournament_id, create_timestamp

23, 77, "2015-06-12T18:11:44" (last RGL_TOURNAMENT_ENTRANCE_PAID event)

---

“user_balance_transactions”儲存使用者交易，“user_audit”儲存所有使用者審計事件，其中一些與餘額交易有關。

這就是為什麼“user_balance_transactions”表具有“user_audit”表“audit_event_id”的外鍵

嘗試這個：

SELECT 
      t.user_id, 
      t.audit_event 
FROM
       (SELECT ua.user_id, ua.audit_event FROM user_audit ua, user_balance_transactions ubt WHERE
       (audit_event = 'RGL_TOURNAMENT_ENTRANCE_PAID' OR audit_event = 'RGL_TOURNAMENT_ENTRANCE_REFUNDED')
       AND ubt.regular_tournament_id='{$tournament_id}'
       AND ubt.audit_event_id=ua.id
       ORDER BY ua.create_timestamp DESC LIMIT 1) t
       WHERE t.audit_event != 'RGL_TOURNAMENT_ENTRANCE_REFUNDED'

您可以使用函式從表中MAX獲取最大值。create_time``user_balance_transactions

SELECT
   ua.user_id,
   ubt.regular_tournament_id,
   MAX(ubt.create_timestamp) as event_date,
   ua.audit_event
FROM test.user_audit AS ua
LEFT JOIN test.user_balance_transactions AS ubt ON (ubt.audit_event_id=ua.id AND ubt.user_id=ua.user_id AND ua.audit_event='RGL_TOURNAMENT_ENTRANCE_PAID')
GROUP BY ua.user_id;

更新：

測試：

mysql> SELECT
   ->     ua.user_id,
   ->     ubt.regular_tournament_id,
   ->     MAX(ubt.create_timestamp) as event_date,
   ->     ua.audit_event
   -> FROM test.user_audit AS ua
   -> LEFT JOIN test.user_balance_transactions AS ubt ON (ubt.audit_event_id=ua.id AND ubt.user_id=ua.user_id AND ua.audit_event='RGL_TOURNAMENT_ENTRANCE_PAID')
   -> GROUP BY ua.user_id;
+---------+-----------------------+---------------------+------------------------------+
| user_id | regular_tournament_id | event_date          | audit_event                  |
+---------+-----------------------+---------------------+------------------------------+
|      23 |                    77 | 2015-06-12 18:11:44 | RGL_TOURNAMENT_ENTRANCE_PAID |
+---------+-----------------------+---------------------+------------------------------+
1 row in set (0.00 sec)

mysql> 

引用自：https://dba.stackexchange.com/questions/107512