注册登录

3***@qq.com

发布：2022-10-27 09:27
更新：2022-10-31 13:25
阅读：390

求教下 jql里有像类似 sql的 union 语法吗

分类：uniCloud

uniCloud

求教下 jql里有像类似 sql的 union 语法吗
已解决

        const res = await db.collection('book').aggregate()  
            .group({  
                _id: {  
                    author: '$author',  
                    title: '$title'  
                },  
                count1: $.sum('$quantity')  
            })  
            .lookup({  
                from: 'book2',  
                let: {  
                    book_title: '$_id.title',  
                    book_author: '$_id.author',  
                },  
                pipeline: $.pipeline()  
                    .match(dbCmd.expr($.and([  
                        $.eq(['$title', '$$book_title']),  
                        $.eq(['$author', '$$book_author'])  
                    ])))  
                    .done(),  
                as: 'count2_list',  

            })  
            .project({  
                count1: true,  
                count2: $.reduce({  
                    input: '$count2_list',  
                    initialValue: 0,  
                    in: $.sum(['$$value', '$$this.quantity'])  
                })  
            }))  
            .end()

这样的代码可以，有需要的来看下

2022-10-27 09:27 负责人:无分享

6 个回复

DCloud_uniCloud_WYQ

不要重复发同样的帖子，按照我发的链接里面的写法是能写出来的。另外注意这个语句在数据量大的时候全表计算会比较慢

'use strict';  
const db = uniCloud.database()  
exports.main = async (event, context) => {  
  const $ = db.command.aggregate  
  const res = await db.collection('book1').aggregate().group({  
      _id: '$author',  
      count1: $.sum('$quantity')  
    })  

    .lookup({  
      from: 'book2',  
      localField: '_id',  
      foreignField: 'author',  
      as: 'count2_list'  
    })  
    .project({  
      count1: true,  
      count2: $.reduce({  
        input: '$count2_list',  
        initialValue: 0,  
        in: $.sum(['$$value', '$$this.quantity'])  
      })  
    })  
    .end()  
  return {  
    res  
  }  
};

3***@qq.com (作者)

感谢你的回复，

var $ = db.command.aggregate

const res = await db.collection('book').aggregate().group({

_id: {author:'$author',title:'$title'},

count1: $.sum('$数量')

})

.lookup({

from: 'book2',

localField: '_id',

foreignField: 'author',

as: 'count2_list' ,

})

.project({

count1: true,

count2: $.reduce({

input: '$count2_list',

initialValue: 0,

in: $.sum(['$$value', '$$this.数量'])

})

})

.end() 我这样改了下代码，count1是我想要的结果，但是这样写 foreignField 怎么匹配

2022-10-31 15:15

3***@qq.com (作者)

    var $ = db.command.aggregate  

      const res = await db.collection('book').aggregate().group({    

          _id: {author:'$author',title:'$title'},  

          count1: $.sum('$数量')    

        })            

        .lookup({    

          from: 'book2',    

          let: {  

                book_title: '$title',  

                book_author: '$author'  

              },  

              pipeline:$.pipeline()  

                    .match(dbCmd.expr([  

                      $.eq(['$title', '$$book_title']),  

                      $.eq(['$author', '$$book_author'])  

                    ]))  

                    .done(),  

          //localField: '_id',    

          //foreignField: 'author',     

          as: 'count2_list' ,  



        })    

        .project({    

          count1: true,    

          count2: $.reduce({    

            input: '$count2_list',    

            initialValue: 0,    

            in: $.sum(['$$value', '$$this.数量'])    

          })    

        })    

        .end() 改用 pipeline 这个方法的话 count2 不是分组查询之后的结果，是这个book2表的sum值

2022-10-31 15:27

DCloud_uniCloud_WYQ

回复 3***@qq.com: dbCmd.expr 里面再套一个$.and([])

2022-10-31 15:33

3***@qq.com (作者)

const res = await db.collection('book').aggregate().group({

_id: {author:'$author',title:'$title'},

count1: $.sum('$数量')

})

.lookup({

from: 'book2',

let: {

book_title: '$title',

book_author: '$author'

},

pipeline:$.pipeline()

.match(dbCmd.expr($.and([

$.eq(['$title', '$$book_title']),

$.eq(['$author', '$$book_author'])

])))

.done(),

as: 'count2_list' ,

        })    

        .project({    

          count1: true,    

          count2: $.reduce({    

            input: '$count2_list',    

            initialValue: 0,    

            in: $.sum(['$$value', '$$this.数量'])    

          })    

        })    

        .end()  我代码按照大佬的指点改了，但是表book2没有匹配到book，

2022-10-31 17:21

DCloud_uniCloud_WYQ

回复 3***@qq.com: 字段名改成英文，不要用“数量” 这种名字

2022-11-01 11:44

DCloud_uniCloud_WYQ

lookup方法很强大，一般能用sql实现联表功能用lookup方法都能实现。你写个你需要的sql的例子出来

3***@qq.com (作者)

SELECT a.id,a.产品ID, a.货位号,a.库存数量,a.库存数量-(SELECT IFNULL(SUM(b.数量),0) FROM 出单 AS b WHERE b.产品ID=a.产品ID AND b.货位号=a.货位号 AND b.类型 = '销售出库' AND (ISNULL(b.完成) OR b.完成=0 )) +(SELECT IFNULL(SUM(c.数量),0) FROM 订单 AS c WHERE c.产品ID=a.产品ID AND c.货位号=a.货位号 AND c.类型 = '采购退出' AND (ISNULL(c.完成) OR c.完成=0 )) AS 可用库存 FROM 查询库存 AS a

这种sql语句怎么用jql实现

