六、如何自定义SQL查询？

麦田培训学校管理软件MTCUN 3.0白皮书

麦田软件公司出品

六、如何自定义SQL查询？

本节内容面向数据库技术人员。企业的数据输出是多种多样的，要想真正适应企业需求，必需要进行查询开发。本软件提供了灵活的查询扩展功能，开发人员只要参考数据库结构，编制SQL语法，保存到系统，就可以以后重复使用一劳永逸。

管理系统内，增加自定义查询的地方在：[分析--查询管理--新建]

MTcnsoft Training除了支持标准的SQL语法，还内置了一些特殊的标签及函数。

1. 内置标签：

主查询内置标签：

标签	说明	数据类型
{@SchoolID:校区}	表示校区ID。此标签只能配置SQL的in(…)语法使用。	[数字型集合]
{@StartDate:开始日期}	表示开始时间、结束时间。此标签使用时必需同时出现。分秒精度：开始日期当日0点，结束日期当日23点59分59秒	[日期型]
{@EndDate:结束日期}	表示开始时间、结束时间。此标签使用时必需同时出现。分秒精度：开始日期当日0点，结束日期当日23点59分59秒	[日期型]
{@Key:关键词}	表示关键词。当需要多个关键词时，请使用整数编号区分，如：{@Key1:关键词一}{@Key2:关键词二}	[字符串、数值型、日期]
{@UserID}	表示当前登录的员工工号。也可以这样使用{@UserID:员工}，将出现一个可以选择员工的下拉列表。	[数字型]
ShowKey	特殊列名。系统将把列的值做为子查询的一个参数。	[字符串、数值型、日期]

子查询内置标签：

标签	说明
{@SchoolID}	接收主查询中的校区ID。
{@StartDate}	接收主查询中的开始时间、结束时间。
{@EndDate}	接收主查询中的开始时间、结束时间。
{@UserID}	表示当前登录的员工工号。
{@Key}	接收主查询中的关键词。
{@ShowKey}	接收子查询参数列的值。

例一：查询学员充值信息

参数：可分校区，按指定时间段

类型

语法

主查询

Select
Payment.PaymentID as 充值号,
Payment.StudentID as 学号,
Payment.StudentName as 姓名,
Payment.PaymentTypeName as 充值类型,
Payment.PayMethod as 收费方式,
Payment.DateAndTime as 日期,
Payment.CreditExpiration as 信用到期,
Payment.PayMoney as 实收金额,
Payment.TrueName as 收款人
from Payment
where Payment.PaymentTypeID in (1,2,3,4)
and Payment.PostSchoolID in ( {@SchoolID:校区} )
and Payment.DateAndTime >= {@StartDate:开始日期}
and Payment.DateAndTime <= {@EndDate:结束日期}
order by Payment.UserID,Payment.DateAndTime

例二：查询讲师课时、薪资

参数：上课日期段，支持子查询

类型

语法

主查询

Select
LessonDegree.TeacherID as 工号,
Users.TrueName as 姓名,
Sum(LessonDegree.Lessons) as 课时,
Sum(LessonDegree.Lessons*Class.LessonMinute)/60 as 小时,
Sum(LessonDegree.Lessons*Class.LessonMinute)/60 *Users.HourFee as 工资,
LessonDegree.TeacherID as ShowKey

from LessonDegree,Class,Users
where LessonDegree.ClassID = Class.ClassID
and LessonDegree.TeacherID = Users.UserID
and LessonDegree.LeStateID = 1
and Users.Pluralist= 1
and LessonDegree.StartDate >= {@StartDate:开始日期}
and LessonDegree.StartDate <= {@EndDate:结束日期}
group by LessonDegree.TeacherID,Users.TrueName,Users.HourFee

子查询

Select
LessonDegree.ClassID as 班号,
Class.ClassName as 班级,
Class.LessonMinute as 每课时分钟,
Sum(LessonDegree.Lessons) as 课时,
Sum(LessonDegree.Lessons*Class.LessonMinute)/60 as 小时,
Sum(LessonDegree.Lessons*Class.LessonMinute)/60 *Users.HourFee as 工资
from LessonDegree,Class,Users
where LessonDegree.ClassID = Class.ClassID
and LessonDegree.TeacherID = Users.UserID
and LessonDegree.LeStateID = 1
and LessonDegree.TeacherID = {@ShowKey}
and LessonDegree.StartDate >= {@StartDate}
and LessonDegree.StartDate <= {@EndDate}
group by LessonDegree.ClassID,Class.ClassName,Class.LessonMinute,Users.HourFee

2. 内置函数如下：

名称	说明
FormatDate(datetime)	返回日期部分。
FormatTime(datetime)	返回时间部分。
GetSex(int)	输入数值，返回性别的文本表达。
GetYN(int)	输入数值，返回是、否的文本表达。
GetLockState(int)	输入数值，返回禁用、启用的文本表达。
GetLeState(int)	输入数值，返回上课状态的文本表达。
GetAtState(int)	输入数值，返回出勤状态的文本表达。
GetScState(int)	输入数值，返回选班状态的文本表达。
GetSourceType(int)	输入数值，返回咨询来源的文本表达。
GetStudentType(int)	输入数值，返回学员类型的文本表达。
GetLessonType(int)	输入数值，返回上课类型的文本表达。
GetBatchType(int)	输入数值，返回入库、退回公司的文本表达。
Get BillType(int)	输入数值，返回售出、退货的文本表达。
GetScrappedType(int)	输入数值，返回报废、溢出的文本表达。
GetPaymentType(int)	输入数值，返回充值的四种状态的文本表达。
GetLogType(int)	输入数值，返回日志类型的文本表达。
GetWeekText(int)	输入数值，返回星期枚举的文本表达。

六、 如何自定义SQL查询？

六、如何自定义SQL查询？