yonql写法请教

BIP422709

请问我想查询主表按日期字段排序，以子表为条件查询数据该如何去写呀，比如查询物料的最新采购价，采购订单主表以单据日期排序子表中物料id等于目标物料id去查询子表的无税单价数据，该如何去写呢

1. let sql = "select oriTaxUnitPrice from pu.purchaseorder.PurchaseOrders where mainid in(select id from pu.purchaseorder.PurchaseOrder order by vouchdate desc        limit 1) and product =" +product
   

复制代码

这样写好像只能查到最新的采购订单里面的目标物料，如果最新的采购订单里没有这个物料就直接查询为空了

yonyouhmm

要以主表维度还是子表维度查询，没看明白

BIP422709

yonyouhmm · 2023-11-21 13:36要以主表维度还是子表维度查询，没看明白

以子表维度查询，查询子表中符合条件的并且符合条件的子表中子表的主表中单据日期为最新的

BIP422709

只能想到这个方法，不知道各位大佬还有更换的方法不

let sql = "select mainid from pu.purchaseorder.PurchaseOrders where product = "+ source1.value; //查询得到符合条件的所有子表的mainid数组
var res = ObjectStore.queryByYonQL(sql,'upu');
let arr_vouchdate = [];//新建个数组
res.map(function(item,index){
  let sql1 = "select vouchdate  from pu.purchaseorder.PurchaseOrder where id =" + item.mainid; //遍历数组查询主表的单据日期
let res1 = ObjectStore.queryByYonQL(sql1,'upu');
  arr_vouchdate.push({data:res1[0],id:item.mainid}) //将查询的日期和主表id加入新建的数组，数组中最后一个就是最新单据日期的数据
})

//arr_vouchdate[arr_vouchdate.length-1].id 就是子表符合条件的最新单据日期主表id

let sql2 = "select oriUnitPrice from pu.purchaseorder.PurchaseOrders where mainid in(select id from pu.purchaseorder.PurchaseOrder where id ="+arr_vouchdate[arr_vouchdate.length-1].id+") and product = "+ source1.value

  var res2 = ObjectStore.queryByYonQL(sql2,'upu');

let price = res2[0].oriUnitPrice

BIP422709

BIP422709 · 2023-11-21 16:09只能想到这个方法，不知道各位大佬还有更换的方法不

let sql = "select mainid from pu.purchaseorder.PurchaseOrders where product = "+ source1.value; //查询得到符合条件的所有子表的mainid数组

好像也不用新建数组遍历，因为最开始得到的数组res里最后一项就是所需要的主表id,直接这样写一下就行

1. let sql = "select mainid from pu.purchaseorder.PurchaseOrders where product = " + source1.value;
   
2. var res = ObjectStore.queryByYonQL(sql, 'upu');
   
3. let arr_vouchdate = [];
   
4. if (res.length != 0) {
   
5.         // res.map(function(item,index){
   
6.         //   let sql1 = "select vouchdate  from pu.purchaseorder.PurchaseOrder where id =" + item.mainid;
   
7.         //   var res1 = ObjectStore.queryByYonQL(sql1,'upu');
   
8.         //   arr_vouchdate.push({data:res1[0],id:item.mainid})
   
9.         // })
   
10.         let sql2 = "select oriUnitPrice from pu.purchaseorder.PurchaseOrders where mainid in(select id from pu.purchaseorder.PurchaseOrder where id =" + res[res.length - 1].mainid + ") and product = " + source1.value
    
11.         var res2 = ObjectStore.queryByYonQL(sql2, 'upu');
    
12.         let price = res2[0].oriUnitPrice
    
13.         if (price != undefined) {
    
14.                 assign = price
    
15.         }
    
16. }
    
17. }

复制代码