# query查询

通过不同的案例来说明query的使用说明，通过query.js要拼装查询json，然后传到后端接口生产相应的sql。

每个案例输出格式

查询接口
query.js
入参json
输出sql
返回结果

# 单字段查询

通过username查询数据，且默认过滤条件为LK（LIKE）

注意

之前提到过，所有的过滤条件，必须在xml配置，所有这里的过滤条件是在xml配置过的

查询接口

sys/user/list?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toR(qry, 'username', 'test')

查询参数

{"w":[],"r":[{"n":"a1","t":"and","w":[{"k":"username","v":"test","m":"",
    "t":"and","s":0}]}],"o":[],"j":[],"p":{},"s":{}}

sql输出

SELECT t_sys_user.`id` t_sys_user__id, t_sys_user.`role_id` t_sys_user__role_id, t_sys_user.`username` t_sys_user__username, t_sys_user.`email` t_sys_user__email, t_sys_user.`rmks` t_sys_user__rmks, t_sys_user.`type` t_sys_user__type 
FROM t_sys_user 
WHERE ((t_sys_user.USERNAME LIKE ?))

返回结果

{
    "code": 0,
    "header": {
        "typeDsr": "类型（0：管理员，1：非管理员）描述",
        "id": "ID",
        "roleId": "角色ID",
        "username": "登录名",
        "email": "邮件",
        "rmks": "备注",
        "type": "类型（0：管理员，1：非管理员）"
    },
    "data": {
        "list": [
            {
                "id": 3,
                "roleId": 3,
                "username": "test",
                "email": "test@163.com",
                "rmks": "test",
                "type": 1,
                "typeDsr": "普通用户"
            }
        ]
    }
}

# 两个字段AND查询

通过username和email两个字段进行联合查询

查询接口

sys/user/list?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toR(qry, 'username', 'test', 'EQ')
this.$query.toR(qry, 'email',    '163',  'LK')

查询参数

{"w":[],"r":[{"n":"a1","t":"and","w":[{"k":"username","v":"test","m":"EQ","t":"and","s":0},
    {"k":"email","v":"163","m":"LK","t":"and","s":0}]}],"o":[],"j":[],"p":{},"s":{}}

sql输出

SELECT t_sys_user.`id` t_sys_user__id, t_sys_user.`role_id` t_sys_user__role_id, t_sys_user.`username` t_sys_user__username, t_sys_user.`email` t_sys_user__email, t_sys_user.`rmks` t_sys_user__rmks, t_sys_user.`type` t_sys_user__type 
FROM t_sys_user 
WHERE ((t_sys_user.USERNAME = ? AND t_sys_user.EMAIL LIKE ?))

# 两个字段OR查询（三种方式）

方式一：username = ? or email = ?，且seq不设置排序，那么必须是顺序添加，如果设置了seq，那么可以不按顺序添加

查询接口

sys/user/list?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toR(qry, 'username', 'test', 'LK')
this.$query.toR(qry, 'email',    '163',  'LK', 'OR')

查询参数

{"w":[],"r":[{"n":"a1","t":"and","w":[{"k":"username","v":"test","m":"LK","t":"and","s":0},
    {"k":"email","v":"163","m":"LK","t":"or","s":0}]}],"o":[],"j":[],"p":{},"s":{}}

sql输出

SELECT t_sys_user.`id` t_sys_user__id, t_sys_user.`role_id` t_sys_user__role_id, t_sys_user.`username` t_sys_user__username, t_sys_user.`email` t_sys_user__email, t_sys_user.`rmks` t_sys_user__rmks, t_sys_user.`type` t_sys_user__type 
FROM t_sys_user 
WHERE ((t_sys_user.USERNAME LIKE ? OR t_sys_user.EMAIL LIKE ?))

方式二：username = ? or mob = ?，且seq不设置排序，那么必须是顺序添加，如果设置了seq，那么可以不按顺序添加

查询接口

sys/user/list?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toR(qry, 'username', 'test', 'LK')
this.$query.toR(qry, 'email',    '163',  'LK', 'and', '1', 'a2', 'OR')

查询参数

{"w":[],"r":[{"n":"a1","t":"and","w":[{"k":"username","v":"test","m":"LK","t":"and","s":0}]},
    {"n":"a2","t":"or","w":[{"k":"email","v":"163","m":"LK","t":"and","s":"1"}]}],"o":[],"j":[],"p":{},"s":{}}

sql输出

