一个表汇总查询后的结果与另外一个表关联
第一个表prdt1 Aselect A.prd_no,sum(QTY) as 现存量,sum(QTY_ON_WAY) AS 在途量,sum(QTY_ON_RSV) AS 未发量 from prdt1 AWHERE A.PRD_NO LIKE '308%' GROUP BY A.PRD_NO另外一个表prdt B
PRD_NO NAME SPC(备注型text)我想两个表横向关联联结果得到:A.prd_no,sum(QTY) as 现存量,sum(QTY_ON_WAY) AS 在途量,sum(QTY_ON_RSV) AS 未发量,B.NAME,B.SPC如果用语句查询?
第一个表prdt1 Aselect A.prd_no,sum(QTY) as 现存量,sum(QTY_ON_WAY) AS 在途量,sum(QTY_ON_RSV) AS 未发量 from prdt1 AWHERE A.PRD_NO LIKE '308%' GROUP BY A.PRD_NO另外一个表prdt B
PRD_NO NAME SPC(备注型text)我想两个表横向关联联结果得到:A.prd_no,sum(QTY) as 现存量,sum(QTY_ON_WAY) AS 在途量,sum(QTY_ON_RSV) AS 未发量,B.NAME,B.SPC如果用语句查询?
*
from
(select A.prd_no,sum(QTY) as 现存量,sum(QTY_ON_WAY) AS 在途量,sum(QTY_ON_RSV) AS 未发量 from prdt1 a
WHERE A.PRD_NO LIKE '308%' GROUP BY A.PRD_NO)a
left join b
on
a.prd_no=b.prd_no
*
from
(select A.prd_no,sum(QTY) as 现存量,sum(QTY_ON_WAY) AS 在途量,sum(QTY_ON_RSV) AS 未发量 from prdt1 a
WHERE A.PRD_NO LIKE '308%' GROUP BY A.PRD_NO)a
left join prdt b
on
a.prd_no=b.prd_no
select A.prd_no,sum(QTY) as 现存量,sum(QTY_ON_WAY) AS 在途量,sum(QTY_ON_RSV) AS 未发量 from prdt1 A
WHERE A.PRD_NO LIKE '308%' GROUP BY A.PRD_NO
)
select a.*,b.name,b.spc
from cte a,prdt B
where a.prd_no= b.prd_no
from prdt b,
(select A.prd_no as prd_no,sum(QTY) as 现存量,sum(QTY_ON_WAY) AS 在途量,sum(QTY_ON_RSV) AS 未发量 from prdt1 A
WHERE A.PRD_NO LIKE '308%' GROUP BY A.PRD_NO) c
where b.prd_no = c.prd_no
from prdt1 A join prdt B on A.prd_no = B.prd_no
WHERE A.PRD_NO LIKE '308%' GROUP BY A.PRD_NO
select A.prd_no,sum(QTY) as 现存量,sum(QTY_ON_WAY) AS 在途量,sum(QTY_ON_RSV) AS 未发量,B.NAME,B.SPC
from(
select A.prd_no,sum(QTY) as 现存量,sum(QTY_ON_WAY) AS 在途量,sum(QTY_ON_RSV) AS 未发量 from prdt1 A
WHERE A.PRD_NO LIKE '308%' GROUP BY A.PRD_NO
)A inner join prdt b on a.prd_no=b.prd_no