帆软练习题

减肥中的小南瓜

已于 2022-08-15 15:05:42 修改

阅读量4.7k

点赞数 1

分类专栏：帆软学习文章标签： sql

于 2022-05-26 15:26:36 首次发布

本文链接：https://blog.csdn.net/weixin_44342318/article/details/124985472

版权

帆软学习专栏收录该内容

1 篇文章 6 订阅

订阅专栏

1、基本原理理解

使用SALES_BASIC表制作明细表、分组报表、交叉报表以了解数据显示方式（分组、列表、求和）；数据的扩展（横向与纵向）；父格的含义。

说明：不规定模板样式，按自己的理解做出对应的3张报表，做好后说出自己对原理的理解

--region, salesperson, product_type, sales_volume ,SALES_BASIC 销售基础
select 地区 region,销售员 salesperson,产品类型 product_type,销量 sales_volume  from SALES_BASIC

多源报表

模板效果：

第一页：

第二页：

实现功能：

多源报表：供应商与所在地区来源于表PRODUCER

明细来源于表PRODUCT

分组报表：按组显示数据，每个供应商对应其产品明细

按组分页：每个供应商一页

表头重复：

数据字典：供应商与产品名称实际值为id，显示值为名称

使用数据：

FRDemo中的PRODUCER表与PRODUCT

--PRODUCER 供应商,PRODUCT 产品
--PRODUCERID供应商ID，PRODUCERNAME供应商姓名，CITY城市，COUNTRY国家，PRODUCTID供应商ID，PRODUCTNAME产品名称，PRODUCERID生产商，PRODUCTTYPE产品类型，COST成本，QUANTITY数量
select 
PRODUCERNAME,CITY,COUNTRY,PRODUCTID,PRODUCTNAME,PRODUCTTYPE,COST,QUANTITY
,COUNTRY||'-'||CITY COUNTRYCITY
,COST * QUANTITY amount
 from PRODUCER A
LEFT JOIN PRODUCT B
ON A.PRODUCERID = B.PRODUCERID

动态格间运算

提前学习1：

字段名	公式
比较	比较=本期应付金额-01月份的应付金额将单元格扩展出的每个值与第一个值进行比较，示例中为求差值。
占比	占比=本期应付金额/所有月份应付金额计算单元格扩展出的每个值在总量中占据的比例。
环比	计算每行数据与上一行数据的比率。环比（发展速度）=本期应付金额/上期应付金额

在 C2 单元格写入公式 B2 - B2[A2:1]，计算 B2 单元格扩展出来的数据，每一行与第一行的差值

公式	说明
B2	本期应付金额
B2[A2:1]	01月份的应付金额，即返回 A2 列的第一个数值对应的 B2 单元格数值
B2 - B2[A2:1]	本期应付金额-01月份的应付金额

在 D2 单元格写入公式B2 / SUM(B2[!0])，计算 B2 单元格扩展出来的数据，每一行在总值中的占比

公式	说明
B2	本期应付金额
B2[!0]	返回 B2 扩展出来的所有值，即3949.70，2293.65，2310.21，2116.69，1346.74，4780.93 详情参见：层次坐标常用公式
SUM(B2[!0])	对返回的所有 B2 值求和，也就是所有月份应付金额总计
B2 / SUM(B2[!0])	计算每月应付金额占总计额比重

在 E2 单元格写入公式IF(&A2 > 1, B2 / B2[A2:-1], 0)，计算 B2 单元格扩展出来的数据，每一行与上一行的比率

公式	说明
&A2	获取单元格 A2 扩展后每个值对应的位置
B2 / B2[A2:-1]	B2[A2:-1]：返回上一个月的应付金额 B2 / B2[A2:-1]：本月应付金额/上月应付金额
IF(&A2 > 1, B2 / B2[A2:-1], 0)	如果不是序号为1 ，即第一个单元格，则用，如果是第一个单元格，则输出 0

提前学习2

字段名	公式
逐层累计	按照年份分组，计算每一年月份应付金额的累加分组报表中，每一组数据逐行累计。
跨层累计	计算所有年月应付金额累加分组报表中，所有数据逐行累计，跨组时接着上一组累计结果继续累计。
条件汇总	对符合条件的数据进行汇总

逐层累计：C2 + D2[B2:-1]