SELECT t_sys_user.`id` t_sys_user__id, t_sys_user.`role_id` t_sys_user__role_id, t_sys_user.`username` t_sys_user__username, t_sys_user.`email` t_sys_user__email, t_sys_user.`rmks` t_sys_user__rmks, t_sys_user.`type` t_sys_user__type 
FROM t_sys_user 
WHERE ((t_sys_user.USERNAME LIKE ?) OR (t_sys_user.EMAIL LIKE ?))

方式三：字段之间通过分隔符(",")或者("__")进行分割

查询接口

sys/user/list?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toR(qry, 'username,email',  'test', 'LK', 0, 'a1')
this.$query.toR(qry, 'username__email', 'test', 'LK', 1, 'a2')

查询参数

{"w":[],"r":[{"n":"a1","t":"and","w":[{"k":"username,email","v":"test","m":"LK","t":"and","s":"0"}]},
    {"n":"a2","t":"","w":[{"k":"username__email","v":"test","m":"LK","t":"and","s":"1"}]}],"o":[],"j":[],"p":{},"s":{}}

sql输出

SELECT t_sys_user.`id` t_sys_user__id, t_sys_user.`role_id` t_sys_user__role_id, t_sys_user.`username` t_sys_user__username, t_sys_user.`email` t_sys_user__email, t_sys_user.`rmks` t_sys_user__rmks, t_sys_user.`type` t_sys_user__type 
FROM t_sys_user 
WHERE ((t_sys_user.USERNAME LIKE ? OR t_sys_user.EMAIL LIKE ?)  
AND (t_sys_user.USERNAME LIKE ? OR t_sys_user.EMAIL LIKE ?))

# 通过IN,Between查询

查询接口

sys/user/list?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toR(qry, 'username', 'test,anno', 'IN')
this.$query.toR(qry, 'type',     '0,1',        'BT')

查询参数

{"w":[],"r":[{"n":"a1","t":"and","w":[{"k":"username","v":"test,anno","m":"IN","t":"and","s":0},
    {"k":"type","v":"0,1","m":"BT","t":"and","s":0}]}],"o":[],"j":[],"p":{},"s":{}}

sql输出

SELECT t_sys_user.`id` t_sys_user__id, t_sys_user.`role_id` t_sys_user__role_id, t_sys_user.`username` t_sys_user__username, t_sys_user.`email` t_sys_user__email, t_sys_user.`rmks` t_sys_user__rmks, t_sys_user.`type` t_sys_user__type 
FROM t_sys_user 
WHERE ((t_sys_user.USERNAME IN (?,?) AND t_sys_user.TYPE BETWEEN ? AND ?))

# 排序查询

默认排序为ASC

查询接口

sys/user/list?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toO(qry, 'type', 'desc')

查询参数

{"w":[],"r":[],"o":[{"k":"type","t":"desc"}],"j":[],"p":{},"s":{}}

sql输出

SELECT t_sys_user.`id` t_sys_user__id, t_sys_user.`role_id` t_sys_user__role_id, t_sys_user.`username` t_sys_user__username, t_sys_user.`email` t_sys_user__email, t_sys_user.`rmks` t_sys_user__rmks, t_sys_user.`type` t_sys_user__type 
FROM t_sys_user 
ORDER BY t_sys_user.TYPE DESC

# 分页查询

count默认为0，如果count=1，那么分页查询，不查总数

查询接口

sys/user/page?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toP(qry, 1, 10)

查询参数

{"w":[],"r":[],"o":[],"j":[],"p":{"n":"1","s":"10","c":0},"s":{}}

sql输出

SELECT t_sys_user.`id` t_sys_user__id, t_sys_user.`role_id` t_sys_user__role_id, t_sys_user.`username` t_sys_user__username, t_sys_user.`email` t_sys_user__email, t_sys_user.`rmks` t_sys_user__rmks, t_sys_user.`type` t_sys_user__type 
FROM t_sys_user
LIMIT ?

返回结果

{
    "code": 0,
    "header": {
        "typeDsr": "类型（0：管理员，1：非管理员）描述",
        "id": "ID",
        "roleId": "角色ID",
        "username": "登录名",
        "email": "邮件",
        "rmks": "备注",
        "type": "类型（0：管理员，1：非管理员）"
    },
    "data": {
        "list": [
            {
                "id": 1,
                "roleId": 1,
                "username": "admin",
                "email": "demo@163.com",
                "rmks": "admin",
                "type": 0,
                "typeDsr": "管理员"
            },
            {
                "id": 2,
                "roleId": 2,
                "username": "anno",
                "email": "anno@163.com",
                "rmks": "anno",
                "type": 2,
                "typeDsr": "匿名用户"
            },
            {
                "id": 3,
                "roleId": 3,
                "username": "test",
                "email": "test@163.com",
                "rmks": "test",
                "type": 1,
                "typeDsr": "普通用户"
            }
        ]
    },
    "page": {
        "total": 3,
        "pageSize": 10,
        "pageNum": 1
    }
}