2022-10-27 13:45
3***@qq.com (作者)

CREATE TEMPORARY TABLE ls AS （SELECT 产品ID,原料ID,SUM(a数量).b数量 AS 总数 FROM bom WHERE 产品ID IN ('产品ID') GROUP BY 原料ID UNION ALL SELECT 产品ID,原料ID,SUM(b数量).c数量 AS 总数 FROM bom WHERE 产品ID IN ('产品ID') GROUP BY 原料ID）

SELECT ls.原料ID,产品.产品名称,产品.规格,产品.型号,产品.预警数量,a.总库存,SUM(总数) AS 数量 FROM ls INNER JOIN 产品 ON ls.原料ID=产品.产品ID INNER JOIN (SELECT id,产品ID, IFNULL(SUM(库存数量),0) AS 总库存 FROM 查询库存 GROUP BY 产品ID )AS a ON ls.原料ID=a.产品ID GROUP BY ls.原料ID

上面这两个sql语句，用jql要怎么实现啊

2022-10-27 13:45

3***@qq.com (作者)

SELECT a.id,a.产品ID, a.货位号,a.库存数量,a.库存数量-(SELECT IFNULL(SUM(b.数量),0) FROM 出单 AS b WHERE b.产品ID=a.产品ID AND b.货位号=a.货位号 AND b.类型 = '销售出库' AND (ISNULL(b.完成) OR b.完成=0 )) +(SELECT IFNULL(SUM(c.数量),0) FROM 订单 AS c WHERE c.产品ID=a.产品ID AND c.货位号=a.货位号 AND c.类型 = '采购退出' AND (ISNULL(c.完成) OR c.完成=0 )) AS 可用库存 FROM 查询库存 AS a

这种sql语句怎么用jql实现

3***@qq.com (作者)

CREATE TEMPORARY TABLE ls AS （SELECT 产品ID,原料ID,SUM(a数量).b数量 AS 总数 FROM bom WHERE 产品ID IN ('产品ID') GROUP BY 原料ID UNION ALL SELECT 产品ID,原料ID,SUM(b数量).c数量 AS 总数 FROM bom WHERE 产品ID IN ('产品ID') GROUP BY 原料ID）

SELECT ls.原料ID,产品.产品名称,产品.规格,产品.型号,产品.预警数量,a.总库存,SUM(总数) AS 数量 FROM ls INNER JOIN 产品 ON ls.原料ID=产品.产品ID INNER JOIN (SELECT id,产品ID, IFNULL(SUM(库存数量),0) AS 总库存 FROM 查询库存 GROUP BY 产品ID )AS a ON ls.原料ID=a.产品ID GROUP BY ls.原料ID

上面这两个sql语句，用jql要怎么实现啊

DCloud_uniCloud_WYQ

看下这个帖子里面是不是你要的 https://ask.dcloud.net.cn/question/156169

3***@qq.com (作者)

根据帖子上的方法试了试，用lookup 这个方法，localField foreignField 2个字段名字一样就报错，用jql也是同样报错，搞了一天了，这个比sql难懂多了

2022-10-28 13:54
3***@qq.com (作者)

并非多复杂的查询，好难搞啊

2022-10-28 13:54
3***@qq.com (作者)

如果，服务器对返回条数没有限制的话，const intoQuery = await dbJQL.collection('book').where(where)

.groupBy('author,title').groupField('sum(数量) as 入总数').get()

const outQuery = await dbJQL.collection('book2').where(where)

.groupBy('author,title').groupField('sum(数量) as 出总数').get()

let shuju = intoQuery.data.map((v) => {

let obj = outQuery.data.find(o => o.author === v.author && o.title === v.title)

let czs = typeof(obj) == 'object' ? obj.出总数 : 0

let kcs = {

出总数: czs,

库存: v.入总数 - czs

}

我可以用这个方法，有限制只能靠数据库先算出来再计算了

2022-10-28 13:56
DCloud_uniCloud_WYQ

回复 3***@qq.com: 把你真实查询用的语句写出来，不要带这些中文字段

2022-10-28 14:07
3***@qq.com (作者)

回复 DCloud_uniCloud_WYQ: const intoQuery = await dbJQL.collection('book')

.groupBy('author,title').groupField('sum(number) as inTotal').get()

const outQuery = await dbJQL.collection('book2')

.groupBy('author,title').groupField('sum(number) as outTotal').get()

let shuju = intoQuery.data.map((v) => {

let obj = outQuery.data.find(o => o.author === v.author && o.title === v.title)

let czs = typeof(obj) == 'object' ? obj.outTotal : 0

let kcs = {

outTotal: czs,

stock: v.inTotal - czs

}

我这个查询最后用map方法把两个表合并在了一起，用jql这个要怎么写代码啊

2022-10-28 15:53

3***@qq.com (作者)

const intoQuery = await dbJQL.collection('book')

.groupBy('author,title').groupField('sum(number) as inTotal').get()

const outQuery = await dbJQL.collection('book2')

.groupBy('author,title').groupField('sum(number) as outTotal').get()

let shuju = intoQuery.data.map((v) => {

let obj = outQuery.data.find(o => o.author === v.author && o.title === v.title)

let czs = typeof(obj) == 'object' ? obj.outTotal : 0

let kcs = {

outTotal: czs,

stock: v.inTotal - czs

}
我这个查询最后用map方法把两个表合并在了一起，用jql这个要怎么写代码啊

要回复问题请先登录或注册

求教下 jql里有像类似 sql的 union 语法吗

6 个回复

公告

相关问题

求教下 jql里有像类似 sql的 union 语法吗

与内容相关的链接

6 个回复

公告

相关问题