时间戳

首页 > 解决方案 > MySQL - 从 2 个表中选择，其中结果中的几列应基于条件语句

mysql - MySQL - 从 2 个表中选择，其中结果中的几列应基于条件语句

问题描述

亲爱的 MySQL/SQL 专家，

在以下情况下请求您的帮助。我有 2 张桌子，如下所述。 

TABLE1
+----+---------------------+---------------------+----------------+----------------+
|uid | active_hour_start   | active_hour_end     | activity_name  | activity_value |
+----+---------------------+---------------------+----------------+----------------+
| 01 | 2018-06-17 13:00:00 | 2018-06-17 14:00:00 | activity1      |             10 |
| 01 | 2018-06-17 13:00:00 | 2018-06-17 14:00:00 | activity2      |             15 |
| 01 | 2018-06-17 12:00:00 | 2018-06-17 13:00:00 | activity1      |             20 |
| 01 | 2018-06-17 12:00:00 | 2018-06-17 13:00:00 | activity2      |             30 |
| 01 | 2018-06-17 12:00:00 | 2018-06-17 13:00:00 | activity3      |             05 |
| 01 | 2018-06-17 11:00:00 | 2018-06-17 12:00:00 | activity4      |             55 |
| 01 | 2018-06-17 10:00:00 | 2018-06-17 11:00:00 | activity5      |             30 |
| 01 | 2018-06-17 10:00:00 | 2018-06-17 11:00:00 | activity1      |             25 |
| 01 | 2018-06-17 09:00:00 | 2018-06-17 10:00:00 | activity4      |             15 |
| 01 | 2018-06-17 09:00:00 | 2018-06-17 10:00:00 | activity5      |             35 |
+----+-------+---------+------------------+-------+---------------+----------------+

TABLE2
+----+---------------+---------------------+------------+-----------------+
|uid | user_name     | start_date_time     | parameter  | parameter_value |
+----+---------------+---------------------+------------+-----------------+
| 01 | s01234        | 2018-06-17 11:35:26 | status     | B               |
| 01 | s01234        | 2018-06-17 11:35:26 | type       | GOLD            |
| 01 | s01234        | 2018-06-16 08:15:36 | status     | X               |
| 01 | s01234        | 2018-06-16 08:45:36 | type       | SILVER          |
| 02 | b23456        | 2018-06-16 13:00:00 | status     | A               |
| 02 | b23456        | 2018-06-16 13:00:00 | type       | SILVER          |
+----+---------------+---------------------+------------+-----------------+

正如您在表 2 中所看到的，每次参数值更改时都会保留一条记录。我想从 2 个表中返回结果集，如下所示：

EXPECTED RESULTSET
+----+---------------------+---------------------+----------------+----------------+----------------+----------------+
|uid | active_hour_start   | active_hour_end     | activity_name  | activity_value | status         | type           |
+----+---------------------+---------------------+----------------+----------------+----------------+----------------+
| 01 | 2018-06-17 13:00:00 | 2018-06-17 14:00:00 | activity1      |             10 | B              | GOLD           | 
| 01 | 2018-06-17 13:00:00 | 2018-06-17 14:00:00 | activity2      |             15 | B              | GOLD           |
| 01 | 2018-06-17 12:00:00 | 2018-06-17 13:00:00 | activity1      |             20 | B              | GOLD           |
| 01 | 2018-06-17 12:00:00 | 2018-06-17 13:00:00 | activity2      |             30 | B              | GOLD           |
| 01 | 2018-06-17 12:00:00 | 2018-06-17 13:00:00 | activity3      |             05 | B              | GOLD           |
| 01 | 2018-06-17 11:00:00 | 2018-06-17 12:00:00 | activity4      |             55 | X              | SILVER         |
| 01 | 2018-06-17 10:00:00 | 2018-06-17 11:00:00 | activity5      |             30 | X              | SILVER         |
| 01 | 2018-06-17 10:00:00 | 2018-06-17 11:00:00 | activity1      |             25 | X              | SILVER         |
| 01 | 2018-06-17 09:00:00 | 2018-06-17 10:00:00 | activity4      |             15 | X              | SILVER         |
| 01 | 2018-06-17 09:00:00 | 2018-06-17 10:00:00 | activity5      |             35 | X              | SILVER         |
+----+-------+---------+------------------+-------+---------------+----------------+----------------+----------------+

结果集包含 TABLE1 中给定持续时间的所有行（基于 active_hour_start/active_hour_end 上的 where 子句）以及 2 个附加列，这些列包含基于表 2 的 start_date_time 的参数名称和值。

谢谢你。

标签： mysqlsqldatabase

解决方案

以下查询将返回所需的结果。

select
table1.uid,table1.active_hour_end,table1.activity_name,
table1.activity_name,tab2.status,tab2.type
from
table1 
inner join
    (
        SELECT
            A.uid,
            A.username,
            A.start_date_time,
            St.Status,
            t.type 
        from
            (
                select distinct
                    uid,
                    user_name,
                    start_date_time 
                from
                    table2
            )
            A 
            INNER JOIN
                (
                    select
                        uid,
                        start_date_time,
                        parameter_value as Status 
                    from
                        table2 
                    where
                        parameter = 'status' 
                )
                ST 
                on (A.uid = ST.uid 
                and A.start_date_time = st.start_date_time ) 
            INNER JOIN
                (
                    select
                        uid,
                        start_date_time,
                        parameter_value as type 
                    from
                        table2 
                    where
                        parameter = 'type' 
                )
                T 
                on (A.uid = T.uid 
                and A.start_date_time = T.start_date_time ) 
    )
    tab2 
    on (table1.uid = tab2.uid 
    and tab2.start_date_time >= table1.active_hour_start 
    and start_date_time <= active_hour_end)

推荐阅读