# 过滤查询字段

可以精确某几个字段查询，多个字段通过(",")分割

查询接口

sys/user/list?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toS(qry, 'username,mob')

查询参数

{"w":[],"r":[],"o":[],"j":[],"p":{},"s":{"v":"username,email"}}

sql输出

SELECT t_sys_user.`username` t_sys_user__username, t_sys_user.`email` t_sys_user__email 
FROM t_sys_user

返回结果

{
    "code": 0,
    "header": {
        "typeDsr": "类型（0：管理员，1：非管理员）描述",
        "id": "ID",
        "roleId": "角色ID",
        "username": "登录名",
        "email": "邮件",
        "rmks": "备注",
        "type": "类型（0：管理员，1：非管理员）"
    },
    "data": {
        "list": [
            {
                "id": null,
                "roleId": null,
                "username": "admin",
                "email": "demo@163.com",
                "rmks": null,
                "type": null,
                "typeDsr": null
            },
            {
                "id": null,
                "roleId": null,
                "username": "anno",
                "email": "anno@163.com",
                "rmks": null,
                "type": null,
                "typeDsr": null
            },
            {
                "id": null,
                "roleId": null,
                "username": "test",
                "email": "test@163.com",
                "rmks": null,
                "type": null,
                "typeDsr": null
            }
        ]
    }
}

# 复杂查询

查询接口

sys/user/page?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toR(qry, 'username', 'test', 'EQ',  'and', 1, 'a1')
this.$query.toR(qry, 'email',    '163',  'LK',  'and', 0, 'a1')
this.$query.toR(qry, 'type',      1,     'IS',  'or',  1, 'a2')
this.$query.toR(qry, 'rmks',      1,     'NIS', 'or',  0, 'a2')
this.$query.toS(qry, 'username,mob')
this.$query.toO(qry, 'type', 'desc')
this.$query.toP(qry, 1, 10)

查询参数

{"w":[],"r":[{"n":"a1","t":"and","w":[{"k":"username","v":"test","m":"EQ","t":"and","s":"1"},
{"k":"email","v":"163","m":"LK","t":"and","s":"0"}]},
{"n":"a2","t":"and","w":[{"k":"type","v":"1","m":"IS","t":"or","s":"1"},
{"k":"rmks","v":"1","m":"NIS","t":"or","s":"0"}]}],"o":[{"k":"type","t":"desc"}],
"j":[],"p":{"n":"1","s":"10","c":0},"s":{"v":"username,mob"}}

sql输出

SELECT t_sys_user.`username` t_sys_user__username 
FROM t_sys_user 
WHERE ((t_sys_user.EMAIL LIKE ? AND t_sys_user.USERNAME = ?) 
AND (t_sys_user.RMKS IS NOT NULL OR t_sys_user.TYPE IS NULL)) 
ORDER BY t_sys_user.TYPE DESC 
LIMIT ?

# ON过滤条件

左（右）连表查询，ON后面添加过滤条件

查询接口

sys/user/listUserLtRole?this.$query.toQ(qry)

过滤条件

let qry = this.$query.new()
this.$query.toJ(qry, 'username', 'test',  'LK',  'urOn')
this.$query.toJ(qry, 'email',    'ROLE',  'LK',  'urOn')

查询参数

{"w":[],"r":[],"o":[],"j":[{"n":"urOn","r":[{"t":"and",
"w":[{"k":"username","v":"test","m":"LK","t":"and","s":0},
{"k":"roleNm","v":"ROLE","m":"LK","t":"and","s":0}]}]}],"p":{},"s":{}}

sql输出

SELECT t_sys_user.`id` t_sys_user__id , t_sys_user.`role_id` t_sys_user__role_id , t_sys_user.`username` t_sys_user__username , t_sys_user.`email` t_sys_user__email , t_sys_user.`rmks` t_sys_user__rmks , t_sys_user.`type` t_sys_user__type , t_sys_role.`id` t_sys_role__id , t_sys_role.`cd` t_sys_role__cd , t_sys_role.`nm` t_sys_role__nm , t_sys_role.`rmks` t_sys_role__rmks 
FROM t_sys_user t_sys_user LEFT JOIN t_sys_role t_sys_role 
on t_sys_user.role_id=t_sys_role.id 
AND ((t_sys_user.USERNAME LIKE ? AND t_sys_role.NM LIKE ?))

← query.js 数据XML出参 →