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 范围 2 插入：insert into table(field1,field2) values(value1,value2) 3 删除：delete from table where 范围 4 更新：update table set field1=value1 where ...
insert into(列名) select 列名 from 表名 where 条件 –不创建表，只复制表数据 select 列名 into 表名（这个表名是不存在的） from 表名 where 条件，–创建一张新表，只复制选择的列名字段数据 Insert是T-sql中...
insert into chanpin select b.productid,a.productname,count(quantity) from [OrderDetails] b,products a where a.productid=b.productid group by b.productid,a.productname 在员工表中找一个员工,删除这个...
选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * ...
选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 ...
select *, (select count(*) from student where cid = classes.id) as num from classes order by num; # in, not in子句查询示例 查询班级id大于小于的这些班级的学生信息 select * from student where cid ...
选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：...
选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * ...
insert into ntable select sname,sdept from student where sno in (select sno from sc where grade(grade)>=3); 总结： 1.通过这次实验我学会了SQL的常用数据更新操作。 2.能熟练应用INSERT,UPDATE,DELETE语句。...
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件 例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 4、子查询(表名1：a 表名2：b) select a,b,c from a where a IN ...
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件 例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 4、说明：子查询(表名1：a 表名2：b) select a,b,c from a where a ...
SQL>select * from dictionary where instr(comments,'index')>0; 如果我们想知道user_indexes表各字段名称的详细含义，可以用下面这条SQL语句: SQL>select column_name,comments from dict_columns where ...
-- SELECT * FROM student st WHERE id=ANY ( SELECT sc1.stu_id FROM score sc1 WHERE sc1.stu_id IN(SELECT sc.stu_id from score sc WHERE sc.c_name='计算机') and sc1.c_name='英语'); -- SELECT * FROM ...
INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON IF (NOT EXISTS (SELECT P.SSN FROM Person P, inserted I WHERE P.SSN = I.SSN)) INSERT INTO Person SELECT SSN,Name,Address,Birthdate,Comment FROM ...
选择：select * from table1 where 范围 插入：insert into table1(field1,field2) values(value1,value2) 删除：delete from table1 where 范围 更新：update table1 set field1=value1 where 范围 查找：select * ...
from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002'); 17、--按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生...
一、select用法示例 SELECT ST.STUDENT_ID, ST.STUDENT_NAME, ... WHERE ST.STUDENT_ID = #{studentID} 这条语句就叫做‘getStudent，有一个String参数，并返回一个StudentEntity类型的对象。
- SELECT *FROM text_file(data.txt) WHERE in_array( .line. ,array(1,2,3,4,5,6,)) -SELECT * FROM dir_files(doc).files WHERE name LIKE D3Linq - SELECT *FROM myArray.subitems WHERE name= Tufan - SELECT ...
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件 例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 4、说明：子查询(表名1：a 表名2：b) select a,b,c from a where a ...
选择查询: select 字段(以”,”隔开) from 表名 where 条件; 日期和时间: Select datetime('now') 日期: select date('now'); 时间: select time('now'); 总数：select count(*) from table1; 求和：select sum...