dataworks插入,分组，排序，关联语句

– 向表中插入数据
– insert into/overwrite table 表名 values (…),(…),…;
– insert into/overwrite table 表名 select …;

– 创建样例表
create table if not exists temp_test_xsxx_30(
xh string comment ‘学号’,
xm string comment ‘姓名’,
xb string comment ‘性别’,
bjbh string comment ‘所属班级编号’,
rxrq string comment ‘入学日期’,
rxcj string comment ‘入学成绩’,
syd string comment ‘生源地’
)comment ‘测试插入非分区表’;

– 向非分区表中插入数据 insert into模式
insert into table temp_test_xsxx_30
VALUES(‘9211271028’,‘孙德邻’,‘女’,‘2013101050703415’,‘20131003’,375,‘沧州师范学院’),
(‘9002211017’,‘田普’,‘男’,‘2013101050703925’,‘20131003’,378,‘衡水学院’);
–查看该表中数据
select * from temp_test_xsxx_30;
– 显示表的详细结构信息
desc temp_test_xsxx_30;

– 覆盖写入
insert OVERWRITE table temp_test_xsxx_30
VALUES(‘8001203047’,‘吴丹’,‘女’,‘20131010507666’,‘20131008’,666,‘邯郸学院’);
–查看该表中数据
select * from temp_test_xsxx_30;
– 显示表的详细结构信息
desc temp_test_xsxx_30;

– 继续INSERT INTO 插入数据
INSERT INTO TABLE temp_test_xsxx_30
VALUES
(‘9108081022’,‘单英杰’,‘女’,‘2013101050702923’,‘20131005’,366,‘唐山师范学院’),
(‘9008216422’,‘刘岩’,‘男’,‘2013101050703903’,‘20131007’,365,‘河北农业大学’),
(‘9105250021’,‘魏浩璇’, ‘女’,‘2013101050703713’,‘20131007’,374,‘唐山师范学院’),
(‘9303260027’,‘刘海宽’,‘男’,‘2013101050703623’,‘20131004’,364,‘沧州师范学院’),
(‘9206247041’,‘李艳玲’,‘女’,‘2013101050702901’,‘20131004’,363,‘沧州师范学院’),
(‘9107231023’,‘崔娟’,‘女’,‘2013101050703419’,‘20131008’,383,‘廊坊师范学院’),
(‘9011133827’,‘董莉娜’,‘女’,‘2013101050702815’,‘20131008’,353,‘廊坊师范学院’);

– 读取表中数据select/read
read temp_test_xsxx_30;

– group by分组查询
select syd,avg(rxcj)as avg_score from temp_test_xsxx_30
group by syd;

– order by排序查询
select * from temp_test_xsxx_30 order by rxcj desc limit 5;

– join查询
– maxcomputer中提供join操作但不允许笛卡尔积即只能等值连接
select x.xh,x.xm,x.xb,x.bjbh,x.rxrq,b.bjmc
from xsxx x
inner join bjxx b
on x.bjbh=b.bjbh;