sql面试总结

最新推荐文章于 2025-08-09 23:51:15 发布

原创最新推荐文章于 2025-08-09 23:51:15 发布 · 96 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#面试

数据库同时被 2 个专栏收录

10 篇文章

订阅专栏

3 篇文章

订阅专栏

本文通过创建两个表格并插入数据，演示了如何使用SQL进行数据分组及聚合计算。具体包括按条件分组计数及按部门计算平均值等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


--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--
1	1500~2500	         2
2	0~1500	         1
3	2500~3500 	2

开发组	1833.33333333333
维护组	2250