如何将两张表查询的结果集和下一张表查询

时间:2022-06-19 14:51:50

思路: 将两张表查询出来的结果集用“()” 加个别名

sql:


var sqlText = "select ST_LOTPLAN.DESCRIPTION,(select DESCRIPTION from SITE where SITEID=ST_LOTPLAN.SITEID) AS SITE,"
+" MAXIMO.COMPANY(ST_LOTPLAN.COMPANY) as company,"
+" ST_LOTPLAN.BEGINTIME,/*计划开始日期*/"
+" ST_LOTPLAN.ENDTIME,/*计划结束时间*/"
+" MAXIMO.item(t1.ITEMNUM) as yyitem,"
+" t1.RKJS, t1.RKSL,"
+" t2.YYJS, t2.YYWEIGHT, t2.MYWEIGHT,"
+" t2.MGWEIGHT, t2.NSWEIGHT,"
+" t2.ZWWEIGHT, t2.ZRSHWEIGHT,"
+" MAXIMO.item(t3.ITEMNUM) as xhyitem,"
+" t3.XHYJS, t3.XHYWEIGHT"
+" from ST_LOTPLAN "
+" left outer join "
+" (select po.st_lotplanid,MATRECTRANS.ITEMNUM,"
+" sum(MATRECTRANS.ST_UNIT1QTY) as rkjs,/*原烟入库件数*/"
+" sum(MATRECTRANS.QUANTITY) as rksl/*原烟入库数量*/ "
+" from po "
+" inner join MATRECTRANS "
+" on PO.PONUM = MATRECTRANS.PONUM "
+" and PO.ST_ITEMTYPE = '原烟' "
+" and PO.RECEIPTS = '完成'"
+" group by po.st_lotplanid, MATRECTRANS.ITEMNUM) t1/*原烟入库*/"
+" on ST_LOTPLAN.ST_LOTPLANID = t1.ST_LOTPLANID"
+" left outer join "
+" (select a.ST_LOTPLANID,a.ST_YYITEMNUM,"
+" sum(a.ST_YYCOUNT) as yyjs,/*原烟件数*/"
+" sum(a.ST_YYWEIGHT) as yyweight,/*原烟重量*/"
+" sum(a.ST_BLEAFQTY) as myweight,/*霉烟重量*/"
+" sum(a.ST_BCABOQTY) as mgweight,/*霉梗重量*/"
+" sum(a.ST_SILTQTY) as nsweight,/*泥沙重量*/"
+" sum(a.ST_SUNDRIESQTY) as zwweight,/*杂物重量*/"
+" sum(a.ST_ULLAGEQTY) zrshweight/*自然损耗重量*/"
+" from po a where a.ST_ITEMTYPE='选后烟'"
+" group by a.ST_LOTPLANID,a.ST_YYITEMNUM) t2/*选叶原烟*/"
+" on ST_LOTPLAN.ST_LOTPLANID = t2.ST_LOTPLANID and t1.ITEMNUM=t2.ST_YYITEMNUM"
+" left outer join "
+" (select c.ST_LOTPLANID,c.ST_YYITEMNUM,b.ITEMNUM,"
+" sum(b.ST_UNIT1QTY) as xhyjs,/*选后烟件数*/"
+" sum(b.ORDERQTY) as xhyweight"
+" from POLINE b"
+" inner join po c"
+" on b.PONUM = c.PONUM and c.ST_ITEMTYPE='选后烟'"
+" group by c.ST_LOTPLANID,c.ST_YYITEMNUM,b.ITEMNUM)t3"
+" on ST_LOTPLAN.ST_LOTPLANID = t3.ST_LOTPLANID and t2.ST_YYITEMNUM = t3.ST_YYITEMNUM "
+" where 1=1 and "+params["where"]