公式	说明
D2[B2:-1]	每次返回当年逐层累计列的上一个数值比如：2010年1月返回0；2010年2月返回 1月份对应的 D2 的值 2837.94
C2 + D2[B2:-1]	当前年月的应付金额+上一个逐层累计列单元格数值

跨层累计：IF(&B2 > 1, C2 + E2[B2:-1], C2 + E2[A2:-1,B2:!-1])

公式	说明
&B2	获取单元格 B2 扩展后每个值对应的位置注：B2 的位置根据左父格年份从头累计例如：2011年01月份返回的为 1 ；2010 年01月份返回的也为 1 详情参见：层次坐标常用公式
E2[B2:-1]	每次返回当年逐层累计列的上一个数值比如：2010年1月返回0；2010 年 2 月返回 1月份对应的 E2 的值 2837.94 详情参见：层次坐标
E2[A2:-1,B2:!-1]	对每年的01月份数据，都返回上一年的最后一个月份的跨层累计数据例如：2011年01月的跨层累计返回 2010年06月的跨层累计数据即 15631.50
IF(&B2 > 1, C2 + E2[B2:-1], C2 + E2[A2:-1,B2:!-1])	如果不是每年的第一个月，则返回当年逐层累计列的上一个数值+当月应付金额，如果是每年的第一个月，返回上一年的最后一个月份的跨层累计数据+当月应付金额

条件汇总：COUNT(C2[!0]{A2=$A2 && C2>2500})

公式解释详情参见：层次坐标常用公式

公式	说明
A2=$A2	$A2 获取单元格 A2 扩展后对应位置上的值然后判断 A2 是否和 A2 扩展出去的值相等即判断是否在同一年，由于需求为分年统计
A2=$A2 && C2>2500	同时满足在同一年内且每月应付金额大于2500
C2[!0]{A2=$A2 && C2>2500}	取同时满足在同一年内，且每月应付金额大于2500的金额比如：2010 年返回的数值为 2837.94、3596.13、2822.19、2580.17
COUNT(C2[!0]{A2=$A2 && C2>2500})	对返回的应付金额计数

  rank() over([partition by col1] order by col2) 
     dense_rank() over([partition by col1] order by col2) 
     row_number() over([partition by col1] order by col2) 
     其中[partition by col1]可省略。

二：区别
    三个分析函数都是按照col1分组内从1开始排序
    
    row_number() 是没有重复值的排序(即使两天记录相等也是不重复的)，可以利用它来实现分页
    dense_rank() 是连续排序，两个第二名仍然跟着第三名
    rank()       是跳跃拍学，两个第二名下来就是第四名

select * from(
select b.area,b.cob,b.eid,b.device_num,uploadtime,
case when is_network  is null then '0' else is_network end as is_network
,case when is_production is null then '0' else is_production end as is_production,
Lot from (
select * from (
select *,row_number()over(partition by eqid order by uploadtime desc)as num
from aa_mes.dws_aa_is_nwtwork where is_network = '1' and uploadtime < '2021-11-01 23:00:00'
) d where num = 1

)g right join aa_mes.device_mapping as b
on b.eid = g.eqid )k where  device_num not like '%S' order by area,cob,eid;

    理论就不多讲了，看了案例，一下就明白了
    
SQL> create table t(
  2   name varchar2(10),
  3   score number(3));
 
Table created
 
SQL> insert into t(name,score) 
  2   select '语文',60 from dual union all
  3   select '语文',90 from dual union all
  4   select '语文',80 from dual union all
  5   select '语文',80 from dual union all
  6   select '数学',67 from dual union all
  7   select '数学',77 from dual union all
  8   select '数学',78 from dual union all
  9   select '数学',88 from dual union all
 10   select '数学',99 from dual union all
 11   select '语文',70 from dual
 12  /
 
10 rows inserted
 
SQL> select * from t;
 
NAME       SCORE
---------- -----
语文          60
语文          90
语文          80
语文          80
数学          67
数学          77
数学          78
数学          88
数学          99
语文          70
 
10 rows selected
 
SQL> select name,score,rank() over(partition by name order by score) tt from t;
 
NAME       SCORE         TT
---------- ----- ----------
数学          67          1
数学          77          2
数学          78          3
数学          88          4
数学          99          5
语文          60          1
语文          70          2
语文          80          3   <----
语文          80          3   <----
语文          90          5
 
