Mysql 编程实战二
2023-09-14 09:13:14 时间
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)
相关文章
- MySQL Error number: 3821; Symbol: ER_CHECK_CONSTRAINT_NOT_FOUND; SQLSTATE: HY000 报错 故障修复 远程处理
- MySQL参考书籍:实战技巧介绍(mysql参考书籍)
- Mysql授予角色权限的实战指南(mysql给权限命令)
- MySQL网络连接之Socket编程(mysql的socket)
- MySQL 字符串相加实现简单编程(mysql字符相加)
- MySQL中的二进制数据处理(mysql二进制数据)
- MySQL存储表情:实现简单又方便(mysql存表情)
- 最佳架构:MySQL环境优化实战(mysql环境要求)
- 深入解读MySQL中的主键字段类型(mysql主键字段类型)
- 如何在MySQL中删除数据库?(mysql怎样删除数据库)
- 使用VC编程操作MySQL数据库实现数据存储和查询(vcmysql编程)
- 如何快速安全地退出MySQL登录,教您MySQL退出登录的正确姿势,避免数据丢失和安全风险。(mysql退出登录)
- 索引MySQL哈希索引:解决高效查询的好方法(mysql哈希)
- C语言编程开发MySQL查看器(c mysql查看器)
- MySQL中使用C语言实现数据插入(c mysql 数据插入)
- C语言编程增加MySQL表中字段(c mysql增加字段)
- 在cmd中开启MySQL服务的指令步骤(cmd开启mysql服务)
- 用Net和MySQL实现软件开发编程(.net mysql编程)
- 使用C语言操作MySQL数据库(c 与 mysql数据库)
- 获取 MySQL 32 位安装程序下载的方法(mysql下载32bit)
- MySQL下拉框回显实现教程(mysql下拉框回显)