淘先锋技术网

首页 1 2 3 4 5 6 7

sql语句大集合练习

  • 数据表
    在这里插入图片描述
  • 任务
第一天
建表、数据加载
	•	创建一个数据库（接下来都在此库中操作），库命格式：Temp_ + 姓名拼音首字母
	•	根据“表.xlsx”中的信息来创建表（含主键）
	•	将四个Excel中的数据加载到对应的表中
	•	删除客户表中开户柜员号为空的数据


第二天
单表-条件查询
	•	查询客户表中，所有在2011/12/01及之后开户的客户所有信息
	•	查询客户表中，所有在2011/12/01（含）~2011/12/25（含）之间开户的客户所有信息
	•	查询客户表中，所有在2011/12/01、2011/12/20、2011/12/25这3天开户的客户所有信息
	•	查询客户表中，所有不在2011/12/01这1天开户的客户所有信息
	•	查询客户表中，所有不在2011/12/01、2011/12/20这2天开户的客户所有信息
	•	查询客户表中，所有在2011年12月开户且开户机构为7开头的客户信息（只要客户号、开户时间、开户机构这三个字段）
	•	查询客户表，客户号中含有数字“088”或者“188”的客户所有信息
单表-分组聚合
	•	查询客户表，统计总共有多少条记录
	•	查询客户表，统计总共有多少个机构、多少个柜员（1个机构号只算1个，1个柜员号也只算1个）
	•	查询客户表，根据机构号分组，统计每个机构的最早开户日期、最晚开户日期
	•	查询客户表，根据年份、机构号分组，统计每年、每个机构的开户数
	•	查询客户表，统计每个机构的加权开户数（2000之前开户的1户算2户，2000~2005期间开户的1户算1.5户，2006开始及之后开户的算1户）


第三天
单表-排序
	•	查询客户表，所有在2000年之后开户的客户所有信息，按开户机构（升序）、开户时间排序（降序）排序。
	•	查询客户表，给每个客户增加一个编号（根据开户日期排名，排名不能重复）
	•	查询客户表，按年份统计、机构开户数的排名（根据开户数降序排名，排名不能重复，展示字段：年份、机构号、开户数、排名）
查询（含子查询）
	•	查询客户表、存款信息表，查询有存款的客户信息（展示字段：客户号、客户名称，记录必须唯一）
	•	查询客户表、贷款信息表，查询无贷款的客户信息（展示字段：客户号、客户名称，记录必须唯一）
	•	查询客户表、存款信息表、贷款信息表，查询既有存款又有贷款的客户信息展示字段：客户号、客户名称，记录必须唯一）
表关联 
1、查询客户表、存款信息表，查询有存款的客户信息（展示字段：客户号、客户名称，账号、币种、开户日期）
2、查询客户表、存款信息表、贷款信息表，查询所有客户信息（展示字段：客户号、客户名称，账号、币种、开户日期、开户日期、借据号、贷款种类代码、所属账务机构，如果有null值则转换成空值）


第四天
表关联-分组聚合
	•	查询客户表、存款信息表、贷款信息表，统计每个客户的存款账号个数、贷款借据个数
（展示字段：客户号、客户姓名、存款账号个数、贷款借据个数，如果没有存款则存款账户个数置0，如果没有贷款则贷款借据号个数置0）

2、查询存款信息表、协议余额历史，统计客户号1035797902下每个帐号在2011.12.31这一天的余额
	（展示字段：客户号、存款账号、开户机构、开户日期、2011.12.31余额）

3、查询存款信息表、协议余额历史表，统计客户号1035797902下每个帐号在2011年12月的月日均（月日均等于当月每日余额累加除以当月天数）
（展示字段：客户号、存款账号、开户机构、开户日期、2011.12月日均）

4、查询客户号为1049193052的2011.12.31贷款时点余额（客户有多少个贷款帐号在贷款信息表中，每个贷款帐号的余额在协议余额历史表中Agmt_Type_Cd=’02’）
  • sql语句
/*第一天 */
use temp_zzy;
-- 删除客户表中开户柜员号为空的数据
delete from t01_customer where Cust_Id = null;

/*第二天*/
#单表-条件查询
-- 查询客户表中，所有在2011/12/01及之后开户的客户所有信息
select * from t01_customer where Open_Dt >= 20111201;
-- 查询客户表中，所有在2011/12/01（含）~2011/12/25（含）之间开户的客户所有信息
select * from t01_customer where Open_Dt between 20111201 and 20111225;
-- 查询客户表中，所有在2011/12/01、2011/12/20、2011/12/25这3天开户的客户所有信息
select * from t01_customer where Open_Dt in(20111201,20111220,20111225);
-- 查询客户表中，所有不在2011/12/01这1天开户的客户所有信息
select * from t01_customer where Open_Dt !=20111201;
-- 	查询客户表中，所有不在2011/12/01、2011/12/20这2天开户的客户所有信息
select * from t01_customer where Open_Dt not in(20111201,20111220);
-- 	查询客户表中，所有在2011年12月开户且开户机构为7开头的客户信息（只要客户号、开户时间、开户机构这三个字段）
select Cust_Id,Open_Org_Id,Open_Dt from t01_customer where Open_Dt like '201112%' and Open_Org_Id like '7%';
-- 查询客户表，客户号中含有数字“088”或者“188”的客户所有信息
select * from t01_customer where Cust_Id like '%088%' or Cust_Id like '%188%';
#单表-分组聚合
-- 查询客户表，统计总共有多少条记录
select * from t01_customer;
-- 查询客户表，统计总共有多少个机构、多少个柜员（1个机构号只算1个，1个柜员号也只算1个）
select distinct Open_Org_Id,Open_Teller_Id from t01_customer;
-- 查询客户表，根据机构号分组，统计每个机构的最早开户日期、最晚开户日期
select Open_Org_Id,min(Open_Dt),max(Open_Dt) from t01_customer group by Open_Org_Id ;
-- 查询客户表，根据年份、机构号分组，统计每年、每个机构的开户数
select substr(Open_Dt,1,4),count(Cust_Id) from t01_customer group by substr(Open_Dt,1,4),Open_Org_Id;

