合并具有不同列的表的搜索结果并按日期排序

I'm trying to build a search result of three differend log tables with one output. This output has to be sorted by "time" and echoed in a foreach loop.

我正在尝试使用一个输出构建三个差异日志表的搜索结果。此输出必须按“时间”排序,并在foreach循环中回显。

table

id | record-id | user_id | time | sector | info

id | record-id | user_id |时间|部门|信息
table

id | user_id | ip | time | path | info | message | level

id | user_id | ip |时间|路径|信息|消息|水平
table

id | req_id | user | time | info | type | ip

id | req_id |用户|时间|信息|类型| IP

The three results would all be different and hardly be comparable ...

这三个结果都是不同的,几乎没有可比性......

The question is, where and how to merge the result ... I dont think I can handle this with only one query, or? JOIN (there is no reference) and UNION (different count of colums) may be the wrong choice?

问题是,在哪里以及如何合并结果......我不认为我只用一个查询来处理这个问题,或者? JOIN(没有参考)和UNION(不同的colums数)可能是错误的选择?

If there is no other option I can make 3 querys and merge the result array like that

如果没有其他选项,我可以进行3次查询并合并结果数组

function cmp($a, $b) {
  if ($a['time'] == $b['time']) {
    return 0;
  }

  return ($a['time'] < $b['time']) ? -1 : 1;
}

$new_arr = array_merge($result1, $result2, $result3);
uasort($new_arr, 'cmp');

2 个解决方案

#1

A possible solution is to use UNION ALL and CONCAT_WS() to unify your resultset into fixed number of columns (5 in an example below). And then explode() details column values in php while you iterate over your resultset.

一种可能的解决方案是使用UNION ALL和CONCAT_WS()将结果集统一为固定数量的列(下例中为5)。然后,在迭代结果集时,explode()会详细说明php中的列值。

SELECT 1 source, id, record_id id2, time, CONCAT_WS('|', user_id, sector, info) details
  FROM table1
 UNION ALL
SELECT 2, id, user_id, time, CONCAT_WS('|', ip, path, info, message, level)
  FROM table2
 UNION ALL
SELECT 3, id, req_id, time, CONCAT_WS('|', user, info, type, ip)
  FROM table3
 ORDER BY time DESC, source, id, id2

Sample output:

| SOURCE | ID |  ID2 |                TIME |                                                              DETAILS |
-------------------------------------------------------------------------------------------------------------------
|      1 | 11 |  111 | 2013-06-30 16:00:00 |                                              12|sector1|info details |
|      2 | 12 |   13 | 2013-06-30 15:00:00 | 10.10.10.1|/your/path/some/where|info details|message details|level1 |
|      3 | 13 | 1024 | 2013-06-30 12:00:00 |                              user1|info details|type info|10.10.10.2 |

Here is SQLFiddle dmeo

这是SQLFiddle dmeo

#2

You could use a union as long as you "stretch" the results of the unbalanced columns, e.g.

只要您“拉伸”不平衡列的结果,就可以使用联合,例如

SELECT 'table1' AS source, record_id, user_id, time, sector, path
UNION ALL
SELECT 'table2', user_id, null, null, ip, etc....
UNION ALL
SELECT 'table3', null, req_id, null, null, ip, etc...

Basically for any field that's common between the three table, or at least type-compatible, you can use a particular column in the results for that field. For unmatched/type-imcompatible fields in one table, simply select NULL as the 'matching' value in the other queries.

基本上对于三个表之间常见的任何字段,或者至少类型兼容,您可以在该字段的结果中使用特定列。对于一个表中不匹配/类型不兼容的字段,只需在其他查询中选择NULL作为“匹配”值。

However, this makes your client-side logic far more complicated, so you're probably better off just biting the bullet and running 3 different queries. At some point, the minor efficiency gains of firing off a single union'd query are outweighed by the extra processing you have to do in the client to separate out the relevant data again.

但是,这会使您的客户端逻辑变得更加复杂,因此您最好只是咬住子弹并运行3个不同的查询。在某些时候,通过在客户端中必须执行的额外处理来再次分离出相关数据,会超过触发单个联合查询的次要效率增益。

#1