根据相同的id从两个表中选择并分组 [英] SELECT from two tables based on the same id and grouped

问题描述

我有两个不同结构的表(table1已确认项目，table2项目等待确认，每个用户在任一表中可能有更多项目):

I have two tables with different structure (table1 confirmed items, table2 items waiting for confirmation, each user may have more items in either table):

table1
id (primary) | user_id | group_id | name | description | active_from | active_to

和

table2
id (primary) | user_id | group_id | name | description | active_from

我想要的是某个用户的所有项目的列表 - 即两个表中具有相同 user_id(例如 1)的行准备按 group_id 分组显示(即第一个组 1，然后是组 2 等)按 name(在每个组内)排序.输出应如下所示:

What I try to have is the list of all the items of a certain user - i.e. rows from both tables with the same user_id (e.g. 1) prepared to be shown grouped by group_id (i.e. first group 1, then group 2 etc.) ordered by name (inside each group). Output should look like that:

(all the groups below belong to the same user - with certain user_id)

# Group 1 (group_id) #
Item 67 (id): Apple (name), healthy fruit (description) (item stored in table1)
Item 29: Pear, rounded fruit (item stored in table2)

# Group 2 #
Item 14: Grape, juicy fruit (item stored in table2)

# Group 3 #
Item 116: Blackberry, shining fruit (item stored in table2)
Item 14: Plum, blue fruit (item stored in table1)
Item 7: Raspberry, red fruit (item stored in table1)

我无法找到可行的解决方案，我尝试使用 JOIN 以及使用 WHERE 子句从两个表中使用简单的 SELECT.

I am not able to have a working solution, I tried using JOIN as well as simple SELECT from two tables using WHERE clause.

我以以下代码结束，该代码显然不起作用，因为返回了错误(高得多 - 冗余)数量的结果(不是谈论来自 table2 的结果的非实现排序):

I ended with the following code which is obviously not working since returning wrong (much higher - redundant) number of results (not talking about non-implemented ordering of the results from table2):

SELECT table1.id, table1.user_id, table1.group_id, table1.active_from, table1.active_to, table2.id, table2.user_id, table2.group_id, table2.active_from
FROM table1
LEFT JOIN table2
ON table1.user_id = table2.user_id
WHERE (table1.group_id='".$group_id."' OR table2.group_id='".$group_id."') AND (table1.user_id='".$user_id."' OR table2.user_id='".$user_id."')
ORDER BY table1.property_name ASC

推荐答案

union 可以解决您的问题.需要一些数据处理来为联合双方提供相同数量和类型的列:

A union would suit your problem. Some data massage is required to give both sides of the union the same number and type of columns:

select  group_id
,       id as item_id
,       name
,       description
,       source_table
from    (
        select  id
        ,       user_id
        ,       group_id
        ,       name
        ,       description
        ,       'from table1' source_table
        from    table1
        union all
        select  id
        ,       user_id
        ,       group_id
        ,       name
        ,       description
        ,       'from table2'  -- Column name is already defined above
        from    table2
        ) as SubQueriesMustBeNamed
where   user_id = 1
order by
        group_id
,       name

SQL Fiddle 的工作示例.

要根据需要格式化结果集，请迭代结果集.当 group_id 改变时，打印一个 # Group N # 标题.

To format the result set as you like, iterate over the result set. When the group_id changes, print a # Group N # header.

客户端应该不需要其他循环或迭代，只需一个 foreach 或查询返回的一组行的等效项.

There should be no need to have other loops or iterations client-side, just one foreach or equivalent over the set of rows returned by the query.

这篇关于根据相同的id从两个表中选择并分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！