mysql - 从多个表中提取不同的记录作为一个交易历史列表

Question

我正在开发员工管理/奖励系统，需要能够显示单个“交易历史记录”页面，该页面按时间顺序在一个列表中显示员工经历的不同事件。 (有点像在 Facebook 中你可以转到你的历史/操作部分并查看你所做的所有事情的时间顺序列表并影响你，即使它们彼此无关并且只有你作为普通用户)

我有不同的表用于不同的事件，每个表都有一个 employee_id 键和一个“发生的”时间戳，一些表示例:

bonuses
customers
raise
complaints
feedback

因此，无论何时发生事件(即，将新客户分配给员工，或者员工收到投诉或加薪)，都会在适当的表中添加一个新行，其中包含它影响的员工 ID 和发生时间的时间戳.

我需要一个查询来提取包含员工的所有记录(例如最多 50 条)并返回该员工的历史 View 。每个表中的字段名称不同(即奖金包括带注释的金额，客户包括客户信息等)。

我需要输出是使用列名称的摘要 View ，例如:

event_type = (new customer, bonus, feedback etc)
date
title (a brief worded title of the type of event, specified in sql based on the table its referencing)
description  (verbiage about the action, such as if its event_type bonus display the bonus amount here, if its a complain show the first 50 characters of the complaint message or the ID of the user that filed the complaint from the complaints table. All done in SQL using if statements and building the value of this field output based on which table it comes from. Such as if its from the customers table IF current_table=customers description='A customer was assigned to you by'.customers.assigner_id).

理想情况下，

有什么办法吗？

我考虑过的另一种选择是，我可以执行 5-6 个不同的查询，分别从各自的表中提取记录，然后使用 mysql 命令将所有查询的结果按时间顺序“网格化/交错”到一个列表中.那也可以接受

Answer 1

您可以使用 UNION 查询将所有信息合并在一起，并使用 ORDER BY 子句按时间顺序对操作进行排序。每个查询必须具有相同数量的字段。您的 ORDER BY 子句应该放在最后。

下面的示例假设您在客户表中有一个名为 customer_name 的字段，在奖金表中有一个名为 bonus_amount 的字段。

它看起来像这样:

SELECT   'New Customer' as event_type, date, 
         'New customer was assigned' as title, 
         CONCAT('New Customer: ', customer_name, ' was assigned') as description
FROM     customers
WHERE    employee_id = 1

UNION

SELECT   'Bonus' as event_type, date, 
         'Received a bonue' as title, 
         CONCAT('Received a bonus of $', FORMAT(bonus_amount, 2), '.') as description
FROM     bonuses
WHERE    employee_id = 1

UNION

...

ORDER BY date DESC;