目录
FineReport学习计划之SQL上篇
前言
来源于FineReport学习计划表 https://bbs.fanruan.com/thread-77147-1-1.html 中的SQL自学部分。
这部分题目,网上没有答案,官方也明确不会提供答案。
作为一个曾经已经SQL入门过了的人再来撸这套题,相对容易些。既然撸出来了,就来发个帖,一为知识分享,二为记录备忘。
零基础快速自学SQL(1~2天)https://bbs.fanruan.com/task-94.html
总结
- 有一题未完成,结果明显不大对。先战略性放弃。
(1)题目:综合查询 之 统计1997年上半年的每份订单上所订购的产品的总数量。
(2)解题结果分析:我的答题结果,加了1997的时间限制条件和不加时间限制条件,查询结果都是2157条,应该有误。
(3)心得体会及下一步计划:由于后面还得撸finereport可视化,所以这个就先不纠结了。兵法应有云,不应计较一城一池的得失,果断战略性放弃。(当然也是可以找高手同事帮忙解决,这个容后再议)
(4)答题过程:
①未加订购日期条件(无法传图,可自行跑数尝试)
-- 该语句查询结果2157条
SELECT ddmx.订单ID,ddmx.产品ID,cp.产品名称,sum(数量) as 总数量
FROM `订单明细` as ddmx,`产品`as cp,`订单` as dd
WHERE ddmx.产品ID = cp.产品ID
GROUP BY ddmx.订单ID,ddmx.产品ID
②增加订购日期条件(无法传图,可自行跑数尝试)
-- 该语句增加了订购日期条件,但查询结果还是2157条,应该有误
SELECT ddmx.订单ID,ddmx.产品ID,cp.产品名称,sum(数量) as 总数量
FROM `订单明细` as ddmx,`产品`as cp,`订单` as dd
WHERE ddmx.产品ID = cp.产品ID AND LEFT(订购日期,4)='1997' AND MONTH(订购日期)<=6
GROUP BY ddmx.订单ID,ddmx.产品ID
2. 同样的需求,可通过不同的语句来实现。有些语句看起来更优雅更赏心悦目,有些语句运行起来更快更节约性能,这些就是“剑术”高低之分了。
下面这个例子,请教了高手同事,用了left outer join 之后,发现前面几道题也都是可以通过这个join来实现的,高手同事的“join”看起来比我的“select 多表字段” 更简洁,而且更易于维护变更(不会看到眼花不知从何改起)。
例子:
--按运货商公司名称,统计1997年由各个运货商承运的订单的总数量
-- (1)先计算“订单”表中各个运货商的总订单数
SELECT 运货商, SUM(num) as 总订单数 from (SELECT 运货商,订单ID,count(1) as num FROM `订单` group by 运货商,订单ID) a
或
SELECT 运货商, COUNT(1) as 总订单数 from `订单` group by 运货商
-- (2)回到题干
select t1.运货商,t2.公司名称,min(货主城市),count(1)
from `订单` t1
left outer join `运货商` t2 on t1.运货商=t2.运货商ID
where LEFT(t1.订购日期,4)='1997'
group by t1.运货商,t2.公司名称
题目及答题过程
单表查询
--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区,另外一些供应商所在的城市是天津
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
多表查询
--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的值,并将查询结果按雇员的“姓氏”和“名字”字段的升序排列,“姓氏”和“名字”值相同的记录按“订单 ID”的降序排列
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称及其销售金额
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
综合查询
--查询所有运货商的公司名称和电话
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询所有客户的公司名称、电话、传真、地址、联系人姓名和联系人头衔
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询单价介于10至30元的所有产品的产品ID、产品名称和库存量
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询单价大于20元的所有产品的产品名称、单价以及供应商的公司名称、电话
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询上海和北京的客户在1996年订购的所有订单的订单ID、所订购的产品名称和数量
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--查询华北客户的每份订单的订单ID、产品名称和销售金额
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--按运货商公司名称,统计1997年由各个运货商承运的订单的总数量
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--统计1997年上半年的每份订单上所订购的产品的总数量
(未完成)
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--统计各类产品的平均价格
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
--统计各地区客户的总数量
(图片上传失败,建议直接看《后记》部分的解答结果SQL查询语句)
后记:汇总本次答题所使用过的语句(备忘查询)
select * from `产品`
select * from `订单`
select * from `订单明细`
select * from `供应商`
select * from `雇员`
select * from `客户`
select * from `类别`
select * from `运货商`
一、单表查询
--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值
SELECT 订购日期,订单ID,客户ID,雇员ID FROM `订单`
WHERE 订购日期 > '1996-07-01 00:00:00' AND 订购日期 < '1996-07-15 25:59:59'
--查询供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。
SELECT * FROM `供应商` -- 先查看表格信息,获取准确的字段名
SELECT 供应商ID,公司名称,地区,城市,电话 FROM `供应商`
WHERE 地区 = '华北' AND 联系人职务 = '销售代表'
--查询供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区,另外一些供应商所在的城市是天津
SELECT DISTINCT 地区,count(1) as a FROM `供应商` group by 地区 ORDER BY a DESC -- 先查看表格字段信息,获取准确的字段值(方法一)
SELECT 地区,count(1) num FROM `供应商` group by 地区 ORDER BY num DESC -- 先查看表格字段信息,获取准确的字段值(方法二)
SELECT 供应商ID,公司名称,地区,城市,电话 FROM `供应商`
WHERE 地区 = '华东' or 地区 = '华南'
union ALL
SELECT 供应商ID,公司名称,地区,城市,电话 FROM `供应商`
WHERE 城市 = '天津'
--查询位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值
SELECT 供应商ID,公司名称,地区,城市,电话 FROM `供应商`
WHERE 地区 = '华东' or 地区 = '华南'
二、多表查询
--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的值,并将查询结果按雇员的“姓氏”和“名字”字段的升序排列,“姓氏”和“名字”值相同的记录按“订单 ID”的降序排列
(1)先查看字段名,取“订单”和“雇员”两张表格的字段名信息
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = n'订单' OR table_name = n'雇员'
或
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name in('订单','雇员')
(2)发现“订单”和“雇员”两张表都没有题目要求的“相应订单的客户公司名称”这个字段,那就把所有表都读出来看看这个字段在哪(后面看到是在“客户”这张表)
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name in('产品','订单','订单明细','供应商','雇员','客户','类别','运货商')
(3)查看题目要求的各字段的值
SELECT dd.订购日期,dd.订单ID,kh.公司名称,gy.姓氏 as 雇员姓氏, gy.名字 as 雇员名字
FROM `订单` as dd,`雇员` as gy, `客户` as kh
WHERE dd.雇员ID = gy.雇员ID and dd.客户ID = kh.客户ID AND 订购日期 > '1996-07-01 00:00:00' AND 订购日期 < '1996-07-15 25:59:59'
ORDER BY 雇员姓氏,雇员名字,订单ID DESC
--查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称
SELECT dd.订单ID,yhs.公司名称,cp.产品名称
FROM `订单` as dd,`客户` as kh,`运货商` as yhs,`产品` as cp,`订单明细` as ddmx
WHERE dd.订单ID = ddmx.订单ID and ddmx.产品ID = cp.产品ID and dd.运货商 = yhs.运货商ID AND dd.订单ID in ('10248','10254')
--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name in('产品','订单','订单明细','供应商','雇员','客户','类别','运货商')
-- 为了方便,本次用不上的供货商等表格没有删掉,留着下一题可能还用到。
SELECT dd.订单ID,cp.产品名称,ddmx.数量,ddmx.单价,ddmx.折扣
FROM `订单` as dd,`客户` as kh,`运货商` as yhs,`产品` as cp,`订单明细` as ddmx
WHERE dd.订单ID = ddmx.订单ID and ddmx.产品ID = cp.产品ID and dd.运货商 = yhs.运货商ID AND dd.订单ID in ('10248','10254')
--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称及其销售金额
-- 测试【exp(sum(log(ddmx.数量,ddmx.单价,ddmx.折扣))) as 销售金额】不可用,报错 [Err] 1582 - Incorrect parameter count in the call to native function 'log'
SELECT dd.订单ID,cp.产品名称,ddmx.数量*ddmx.单价*(1-ddmx.折扣) as 销售金额
FROM `订单` as dd,`客户` as kh,`运货商` as yhs,`产品` as cp,`订单明细` as ddmx
WHERE dd.订单ID = ddmx.订单ID and ddmx.产品ID = cp.产品ID and dd.运货商 = yhs.运货商ID AND dd.订单ID in ('10248','10254')
ORDER BY 销售金额 DESC
三、综合查询
--查询所有运货商的公司名称和电话
SELECT 公司名称,电话 FROM `运货商`
--查询所有客户的公司名称、电话、传真、地址、联系人姓名和联系人头衔
SELECT * FROM `客户`
SELECT 公司名称,电话,传真,地址,联系人姓名,联系人职务 FROM `客户`
--查询单价介于10至30元的所有产品的产品ID、产品名称和库存量
SELECT * FROM `产品`
SELECT 产品ID,产品名称,库存量 FROM `产品`
WHERE 单价 >= '10' AND 单价 <='30'
--查询单价大于20元的所有产品的产品名称、单价以及供应商的公司名称、电话
-- 以下语句无法运行,报错 [Err] Out of memory。推测可能是因为多关联了无关的`订单``客户``订单明细``运货商`等4张表.
-- SELECT cp.产品名称,cp.单价,gys.公司名称,gys.电话
-- FROM `订单` as dd,`客户` as kh,`供应商` as gys,`产品` as cp,`订单明细` as ddmx,`运货商` as yhs
-- WHERE gys.供应商ID = cp.供应商ID AND cp.单价 > 20
-- 以下语句运行正常
SELECT cp.产品名称,cp.单价,gys.公司名称,gys.电话
FROM `供应商` as gys,`产品` as cp
WHERE gys.供应商ID = cp.供应商ID AND cp.单价 > 20
--查询上海和北京的客户在1996年订购的所有订单的订单ID、所订购的产品名称和数量
SELECT dd.订单ID,cp.产品名称,ddmx.数量
FROM `订单` as dd,`客户` as kh,`运货商` as yhs,`产品` as cp,`订单明细` as ddmx
WHERE dd.订单ID = ddmx.订单ID and ddmx.产品ID = cp.产品ID AND LEFT(dd.订购日期,4)='1996'
--查询华北客户的每份订单的订单ID、产品名称和销售金额
SELECT dd.订单ID,cp.产品名称,ddmx.数量*ddmx.单价*(1-ddmx.折扣) as 销售金额
FROM `订单` as dd,`客户` as kh,`运货商` as yhs,`产品` as cp,`订单明细` as ddmx
WHERE dd.订单ID = ddmx.订单ID and ddmx.产品ID = cp.产品ID and dd.客户ID = kh.客户ID AND kh.地区='华北'
--按运货商公司名称,统计1997年由各个运货商承运的订单的总数量
-- (1)先计算“订单”表中各个运货商的总订单数
SELECT 运货商, SUM(num) as 总订单数 from (SELECT 运货商,订单ID,count(1) as num FROM `订单` group by 运货商,订单ID) a
或
SELECT 运货商, COUNT(1) as 总订单数 from `订单` group by 运货商
-- (2)回到题干
select t1.运货商,t2.公司名称,min(货主城市),count(1)
from `订单` t1
left outer join `运货商` t2 on t1.运货商=t2.运货商ID
where LEFT(t1.订购日期,4)='1997'
group by t1.运货商,t2.公司名称
--统计1997年上半年的每份订单上所订购的产品的总数量
-- 该语句查询结果2157条
SELECT ddmx.订单ID,ddmx.产品ID,cp.产品名称,sum(数量) as 总数量
FROM `订单明细` as ddmx,`产品`as cp,`订单` as dd
WHERE ddmx.产品ID = cp.产品ID
GROUP BY ddmx.订单ID,ddmx.产品ID
-- 该语句增加了订购日期条件,但查询结果还是2157条,应该有误
SELECT ddmx.订单ID,ddmx.产品ID,cp.产品名称,sum(数量) as 总数量
FROM `订单明细` as ddmx,`产品`as cp,`订单` as dd
WHERE ddmx.产品ID = cp.产品ID AND LEFT(订购日期,4)='1997' AND MONTH(订购日期)<=6
GROUP BY ddmx.订单ID,ddmx.产品ID
--统计各类产品的平均价格
SELECT 产品ID,产品名称,AVG(单价) FROM `产品`
GROUP BY 产品ID,产品名称
--统计各地区客户的总数量
SELECT 地区,COUNT(1) 总数量
FROM `客户`
GROUP BY 地区
ORDER BY 总数量 DESC