10 rows selected
 
SQL> select name,score,dense_rank() over(partition by name order by score) tt from t;
 
NAME       SCORE         TT
---------- ----- ----------
数学          67          1
数学          77          2
数学          78          3
数学          88          4
数学          99          5
语文          60          1
语文          70          2
语文          80          3   <----
语文          80          3   <----
语文          90          4
 
10 rows selected
 
SQL> select name,score,row_number() over(partition by name order by score) tt from t;
 
NAME       SCORE         TT
---------- ----- ----------
数学          67          1
数学          77          2
数学          78          3
数学          88          4
数学          99          5
语文          60          1
语文          70          2
语文          80          3  <----
语文          80          4  <----
语文          90          5
 
10 rows selected
 
SQL> select name,score,rank() over(order by score) tt from t;
 
NAME       SCORE         TT
---------- ----- ----------
语文          60          1
数学          67          2
语文          70          3
数学          77          4
数学          78          5
语文          80          6
语文          80          6
数学          88          8
语文          90          9
数学          99         10
 
10 rows selected

--PRODUCER 供应商,PRODUCT 产品
--PRODUCERID供应商ID，PRODUCERNAME供应商姓名，CITY城市，COUNTRY国家，PRODUCTID供应商ID，PRODUCTNAME产品名称，PRODUCERID生产商，PRODUCTTYPE产品类型，COST成本，QUANTITY数量，CITY_PROPORTION城市占比，RANKING排名，GROUP_RANKING组内排名
select 
CITY,PRODUCTNAME,COST,QUANTITY,COST * QUANTITY AMOUNT
,(COST * QUANTITY) / sum(COST * QUANTITY)over(partition by CITY) CITY_PROPORTION
,(COST * QUANTITY) / sum(COST * QUANTITY)over() PRODUCTN_PROPORTION 
,DENSE_RANK() over(order  by COST * QUANTITY desc ) RANKING1--（重复排序）
,RANK() over(order  by COST * QUANTITY desc ) RANKING2--（跳跃排序）
,DENSE_RANK() over(partition by CITY order  by COST * QUANTITY desc) GROUP_RANKING
from PRODUCT a
left join PRODUCER b
on a.PRODUCERID = b.PRODUCERID

区域内占比	E3 / SUM(E3[!0;!0]{A3 = $A3})
地区内占比	E3 / SUM(E3[!0;!0])
排名一（重复排序）	SORT(E3)
排名二（跳跃排序）	COUNT(E3[!0;!0]{E3 > $E3}) + 1
组内排名	count(E3[!0;!0]{A3 = $A3 && E3 > $E3}) + 1

图表

实现功能：

可以选择数据库中存在的所有年份

根据选择的年份统计出该年下每个月的订单总数、已付订单数量、月销量

根据统计的数据制作混合图表，其中已付订单数量使用折线图，月销量坐标轴在右侧

分类轴日期倾斜显示

统计表中日期从小到大排列

动态显示标题：xx年各月份销售业绩表

使用数据：

FRDemo中的订单表与订单明细

数据：

样式：系列——柱形

系列——折线

系列——坐标轴（添加Y轴，注意：Y轴3中的3对应的是系列序号）

--订单ID,客户ID,雇员ID,订购日期,到货日期,发货日期,运货商,运货费,货主名称,货主地址,货主城市,货主地区,货主邮政编码,货主国家,是否已付,货主省份,应付金额,订单ID,产品ID,单价,数量,进价,折扣
select * from (select distinct
substr(订购日期,0,5) year
,substr(订购日期,0,8) month
,count(数量)over(partition by substr(订购日期,0,8)) 数量
,sum(case  when 是否已付 = 'true' then 1 else 0 end)
over(partition by substr(订购日期,0,8)) 已付订单数量
,sum(数量 * 单价)over(partition by substr(订购日期,0,8)) 月销量
from  订单 a
left join 订单明细 b
on a.订单ID = b.订单ID)
where 1=1
 and year='${year}'

select distinct substr(订购日期,0,5) year from  订单 order by substr(订购日期,0,5)

参数联动

实现功能：

班级，学号实现参数联动

班级为下拉框，学号为复选框，使用sql中定义参数

