Mysql 编程实战二_车势科技mysql-CSDN博客

本文详细介绍了在Mysql中创建dim_shop表的方法，包括表结构定义及数据插入。并通过多个测试案例展示了如何使用Join语句进行数据关联，包括获取所有可能的配对、最大值和最小值的日期范围，为数据库操作提供了实用的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Mysql 编程实战二

创建表dim_shop

 CREATE TABLE dim_shop (
  shop_key bigint(20) NOT NULL AUTO_INCREMENT COMMENT 's_poi.id',
  shop_id int(11) NOT NULL,
  value_date datetime DEFAULT '9999-12-31 00:00:00' COMMENT ' gmt_create from stagedb',
  PRIMARY KEY (shop_key),
  UNIQUE KEY uidx_dim_shop_id (shop_id,value_date),
  UNIQUE KEY uidx_dim_shop_no (value_date)
) ENGINE=InnoDB AUTO_INCREMENT=409557 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

往表中插入数据

INSERT INTO mydatabase.dim_shop (shop_key, shop_id, value_date) 
VALUES ('41735', '297', '2017-05-26 12:17:21')，
('279978', '297', '2018-03-07 10:10:03')，
('378768', '297', '2018-08-31 13:34:14');

测试join语句
- 测试1

 select
    pre.shop_id as shop_id,
    pre.value_date as start_date,
    cur.value_date as end_date
from mydatabase.dim_shop pre
join mydatabase.dim_shop cur
on cur.shop_id = pre.shop_id;
+---------+---------------------+---------------------+
| shop_id | start_date          | end_date            |
+---------+---------------------+---------------------+
|     297 | 2017-05-26 12:17:21 | 2017-05-26 12:17:21 |
|     297 | 2017-05-26 12:17:21 | 2018-03-07 10:10:03 |
|     297 | 2017-05-26 12:17:21 | 2018-08-31 13:34:14 |
|     297 | 2018-03-07 10:10:03 | 2017-05-26 12:17:21 |
|     297 | 2018-03-07 10:10:03 | 2018-03-07 10:10:03 |
|     297 | 2018-03-07 10:10:03 | 2018-08-31 13:34:14 |
|     297 | 2018-08-31 13:34:14 | 2017-05-26 12:17:21 |
|     297 | 2018-08-31 13:34:14 | 2018-03-07 10:10:03 |
|     297 | 2018-08-31 13:34:14 | 2018-08-31 13:34:14 |
+---------+---------------------+---------------------+
9 rows in set (0.00 sec)

这么看，就得到了9个结果集。【3*3】

测试2

select
pre.shop_id AS shop_id,
pre.value_date AS start_date,
max(cur.value_date) AS end_date 
    from (mydatabase.dim_shop pre 
        join mydatabase.dim_shop cur
        on((cur.shop_id = pre.shop_id))
        );  
+---------+---------------------+---------------------+
| shop_id | start_date          | end_date            |
+---------+---------------------+---------------------+
|     297 | 2017-05-26 12:17:21 | 2018-08-31 13:34:14 |
+---------+---------------------+---------------------+
1 row in set (0.00 sec)

这里的join结果只有1条，是因为是用的是max(cur.value_date) as end_date。所以结果只有1条。

测试3

select
    pre.shop_id AS shop_id,
    pre.value_date AS start_date,
    min(cur.value_date) AS end_date 
from mydatabase.dim_shop pre 
    join mydatabase.dim_shop cur
    on((cur.shop_id = pre.shop_id))                                        
where (pre.value_date < cur.value_date) 
group by pre.shop_id,pre.value_date ;

下面这个SQL语句与上面是等效的：

select
    pre.shop_id AS shop_id,
    pre.value_date AS start_date,
    min(cur.value_date) AS end_date 
from mydatabase.dim_shop pre 
    join mydatabase.dim_shop cur
    on((cur.shop_id = pre.shop_id))
where (pre.value_date < cur.value_date) 
group by pre.shop_id,pre.value_date;

执行结果

+---------+---------------------+---------------------+
| shop_id | start_date          | end_date            |
+---------+---------------------+---------------------+
|     297 | 2017-05-26 12:17:21 | 2018-03-07 10:10:03 |
|     297 | 2018-03-07 10:10:03 | 2018-08-31 13:34:14 |
+---------+---------------------+---------------------+
2 rows in set (0.00 sec)