/*第三天*/
#单表-排序
-- 查询客户表，所有在2000年之后开户的客户所有信息，按开户机构（升序）、开户时间排序（降序）排序。
select * from t01_customer where Open_Dt > 20000000 order by Open_Org_Id asc,Open_Dt desc;
-- 	查询客户表，给每个客户增加一个编号（根据开户日期排名，排名不能重复）
Set @i:=0;
Select (@i:=@i+1) as RowNum,Cust_Id from t01_customer order by Open_Dt;
-- 查询客户表，按年份统计、机构开户数的排名（根据开户数降序排名，排名不能重复，展示字段：年份、机构号、开户数、排名）
select substr(Open_Dt,1,4) as Open_Id_year, Open_Org_Id,count(Cust_Id),ROW_NUMBER() OVER(order by count(Cust_Id) desc) as row_num
	from t01_customer 
    group by substr(Open_Dt,1,4),Open_Org_Id ;
#查询(子查询)
-- 查询客户表、存款信息表，查询有存款的客户信息（展示字段：客户号、客户名称，记录必须唯一）
select a.Cust_Id from t01_customer a join T01_Deposit_Acct b on a.Cust_Id = b.Cust_Id;
		-- 子查询方法（下面同理）
        select Cust_Id from t01_customer where Cust_Id = (select Cust_Id from T01_Deposit_Acct where Cust_Id = null);
-- 查询客户表、贷款信息表，查询无贷款的客户信息（展示字段：客户号、客户名称，记录必须唯一）
select a.Cust_Id from t01_customer a join T01_Loan_Iou b on a.Cust_Id != b.Cust_Id;
-- 查询客户表、存款信息表、贷款信息表，查询既有存款又有贷款的客户信息展示字段：客户号、客户名称，记录必须唯一）
select a.Cust_Id from t01_customer a join T01_Deposit_Acct b on a.Cust_Id = b.Cust_Id 
									 join T01_Loan_Iou c on a.Cust_Id = c.Cust_Id;
#表关联 
-- 查询客户表、存款信息表，查询有存款的客户信息（展示字段：客户号、客户名称，账号、币种、开户日期）
select a.Cust_Id from t01_customer a join T01_Deposit_Acct b on a.Cust_Id = b.Cust_Id;
-- 查询客户表、存款信息表、贷款信息表，查询所有客户信息（展示字段：客户号、客户名称，账号、币种、开户日期、开户日期、借据号、贷款种类代码、所属账务机构，如果有null值则转换成空值）
select * from t01_customer,T01_Deposit_Acct,T01_Loan_Iou;#把列名补上

/*第四天*/
#表关联-分组聚合
-- 查询客户表、存款信息表、贷款信息表，统计每个客户的存款账号个数、贷款借据个数
#（展示字段：客户号、客户姓名、存款账号个数、贷款借据个数，如果没有存款则存款账户个数置0，如果没有贷款则贷款借据号个数置0）
select case when count(b.Deposit_Acct_No)=0 then '0' else max(b.Deposit_Acct_No) end Deposit_Acct_No,
		case when count(c.Iou_No)=0 then '0' else max(c.Iou_No) end Iou_No,
        a.Cust_Id,count(b.Deposit_Acct_No)  ,count(c.IOU_No)
		from t01_customer a,T01_Deposit_Acct b ,T01_Loan_Iou c 
        group by a.Cust_Id;
-- 查询存款信息表、协议余额历史，统计客户号1035797902下每个帐号在2011.12.31这一天的余额
#（展示字段：客户号、存款账号、开户机构、开户日期、2011.12.31余额）
select Cust_Id,Deposit_Acct_No,Open_Org_Id,OPen_Dt,Bal as '2011.12.31余额'
		from 
			 T01_Deposit_Acct ,t01_agmt_bal_h
		where Cust_Id = '10357902' and Start_Dt = '20111231'and Agmt_Type_Cd = '01';
-- 查询存款信息表、协议余额历史表，统计客户号1035797902下每个帐号在2011年12月的月日均
#（月日均等于当月每日余额累加除以当月天数）（展示字段：客户号、存款账号、开户机构、开户日期、2011.12月日均）
select Cust_Id,Deposit_Acct_No,Open_Org_Id,OPen_Dt,sum(Bal)/31 as '2011.12日均'
		from 
				T01_Deposit_Acct ,t01_agmt_bal_h
		where 	Cust_Id = '1035797902' 
				and 
				Start_Dt like '201112%'
        group by 
				Deposit_Acct_No;
-- 查询客户号为1049193052的2011.12.31贷款时余额
#（客户有多少个贷款帐号在贷款信息表中，每个贷款帐号的余额在协议余额历史表中Agmt_Type_Cd=’02’）
select bal
		from 
				t01_agmt_bal_h ,T01_Loan_Iou 
        where 	Cust_Id = '1049193052' 
				and Start_Dt like '20111231' 
                and Agmt_Type_Cd = '02';