实现参数为空选出全部的功能，班级为空选出全部班级，学号为空，选出班级下的所有学生

选出全部班级时数据按班级分组显示

实现页面数据居中显示

思考：如果参数为空，不显示任何数据，怎么做

SELECT * FROM STSCORE WHERE CLASSNO='${classno}' or STUDENTNO='${studentno}'

高级要求：做到报表主体内不用过滤，参数全部设在数据源里面。

使用数据：

FRDemo中的Stscore表

--CLASSNO 类别编号,STUDENTNO 学生号,NAME 名称,SEX 性别,COURSE 课程,GRADE 等级
select 
DISTINCT STUDENTNO
from stscore
where 1=1 
${if(len(CLASSNO) == 0,"","and CLASSNO = '" + CLASSNO + "'")}

--CLASSNO 类别编号,STUDENTNO 学生号,NAME 名称,SEX 性别,COURSE 课程,GRADE 等级
select 
CLASSNO,STUDENTNO,NAME,COURSE,GRADE
from stscore
where 1=1
 ${if(len(CLASSNO) == 0,"","and CLASSNO = '" + CLASSNO + "'")}
 ${if(len(STUDENTNO) == 0,"","and STUDENTNO in ('" + replace(STUDENTNO,",","','") + "')")}

--CLASSNO 类别编号,STUDENTNO 学生号,NAME 名称,SEX 性别,COURSE 课程,GRADE 等级
select DISTINCT
CLASSNO
from stscore
where 1=1

下拉复选框联动

检验方法：下拉复选框联动

使用表制作一张报表。查询条件：地区和销售员

地区和销售员都可复选同时销售员选择项需要根据地区联动。

需要查询条件为空是查询全部值。最终展示效果。

其它效果如下图:

如下图，地区不选销售员可以选择全部值。

--region 地区，salesperson 销售员，product_type 产品类型，product产品，sales_volume 产品销量，date 日期
SELECT 
地区,销售员,产品类型,产品,销量,日期
FROM SALES_BASIC
where 1=1
 ${if(len(地区) == 0,"","and 地区 in ('" + replace(地区,",","','") + "')")}
 ${if(len(销售员) == 0,"","and 销售员 in ('" + replace(销售员,",","','") + "')")}

--region 地区，salesperson 销售员，product_type 产品类型，product_sales_volume 产品销量，date 日期
SELECT distinct
地区
FROM SALES_BASIC
where 1=1

 --region 地区，salesperson 销售员，product_type 产品类型，product_sales_volume 产品销量，date 日期
SELECT distinct 
销售员
FROM SALES_BASIC
where 1=1
 ${if(len(地区) == 0,"","and 地区 in ('" + replace(地区,",","','") + "')")}

超级链接

父

--PRODUCER PRODUCT
select 
*
from PRODUCER

子

seq()按组排序（序号）

序号- FineReport帮助文档 - 全面的报表使用教程和学习资料

--PRODUCER PRODUCT
select producername,producttype,productname,quantity,cost 
,quantity * cost amount
from PRODUCT a 
left join PRODUCER b on a.PRODUCERID = b.PRODUCERID
where producername = '${供应商}'
order by ${A} ${B}

图表热点链接

实现功能：

热点链接

子模板动态显示表头

子模板中以60分作为警戒线

使用数据：

FRDemo中的Stscore表

父

=series 如何获取图表对象

--CLASSNO 类别编号 ,STUDENTNO 学生号 ,NAME 名称 ,SEX 性别 ,COURSE 课程 ,GRADE 等级 
select distinct
classno,sum(grade)over(partition by classno) / count(classno)over(partition by classno) grade
from stscore

子

参数需对应

select distinct classno,course
,sum(grade)over(partition by classno,course) / count(classno)over(partition by classno,course) average_grade
from stscore
where 1=1
and classno = '${classno}'
order by classno

select distinct classno,course
,sum(grade)over(partition by classno,course) / count(classno)over(partition by classno,course) average_grade
from stscore
where 1=1
and classno = '${classno}'
order by classno

填报

实现功能：

参数联动选出制定学生的，展示该学生的数据，并可以修改

参数为空时新增一个学生的相应记录

实现主子表多源填报，上部分为学生信息表，下面为科目成绩表

可以新增某个学生的科目

