1.先创建表格test_order,再在表格里插入数据。
新建表 test_order有以下字段
product_id varchar2 --主键
user_name varchar2 --姓名
phone_num number --电话号码
address varchar2 --地址
order_date date --下单时间
delivery_date date --发货时间
创建表格:
create table test_order(product_id varchar(200),user_name varchar2(200),phone_num number(28),address varchar2(200),order_date date,delivery_date date);
往表里插入数据(6条):
insert into test_order values('12345','小敏',280006,'理想国',to_date('2022/12/31 23:00:00','yyyy/mm/dd hh24:mi:ss'),to_date('2088/12/31 23:50:59','yyyy/mm/dd hh24:mi:ss'));
insert into test_order values('12346','小敏',280005,'理想国',to_date('2022/12/30 23:00:00','yyyy/mm/dd hh24:mi:ss'),to_date('2024/12/31 23:50:59','yyyy/mm/dd hh24:mi:ss'));
insert into test_order values('12347','小明',280007,'聪明国',to_date('2022/12/21 23:00:00','yyyy/mm/dd hh24:mi:ss'),to_date('2024/12/31 23:50:59','yyyy/mm/dd hh24:mi:ss'));
insert into test_order values('12348','小李',280008,'颠倒国',to_date('2022/12/11 23:00:00','yyyy/mm/dd hh24:mi:ss'),to_date('2024/12/31 23:50:59','yyyy/mm/dd hh24:mi:ss'));
insert into test_order values('12349','小张',260008,'邋遢国',to_date('2022/12/31 23:00:00','yyyy/mm/dd hh24:mi:ss'),to_date('2088/12/31 23:50:59','yyyy/mm/dd hh24:mi:ss'));
insert into test_order values('12349','小丽',280008,'美丽国',to_date('2022/6/30 23:00:00','yyyy/mm/dd hh24:mi:ss'),to_date('2023/12/31 23:50:59','yyyy/mm/dd hh24:mi:ss'));
对表做以下查询操作:
1、请用sql找出该表所有用户;
select distinct user_name from test_order;
2.请用sql找出该表的用户有几个
select count(distinct user_name) from test_order;
3、请用sql找出该表的重复用户
select user_name from test_order a group by a.user_name having count(1)>1;
4、请用sql找出该表的重复用户有几个;
select count(user_name) from test_order a group by a.user_name having count(1)>1;
5、请用sql找出该表用户发货时间最早/晚的数据;
select max(delivery_date) from test_order;
select min(delivery_date) from test_order;
6、请用sql找出该表用户下单时间相同的所有数据
select * from test_order a where a.order_date=(select order_date from test_order a group by order_date having count(1)>1);
7、按发货时间排序
select a.* from test_order a order by delivery_date;
8、按发货时间降序排序
select a.* from test_order a order by delivery_date desc;
好了,接下来的相关需求以及查询SQL就交给你们吧,如果你对Oracle函数感兴趣,
可点击:字符函数,数字函数,日期函数,类型转换函数了解详情。