使用方法:
page, _ := strconv.Atoi(c.DefaultPostForm("page", "1")) // 页码 起始页为1
limit, _ := strconv.Atoi(c.DefaultPostForm("limit", "10")) // 条数 每页查询的记录数
//搜索类型
// 定义sql条件和参数
declareParams := ""
sqlWhere := ""
sqlParams := []interface{}{}
// 根据日志类型搜索
auhlType := c.PostForm("auhl_type")
if auhlType != "" {
declareParams += " DECLARE @auhl_type INT = ? ; "
sqlWhere += " AND auhl_type =@auhl_type "
sqlParams = append(sqlParams, auhlType)
}
// 根据请求时间段查找 | 格式 2021-04-03 00:00:00
selStarttime, _ := time.Parse("2006-01-02 15:04:05", c.PostForm("selStartTime")) // 选择开始时间
selEndtime, _ := time.Parse("2006-01-02 15:04:05", c.PostForm("selEndTime")) // 选择结束时间
if !selStarttime.IsZero() {
declareParams += " DECLARE @selStartTime DATETIME = ? ; "
sqlWhere += " AND auhl_req_time >= @selStartTime "
sqlParams = append(sqlParams, selStarttime)
}
if !selEndtime.IsZero() {
declareParams += " DECLARE @selEndTime DATETIME = ? ; "
sqlWhere += " AND auhl_req_time <= @selEndTime "
sqlParams = append(sqlParams, selEndtime)
}
// 查询数据
total, datas, err := sqlQueryPageAllUserHttpLog(page, limit, declareParams, sqlWhere, sqlParams...)
if err != nil {
sbjlog.Debug("查询Iot 数据 sqlQueryPageAllUserHttpLog 异常:%s", err.Error())
c.String(http.StatusOK, getVueResult(0, false, "数据加载错误", ""))
return
}
if total == 0 {
c.String(http.StatusOK, getVueResult(0, false, "暂无数据", gin.H{"total": total, "rows": datas}))
return
}
//得到数据 组装返回
c.String(http.StatusOK, getVueResult(0, true, "", gin.H{"total": total, "rows": datas}))
GetCount 获取统计值/查询某个int字段值
/*GetCount 获取统计值/查询某个int字段值
调用例子如下:
count, err := xsql.GetCount(alluserdb.DBTJJAlluser, "SELECT COUNT(1) FROM ALL_USER_INFO ")
*/
func GetCount(sqlBasis *sql.DB, sqlStr string, args ...interface{}) (int, error) {
count := -1
err := sqlBasis.QueryRow(sqlStr, args...).Scan(&count)
return count, err
}
CreatePagingSQLV2
// PagingSQLV2Params 构造分页sqlV2参数
type CreatePagingSQLV2Params struct {
TableName string //主表
PrimaryKeys string //主键 | 单表例子"id,cpft_piid" | 多表例子 "表a:字段1,字段2;表b:字段1,字段2"
PrimaryKeysLeftJoins string //主键由不同表字段组成时 用来放其他主键表的left join语句 (非必填,主键由多个表字段构成时使用)
PageIndex int //第几页
PageSize int //每页多少条
Fields string //查询字段
LeftJoins string //副表联接语句 | (非必填,有联表查询时使用)可以传 多个副表联接语句 | 注意:副表重命名不要使用 "a" ,联接主表字段的条件写法:副表.字段=主表字段
Where string //查询条件
Orders string //排序条件
Total int //符合查询条件的数据总数 | 根据该值使用二分法构造查询sql
DeclareParams string //定义 sql的语句中 查询条件要用到的参数 | 例: " DECLARE @ihet_name VARCHAR(100)=?;"
}
/* CreatePagingSQLV2 | 构造分页sqlV2 | 2021.04.21 Created by zmm
说明:
1. 封装CreatePagingSQLPlus,使用结构体传参,V1方法参数太多,容易传错
2. 去掉 searchItem LeftJoinSearch参数,外部可以通过 AND (SELECT COUNT(1) FROM 表 WHERE 关联条件)>0 实现,这种方式使得查询更加简洁高效
*/
func CreatePagingSQLV2(params CreatePagingSQLV2Params) *string {
// 生成分页SQL
sqlStr := CreatePagingSQLPlus(params.TableName, params.PrimaryKeys, params.PrimaryKeysLeftJoins, params.Fields, params.LeftJoins, params.Where, params.Orders, LeftJoinSearch{}, params.PageIndex, params.PageSize, params.Total)
// 检查是否生成成功
if sqlStr != nil {
// 补充sql参数定义 | 由于 CreatePagingSQLPlus 已在一些项目中使用,这里只做兼容,不直接修改 CreatePagingSQLPlus 方法
*sqlStr = params.DeclareParams + " " + *sqlStr
}
return sqlStr
}
CreatePagingSQLPlus
/*CreatePagingSQLPlus 构造分页sql 调用例子在下方TestPagingSQLPlus()方法中
函数调用说明:当超出查询范围时 函数会返回空字符串 所以调用函数得到的分页sql,需要判断是否为空字符串
传入参数说明:
tbName 主表名名称
pmFields 主键由单表字段组成,多个字段中间用逗号隔开 例:查询文控树,pmFields="id,cpft_piid"
主键由多表字段组成,不同表之间用分号隔开,不同字段用逗号隔开 符号采用英文半角 例:pmFields="表a:字段1,字段2;表b:字段1,字段2"
pmLefts 主键由不同表字段组成时 用来放其他主键表的left join语句
fields 查询字段 | 可查询多个字段,包含主表、副表字段
leftJoin 副表联接语句 | 可以传 多个副表联接语句 | 注意:副表重命名不要使用 "a" ,联接主表字段的条件写法:副表.字段=主表字段
where 查询条件 | 条件只能是 tbName/其他主键所属表 的字段 | 关键字 AND、OR 要大写
orders 排序条件 | 顺序排序只需要传字段,反序排序使用关键字"DESC" 例:原序-"iheli_add_time",反序-"iheli_add_time DESC"
searchItem 外联表查询实例 | 详细请看LeftJoinSearch结构体说明
pageIndex 页码
pageSize 每页条数
total 总条数
思路相关:
1.索引设计原则,参考资料:https://www.cnblogs.com/selene/p/4474721.html
2.Offset-Fetch的使用,参考资料:https://www.cnblogs.com/ljhdo/p/4861263.html (sql2012及以上版本)
3.使用二分法缩小一倍查询范围
4.先查询出主键/唯一索引,再根据主键/索引查询出其他字段
5.建议数据量较大的表,应该设置主键和建立聚焦索引(唯一),经常性order by的字段建立非聚焦索引
*/
func CreatePagingSQLPlus(tbName, pmFields, pmLefts, fields, leftJoin, where, orders string, searchItem LeftJoinSearch, pageIndex, pageSize, total int) *string {
//页码校对
if pageIndex <= 0 {
pageIndex = 1
}
//页条数校对
if pageSize <= 0 {
pageSize = 10
}
//处理Where条件语句
if strings.TrimSpace(where) != "" {
where = " WHERE " + strings.TrimLeft(strings.TrimSpace(where), "AND")
}
before := (pageIndex - 1) * pageSize //按'原序'读取 需要跳过的范围
//超出查询范围 | 起点坐标 > 数据总条数
if before > total {
return nil //返回空字符串
}
//2020.07.16 mm 恢复注释代码 | 已确认主键排序问题,采用在外部添加排序方式处理
//2020.07.09 mm 注释 反序查找 有主键(自定义ID)的表 使用反序查找会出现问题
after := total - pageIndex*pageSize //按'反序'读取需要跳过的范围
/*
0|_____________________________________________________________________________________________________________________________________________|@total
|@before 按'原序'读取 需要跳过的范围| |@after 按'反序'读取需要跳过的范围|
|------目标数据------|
*/
beginIndex := before //起点坐标 初始化为 按'原序'读取的起点
newOrders := orders
//2020.07.16 mm 添加判断,数据条数超过一万才需要使用二分查找
if total > 10000 && before > after { //按'原序'读取 需要跳过的范围 比 按'反序'读取需要跳过的范围 大 ,so 应该反序查询
newOrders = createDescOrders(orders) //将排序条件 反过来
beginIndex = after //设置起点坐标为 按'反序'读取的起点
if beginIndex < 0 { //校对起点坐标值
pageSize += beginIndex //重置每页条数
if pageSize == 0 {
return nil //超出查询范围
}
beginIndex = 0 //起点坐标最小为0
}
}
//定义查询sql
sqlStr, deleteSQL := "", ""
pmKey := decodeMultiTbPmKey(pmFields, tbName)
//判断是否有外联表的查询条件 | 例如:查询日志表中 项目名称有'_API'的日志 ,这里 项目 是属于外联表 ,所以 外联的字段 就是 iheli_tid
if strings.TrimSpace(searchItem.Field) == "" {
sqlStr = " SELECT " + pmKey.reIDFields + " INTO #tmp FROM " + tbName + " " + pmLefts + where //查询满足where条件的数据,放入【tmp】
} else {
mainWhere := " WHERE " + searchItem.Field + " IN (SELECT id FROM #ids)"
if where != "" {
mainWhere = where + " AND " + searchItem.Field + " IN (SELECT id FROM #ids)" //原生where + 外联表搜索条件
}
//构造查询sql
sqlStr = " SELECT TOP 0 " + pmKey.intoIDFields + " INTO #tmp FROM " + tbName + //初始化一个临时表
" " + searchItem.SQLString + //外联接表 查询语句
" DECLARE @count INT=(SELECT COUNT(1) FROM #ids);" + //定义一个变量 基础数据条数
" IF @count>0" +
" INSERT INTO #tmp(" + pmKey.newIDFields + ")" +
" SELECT " + pmKey.idFields + " FROM " + tbName + " " + pmLefts + //查询 主键
" " + mainWhere //查询条件
deleteSQL = " DROP TABLE #ids;" //删除临时表
}
//排序条件
sqlStr += " ORDER BY " + newOrders //排序条件
//分页条件
sqlStr += " OFFSET " + fmt.Sprintf("%d", beginIndex) + " ROWS FETCH NEXT " + fmt.Sprintf("%d", pageSize) + " ROWS ONLY;"
//根据#tmp中的主键id 联表查询出其他所需字段
sqlStr += " SELECT " + fields + " FROM #tmp a " +
pmKey.conditions +
" " + leftJoin +
" ORDER BY " + orders
//删除临时表
sqlStr += " DROP TABLE #tmp;" + deleteSQL
clearTempTbSQL := " IF object_id(N'tempdb..#tmp',N'U') IS NOT NULL DROP TABLE #tmp;" +
" IF object_id(N'tempdb..#ids',N'U') IS NOT NULL DROP TABLE #ids; "
sqlStr = clearTempTbSQL + sqlStr
return &sqlStr
}
//decodeMultiTbPmKey 解析多表联合主键(主键由不同表的字段组成)
func decodeMultiTbPmKey(pmKeys, mainTbName string) pmKey {
keyStr := strings.ReplaceAll(pmKeys, "[", "")
keyStr = strings.ReplaceAll(keyStr, "]", "")
var item pmKey
//不含分号则 主键字段 都为 主表字段
if !strings.Contains(keyStr, ":") {
//字段1,字段2 不同字段用逗号隔开 符号采用英文半角
//例子:keyStr= "ppda_id"
item.conditions += fmt.Sprintf(" LEFT JOIN %s ON ", mainTbName)
for _, skey := range strings.Split(keyStr, ",") {
skey = strings.TrimSpace(skey)
if skey != "" {
item.idFields += fmt.Sprintf("[%s],", skey)
item.newIDFields += fmt.Sprintf("[KEY_%s],", skey)
item.intoIDFields += fmt.Sprintf("[%s] AS [KEY_%s],", skey, skey)
item.reIDFields += fmt.Sprintf("[%s] AS [KEY_%s],", skey, skey)
item.conditions += fmt.Sprintf("[%s]=a.[KEY_%s] AND", skey, skey)
}
}
item.conditions = strings.TrimRight(item.conditions, "AND")
} else {
//表a:字段1,字段2;表b:字段1,字段2 不同表之间用分号隔开,不同字段用逗号隔开 符号采用英文半角
//例子: keyStr= "PROJECT_PROGRAM_DATABASE_ASSOCIATION:[ppda_id];PROJECT_PROGRAM_SERVER_ASSOCIATION:[ppsa_id]"
var tempArr []string
tbName := ""
for _, v := range strings.Split(keyStr, ";") {
tempArr = strings.Split(v, ":")
tbName = tempArr[0]
if tbName == "" {
continue
}
item.conditions += fmt.Sprintf(" LEFT JOIN %s ON ", tbName)
for _, skey := range strings.Split(tempArr[1], ",") {
skey = strings.TrimSpace(skey)
if skey == "" {
continue
}
item.idFields += fmt.Sprintf("[%s],", skey)
item.newIDFields += fmt.Sprintf("[KEY_%s],", skey)
item.intoIDFields += fmt.Sprintf("'1122334455668' AS [KEY_%s],", skey)
item.reIDFields += fmt.Sprintf("[%s] AS [KEY_%s],", skey, skey)
item.conditions += fmt.Sprintf("[%s]=a.[KEY_%s] AND", skey, skey)
}
item.conditions = strings.TrimRight(item.conditions, "AND")
}
}
item.idFields = strings.TrimRight(item.idFields, ",")
item.newIDFields = strings.TrimRight(item.newIDFields, ",")
item.intoIDFields = strings.TrimRight(item.intoIDFields, ",")
item.reIDFields = strings.TrimRight(item.reIDFields, ",")
return item
}
/*TestPagingSQLPlus 测试 调用构造分页sql函数 例子*/
func TestPagingSQLPlus() {
tbName := "dbo.PROJECT_PROGRAM_DATABASE_ASSOCIATION" //主表名称
//单表主键 | 字段1,字段2 不同字段用逗号隔开
//多表主键 | 表a:字段1,字段2;表b:字段1,字段2 不同表之间用分号隔开,不同字段用逗号隔开 符号采用英文半角
pmkey := "PROJECT_PROGRAM_DATABASE_ASSOCIATION:[ppda_id];PROJECT_PROGRAM_SERVER_ASSOCIATION:[ppsa_id]"
//其他主键表的联接语句
pmLeft := "LEFT JOIN dbo.PROJECT_PROGRAM_SERVER_ASSOCIATION ON ppsa_ppiid=ppda_ppiid AND ppsa_environment_type=ppda_program_environment_type"
//查询字段
fields := "ppda_id,ppsa_id,ppi_id,ppsa_environment_type,ppi_name,pros.sbi_name,tdi_id,ppda_db_environment_type,tdi_cnname,dbs.sbi_name"
//副表联接语句
leftJoin := `LEFT JOIN dbo.PROJECT_PROGRAM_INFO ON ppi_id=ppsa_ppiid
LEFT JOIN dbo.SERVER_BASIC_INFO pros ON pros.sbi_id = ppsa_tdiid
LEFT JOIN dbo.TABLE_DB_TYPE_DETAIL ON tdtd_tdi_id= ppda_tdiid AND tdtd_environment_type=ppda_db_environment_type
LEFT JOIN dbo.TABLE_DB_INFO ON tdi_id = ppda_tdiid
LEFT JOIN dbo.SERVER_BASIC_INFO dbs ON dbs.sbi_id = tdtd_server_id `
//查询条件
where := "AND ppda_ppiid =220028 AND ppda_tdiid=32 AND ppda_program_environment_type=10"
//排序条件
orders := "ppda_id,ppsa_id"
//外联表搜索条件 没有外联表搜索时 searchItem.Field="" 即可
searchItem := LeftJoinSearch{
Field: "", //外联字段
}
pageIndex := 1 //页码
pageSize := 10 //每页条数
//构造统计总条数sql
totalSQL := CreateCountSQLPlus(tbName, pmLeft, where, searchItem)
fmt.Println(totalSQL)
total := 1000 //注意实际应用中,total是执行 统计总条数sql 所得
//构造分页sql
pagingSQL := CreatePagingSQLPlus(tbName, pmkey, pmLeft, fields, leftJoin, where, orders, searchItem, pageIndex, pageSize, total)
fmt.Println(pagingSQL)
}