使用文本，数字，下拉框，图片的控件

满意度使用公式自动计算，满85分为满意，否则为不满意

注意，照片字段只需要实现文件上传效果，不需要保存这个字段到数据库。

使用数据：

使用mysql或者sqlserver2008数据库创建两张表

表结构分别如下

表1：StudentInfo 学生信息表

字段名称	字段类型	解释说明
studentno	varchar(20)	学号
name	varchar(10)	姓名
classno	varchar(10)	班级
age	number	年龄
sex	varchar(10)	性别
address	varchar(50)	家庭地址
photo	Blob	照片

表2：CourseGrage 科目成绩表

字段名称	字段类型	解释说明
id	varchar(20)	序号
studentno	varchar(20)	学号
course	varchar(20)	科目
grade	number	成绩

CREATE TABLE `coursegrage` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `studentno` varchar(20) DEFAULT NULL COMMENT '学号',
  `course` varchar(20) DEFAULT NULL COMMENT '科目',
  `grade` int(255) DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `studentinfo` (
  `studentno` varchar(20) NOT NULL COMMENT '学号',
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `classno` varchar(10) DEFAULT NULL COMMENT '班级',
  `age` int(255) DEFAULT NULL COMMENT '年龄',
  `sex` varchar(10) DEFAULT NULL COMMENT '性别',
  `address` varchar(50) DEFAULT NULL COMMENT '家庭住址',
  `photo` blob COMMENT '照片',
  PRIMARY KEY (`studentno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select * from coursegrage a left join studentinfo b on a.studentno = b.studentno

studentinfo表数据：

coursegrage表数据：

动态填报：

模板效果：

销量表

地区可以通过下拉框进行选择，且不允许为空，不可以手动编辑

销售员，文本型，长度在1~5之间，否则提示

产品类型为下拉框，不允许为空

产品为文本框，不允许为空

销量为数字，不允许负数，最大不能超过1000，截图略。

销售成本表

销售员为文本，不允许为空，长度在1~5之间，否则提示，截图略

成本为数字，不允许负数，最大不能超过1000，截图略

点击提交的时候，成本与销售表中的销量进行比较，若成本大于销量则提示成本输入的值错误。

实现功能：

多sheet填报，以自己的名字分别录入一条数据

及时校验

select * from 销量

主子表联动

模板效果：

使用订单表，订单明细表，实现以上报表样式。

要求：点击订单表订单ID后，展示订单明细表，订单明细表订单ID为点击的订单ID，未点击前，右侧无内容（两种实现方式，普通报表 / 决策报表）

提示：嵌入式主子表，决策报表，超链接，动态参数

主

select 订单ID,订购日期,货主名称,是否已付 from 订单

IF($$$,"已支付","未支付")

子

select 订单ID,产品ID,单价 from 订单明细
where 1=1
and 订单ID = '${订单ID}'

动态列

数据集参数实现动态列- FineReport帮助文档 - 全面的报表使用教程和学习资料

使用订单表，实现以上样式

要求：

1、下拉多选列表选择字段名称，全不选展示所有字段，实现下拉列表控制展现内容

2、将“订单ID，雇员ID，客户ID”三个字段对应表头展示改为“Order，Employee，Customer”

提示：SQL动态参数，条件属性，内置数据集，实际值显示值

select * from 订单

select * from 订单 limit 1

select ${col} from 订单

指定字段汇总

不选择汇总字段，默认查询所有内容

选择汇总字段，按所选字段进行销量汇总

使用销量表，实现以上样式

要求：

1、增加除销量之外的“地区、销售员、产品类型、产品”四个汇总字段单选按钮。不选择任何汇总字段，默认展示所有信息

选择某汇总字段，按该字段进行销量汇总并展

默认显示全部：

tabledatafields("") 返回数据集每一列的列名

参数查询数据库任一表- FineReport帮助文档 - 全面的报表使用教程和学习资料

indexofarray(数组array，个数n)	返回数组array的第n个元素
split（string1,string2）	用string1分割string2
tabledatafields("表名")	返回数据集中每一列的列名

select 
${if(len(col) = 0,"*",col+",sum(销量) as 销量")}
from 销量
${if(len(col) = 0,"","group by "+ col)}

多个单选

减肥中的小南瓜

关注

1
点赞
踩
50

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录