sql面试总结
--table create--create table zxl_tb_2012012401( user_id VARCHAR2(256), group_id VARCHAR2(256), user_num NUMBER not null);create table zxl_tb_2012012402( group_id VARCHAR2(256), group_name VARCHAR2(256));--data insert--insert into zxl_tb_2012012401 (USER_ID, GROUP_ID, USER_NUM)values ('0001', '01', 3000);insert into zxl_tb_2012012401 (USER_ID, GROUP_ID, USER_NUM)values ('0002', '02', 2000);insert into zxl_tb_2012012401 (USER_ID, GROUP_ID, USER_NUM)values ('0003', '01', 1000);insert into zxl_tb_2012012401 (USER_ID, GROUP_ID, USER_NUM)values ('0004', '02', 2500);insert into zxl_tb_2012012401 (USER_ID, GROUP_ID, USER_NUM)values ('0005', '01', 1500);insert into zxl_tb_2012012402 (GROUP_ID, GROUP_NAME)values ('01', '开发组');insert into zxl_tb_2012012402 (GROUP_ID, GROUP_NAME)values ('02', '维护组');--sql--select case when user_num<1500 then '0~1500' when user_num<2500 and user_num>=1500 then '1500~2500' when user_num<3500 and user_num>=2500 then '2500~3500' end aa, count(1) from zxl_tb_2012012401 group by case when user_num<1500 then '0~1500' when user_num<2500 and user_num>=1500 then '1500~2500' when user_num<3500 and user_num>=2500 then '2500~3500'end;select b.group_name,avg(a.user_num) from zxl_tb_2012012401 a, zxl_tb_2012012402 b where a.group_id = b.group_id group by a.group_id,b.group_name;--result--11500~2500 220~1500 132500~3500 2开发组1833.33333333333维护组2250