from dim.dim_wms_store a insert overwrite table test_20150609 select * where store_id=2 insert overwrite table test_201506092 select * where store_id=5
--------------------------------------------------
FROM
(
FROM
(
SELECT
id fact_chuku_id,
IF (
export_type = '6',
concat('C', id),
id
) fact_chuku_id_1,
export_state inner_delv_ob_status,
export_type inner_delv_biz_type_cd,
yn inner_delv_cancel_status,
store_from src_store_id,
org_from src_delv_center_num,
store_to target_store_id,
org_to target_delv_center_num,
create_date create_tm,
create_by create_stf_id,
validate_date validate_tm,
validate_by validate_stf_id,
complete_date send_tm,
substring(create_date, 1, 10) chuku_dt
FROM
fdm.fdm_newdeploy_chuku_chain
WHERE
start_date <= '2015-06-08'
AND end_date > '2015-06-08'
AND substring(create_date, 1, 10) >= '2014-12-10'
) fact_chuku
JOIN (
SELECT
chuku_id chuorders_chuku_id,
art_no chuorders_art_no,
max(art_name) item_name
FROM
fdm.fdm_newdeploy_chuorders_chain
WHERE
start_date <= '2015-06-08'
AND end_date > '2015-06-08'
GROUP BY
chuku_id,
art_no
) fact_chuorders
ON (
fact_chuku.fact_chuku_id = fact_chuorders.chuorders_chuku_id
)
LEFT OUTER JOIN (
SELECT
cast(item_sku_id AS BIGINT) sku_id,
item_first_cate_cd,
item_second_cate_cd,
item_third_cate_cd
FROM
gdm.gdm_m03_item_sku_da
WHERE
dt = '2015-06-08'
) fack_sku
ON (
fact_chuorders.chuorders_art_no = fack_sku.sku_id
)
LEFT OUTER JOIN (
SELECT
regexp_replace (max(crtdate), '/', '-') wms_rec_date,
expno expno_1,
regexp_replace (max(down_time), '/', '-') wms_rec_tm
FROM
fdm.fdm_wms2_cals_jd_export_chain
WHERE
start_date <= '2015-06-08'
AND end_date > '2015-06-08'
AND wh_code < 500
AND (
(
EXPTYPE = 'SEX'
AND ywtype <> 'T'
)
OR substr(expno, 1, 1) = 'C'
)
GROUP BY
expno
UNION ALL
SELECT
substr(create_time, 1, 10) wms_rec_date,
outbound_no expno_1,
create_time wms_rec_tm
FROM
fdm.fdm_yayi_report2_ob_internal_order_m_chain
WHERE
dp = 'ACTIVE'
AND yn = 0 -- yayi
UNION ALL
SELECT
substr(create_time, 1, 10) wms_rec_date,
outbound_no expno_1,
create_time wms_rec_tm
FROM
fdm.fdm_wms5_report_ob_internal_order_m_chain
WHERE
dp = 'ACTIVE'
AND yn = 0 -- wms5. 0
) fact_export
ON (
fact_chuku.fact_chuku_id_1 = fact_export.expno_1
)
LEFT OUTER JOIN (
SELECT
*
FROM
(
SELECT
id inner_delv_relation_id,
box_id,
chuku_id,
art_no item_sku_id,
art_num send_qty
FROM
fdm.fdm_newdeploy_send_relation_chain
WHERE
start_date <= '2015-06-08'
AND end_date > '2015-06-08'
) fact_relation
JOIN (
SELECT
id fact_box_id,
barcode inner_delv_box_id,
sendno inner_delv_send_id,
work_statue inner_delv_ib_status,
custom_no carrier_stf_id,
custom_name carrier_name,
transfer_no shipping_bill_id,
transferType inner_delv_ship_mode_cd
FROM
fdm.fdm_newdeploy_box_chain
WHERE
start_date <= '2015-06-08'
AND end_date > '2015-06-08'
) fact_box
ON (
fact_relation.box_id = fact_box.fact_box_id
)
LEFT OUTER JOIN (
SELECT
chukuid chukuid2,
box_no box_no2,
sku sku2,
max(operate_time) send_tm
FROM
fdm.fdm_newdeploy_status_wms_chain
WHERE
start_date <= '2015-06-08'
AND end_date > '2015-06-08'
AND status_wms = 8
GROUP BY
chukuid,
box_no,
sku
) fact_status
ON (
fact_box.inner_delv_box_id = fact_status.box_no2
AND fact_relation.chuku_id = fact_status.chukuid2
AND fact_relation.item_sku_id = fact_status.sku2
)
) fact_relation_box ON (
fact_chuorders.chuorders_chuku_id = fact_relation_box.chuku_id
AND fact_chuorders.chuorders_art_no = fact_relation_box.item_sku_id
) SELECT
inner_delv_relation_id,
fact_chuku_id inner_delv_id,
concat('P', box_id) inner_delv_into_wh_bill_id,
inner_delv_box_id,
inner_delv_send_id,
chuorders_art_no item_sku_id,
item_name,
item_first_cate_cd,
item_second_cate_cd,
item_third_cate_cd,
inner_delv_ob_status,
inner_delv_ib_status,
inner_delv_biz_type_cd,
inner_delv_cancel_status,
src_store_id,
src_delv_center_num,
target_store_id,
target_delv_center_num,
create_tm,
create_stf_id,
validate_tm,
validate_stf_id,
wms_rec_date,
wms_rec_tm,
fact_relation_box.send_tm,
send_qty,
carrier_stf_id,
carrier_name,
shipping_bill_id,
chuku_dt
) fact
INSERT overwrite TABLE gdm_m08_ob_inner_delv_sum PARTITION
(
dp = 'ACTIVE',
dt = '4712-12-31'
)
SELECT
inner_delv_relation_id,
inner_delv_id,
inner_delv_into_wh_bill_id,
inner_delv_box_id,
inner_delv_send_id,
item_sku_id,
item_name,
item_first_cate_cd,
item_second_cate_cd,
item_third_cate_cd,
inner_delv_ob_status,
inner_delv_ib_status,
inner_delv_biz_type_cd,
inner_delv_cancel_status,
src_store_id,
src_delv_center_num,
target_store_id,
target_delv_center_num,
create_tm,
create_stf_id,
validate_tm,
validate_stf_id,
wms_rec_date,
wms_rec_tm,
send_tm,
send_qty,
carrier_stf_id,
carrier_name,
shipping_bill_id
WHERE
chuku_dt > '2014-12-10' INSERT overwrite TABLE gdm_m08_ob_inner_delv_sum PARTITION (
dp = 'HISTORY',
dt = '2015-06-08'
)
SELECT
inner_delv_relation_id,
inner_delv_id,
inner_delv_into_wh_bill_id,
inner_delv_box_id,
inner_delv_send_id,
item_sku_id,
item_name,
item_first_cate_cd,
item_second_cate_cd,
item_third_cate_cd,
inner_delv_ob_status,
inner_delv_ib_status,
inner_delv_biz_type_cd,
inner_delv_cancel_status,
src_store_id,
src_delv_center_num,
target_store_id,
target_delv_center_num,
create_tm,
create_stf_id,
validate_tm,
validate_stf_id,
wms_rec_date,
wms_rec_tm,
send_tm,
send_qty,
carrier_stf_id,
carrier_name,
shipping_bill_id
WHERE
chuku_dt <= '2014-12-10'
相关推荐
例如，`SELECT * FROM table WHERE 范围`表示从`table`表中选择所有列，并通过`范围`条件过滤结果集。 #### INSERT 语句 - **语法**：`INSERT INTO 表 (field1, field2) VALUES (value1, value2)` - **说明**：用于...
在数据库管理与操作中，`SELECT INTO`与`INSERT INTO SELECT FROM`是两种常见的用于复制或创建新表的方式。这两种语句虽然相似，但在实际使用场景中有着不同的作用和限制条件。 #### 1. `SELECT INTO` `SELECT ...
- 使用格式为：`SELECT 列名 FROM 表名 [WHERE 条件];` **示例**： ```sql SELECT BRANCH_OFFICE FROM EMPLOYEES; ``` 这条语句会返回`EMPLOYEES`表中所有员工所在的办公室名称。 **高级特性**： - **DISTINCT** ...
在这种情况下，`SELECT INTO` 和 `INSERT INTO...SELECT` 语句是常用的工具，可以将数据从一个源转换为插入语句。本文将详细介绍如何使用这些方法，并提供一个实用的小工具——SelectInsertTable，帮助用户更方便地...
insert into(列名) select 列名 from 表名 where 条件 –不创建表，只复制表数据 select 列名 into 表名（这个表名是不存在的） from 表名 where 条件，–创建一张新表，只复制选择的列名字段数据 Insert是T-sql中...
本文主要探讨了两种不同的插入数据的方法：`INSERT INTO...VALUES` 和 `INSERT INTO...SELECT`，并以MSSQL（Microsoft SQL Server）为例，分析它们在性能上的差异。 首先，`INSERT INTO...VALUES` 语句用于向表中...
INSERT INTO db1_name(field1, field2) SELECT field1, field2 FROM db2_name; ``` 然而，当涉及到多个表时，我们需要使用`JOIN`操作来合并来自不同表的数据。假设我们有表`b`的`f1`字段和表`c`的`f2`字段需要插入...
在SQL中，表复制是常见的数据操作，主要通过两种语句来实现：`SELECT INTO` 和 `INSERT INTO SELECT`。这两种语句在SQL数据库和Oracle数据库中略有不同，下面将详细解析它们的工作原理和使用场景。 1. `INSERT INTO...
下面将详细讲解标题和描述中提到的基础SQL语句：SELECT、INSERT和UPDATE。 1. **SELECT语句**：这是SQL中最常用的语句，用于从数据库中检索数据。其基本语法是： ```sql SELECT column1, column2, ... FROM ...
修正后的解决方案是在SELECT查询中为结果集添加别名，使得SELECT的结果可以作为INSERT语句的一个值。这样，查询和插入操作在逻辑上是分离的，避免了MySQL的限制。修改后的XML映射文件如下： ```xml <insert id=...
例如，`PreparedStatement ps = conn.prepareStatement("SELECT * FROM table_name WHERE column = ?");` 4. 设置参数：如果使用`PreparedStatement`，可以使用`setXXX()`方法设置参数，如`ps.setString(1, "value...
update emp set sal=sal*1.05 where job = (select job from emp where ename='SCOTT'); 该语句将更新emp表中job字段等于SCOTT的记录的sal字段。 11、模糊查询 select * from emp where ename like 'S%'; 该...
1. `INSERT INTO <表名>(列名) SELECT <列名> FROM <源表名>`，例如：`INSERT INTO TongXunLu (姓名,地址,电子邮件) SELECT SName,SAddress,SEmail FROM Students`。 2. 使用 `SELECT INTO` 语句，可以插入新的标识...
INSERT INTO tab1 SELECT * FROM tab2; COMMIT; ``` 这种操作适用于百万至千万级别的数据，可以在一小时内完成。然而，这种方法会导致归档日志（ARCH）快速生成，因此需要密切关注归档空间并及时备份，防止磁盘空间...
虽然在提供的内容中没有详细解释SELECT语句，但通常包括列名、FROM子句（指定要查询的表）、WHERE子句（定义筛选条件）、ORDER BY子句（用于排序）和GROUP BY子句（用于分组）等。 3. UPDATE语句： UPDATE语句用于...
`INSERT INTO SELECT`的一个显著优点是它支持复杂的查询，可以结合`WHERE`子句筛选数据，甚至可以从多个表中选取数据。同时，可以指定插入特定列，这在目标表结构与源表不完全匹配时尤其有用。 而`SELECT INTO`则更...
- `SELECT * FROM 表名 WHERE 字段名 = 字段值 ORDER BY 字段名 [DESC]`：此语句用于从指定的表中选择所有列，并根据给定的条件筛选记录。`WHERE`子句用于指定筛选条件，`ORDER BY`则用来对结果进行排序。 - 示例...