mysql - 如何精简这样的专栏？

Question

我尝试过寻找类似的东西，但没有成功......

这是关于客户管理系统的表系统。特别是，我需要为每个客户创建一个注释历史记录。

因此，我有一个表“customers”，其中包含列 customers.customer_ID、customers.lastname、customers.firstname、customers.postal_code、customers.city 和 customers.street；

另一个表“notes”，其中包含列 notes.note_ID、notes.customer_ID、notes.subject、notes。描述和notes.entered_on

现在我需要创建第三个表搜索，它浓缩了上面的大部分信息。它有表 search.contact_ID、search.name、search.address 和 search.history。这应该看起来像这样:

contacts:
contact_ID  | lastname  | firstname | ...
------------+-----------+-----------+-----
1           | Doe       | John      | ...
2           | Dane      | Jane      | ...

note:
note_ID | contact_ID    | subject               | description           | entered_on
--------+---------------+-----------------------+-----------------------+----------------
1       | 1             | call received         | John Doe called us to | 2014-05-03
        |               |                       | ask for an offer      |
2       | 1             | offer made            | We called John Doe to | 2014-06-03
        |               |                       | submit our offer      |
3       | 2             | advertisement call    | We called Jane Dane to| 2014-06-03
        |               |                       | inform her of our     |
        |               |                       | latest offer          |
4       | 1             | offer accepted        | John Doe called to    | 2014-08-03
        |               |                       | accept our offer      |

search:
contact_ID  |   name        |   address                       | history
------------+---------------+---------------------------------+-------------------
1           | Doe, John     | 55 Main Street, 12345 Oldtown   | 'On 2014-08-03 offer accepted: John Doe accepted our offer.  
            |               |                                 | On 2014-06-03 offer made: We called John Doe to submit our offer. 
            |               |                                 | On 2014-05-03 call received: John Doe called us to ask for an offer.'
2           | Dane, Jane    | 111 Wall Street, 67890 Newtown  | 'On 2014-06-03 advertisement call: We called Jane Dane to submit our offer.' 

虽然我可以处理其余的大部分内容，但我不知道如何生成历史信息。我的想法如下

WHILE 
    customers.customer_ID = note.customer_ID 
    AND 
    note.entered_on = GREATEST(note.entered_on)
DO 
    SET customers.note_history = CONCAT_WS(' | ', CONCAT_WS(': ',note.subject,note.description), customers.note_history);

但这不一定是按时间顺序排列的。另外，如何将其转换为与用于创建表的其余部分的 SELECT INTO 兼容的语句？

Answer 1

听起来像是 Group-By 的情况，以及 GROUP_CONCAT

CREATE TABLE search (PRIMARY KEY(contact_ID))
  SELECT contact_ID, CONCAT(lastname,', ',firstname) AS name, address, 
    GROUP_CONCAT(CONCAT('On ',entered_on,' ',subject,': ',description) 
        ORDER BY note_ID SEPARATOR "\n") AS history
  FROM contacts LEFT JOIN note USING (contact_ID)
  GROUP BY contact_ID

如果不想使用 CREATE TABLE .. SELECT ... ，可以先创建(或截断!)表，然后使用 INSERT INTO ... SELECT .. . 代替。