MySQL通过比较两个不同的标准从2个不同的表中获得结果

It's me again with my weird queries.

我的怪异查询再次成为了我。

table users:

id | type | partner | company
------------------------------
 1 |   2  |   0     | comp1
 2 |   3  |   2     | comp2
 3 |   3  |   2     | comp3
 4 |   3  |   3     | comp4

table orders:

id | user | partner
--------------------
 1 |  2   |   2
 2 |  2   |   2
 3 |  2   |   3
 4 |  3   |   2

I know it's a little hart to keep track of all these numbers. I'm logged in as type 2(users table) and want to get listed all information from the orders table from the users with type 3 and which are listed as my partners (partner 2) in addition I want the company name from the users table also in my results. The query will be excecuted on the orders table.

我知道跟踪所有这些数字有点害怕。我以类型2(用户表)登录,并希望列出来自订单表中来自类型3的用户的所有信息,并列为我的合作伙伴(合作伙伴2),此外我想要来自用户的公司名称表也在我的结果中。查询将在订单表上进行。

result:

id | user | partner | company
------------------------------
 1 |  2   |   2     | comp2
 2 |  2   |   2     | comp2
 4 |  3   |   2     | comp3

Thanks in advance and ask me if you don't understand the problem. I will try to explain better and any edits to make it more clear are also welcomed.

提前谢谢,问我是否不明白这个问题。我将尝试更好地解释和任何编辑,以使其更清楚也欢迎。

1 个解决方案

#1

-1

Perhaps this

select s.id,
        case when src = 'u' then s.users
        else s.partner
        end as users,
        case when src = 'p' then s.users
        else s.partner
        end as partner,
        u.company
    from
   (
   select 'u' as src,id,users,partner from o where users = 2 and (users = partner)
   union
    select 'p',id,partner,users from o where partner = 2 and (users <> partner)
    ) s
    join u on u.id = s.partner
    where type = 3;

Hopefully self explanatory, note I only need to know the user, the query figures out the partners.

希望自我解释,注意我只需要知道用户,查询就会找出合作伙伴。

result

+------+-------+---------+---------+
| id   | users | partner | company |
+------+-------+---------+---------+
|    2 |     2 |       2 | comp2   |
|    1 |     2 |       2 | comp2   |
|    4 |     3 |       2 | comp3   |
+------+-------+---------+---------+
3 rows in set (0.00 sec)

#1

-1