如何获得这个select语句？

问题描述

我有两张桌子



PR_DET（IT_Code，ReqQty，ReqDate，IT_Desc）



PO_DET （IT_Code，IT_Status，PO_Qty）







我想展示



来自PR_DET的IT_Code，ReqQty，ReqDate，IT_Desc



并想再添加一列Qty_Underpurchase

显示sum（PO_Qty）其中PO_DET.IT_Status ='UnderPurchase'



我怎么能得到这个请帮帮我？

I have two table

PR_DET(IT_Code,ReqQty,ReqDate, IT_Desc)

PO_DET(IT_Code,IT_Status, PO_Qty)



I want to display

IT_Code, ReqQty, ReqDate, IT_Desc from PR_DET

and want add one more column Qty_Underpurchase
showing sum(PO_Qty) where PO_DET.IT_Status = 'UnderPurchase'

how can i achive this please help me out?

推荐答案

SELECT pr.IT_Code, pr.ReqQty, pr.ReqDate, pr.IT_Desc, SUM(po.PO_Qty) AS Qty_Underpurchase FROM PR_DET AS pr
INNER JOIN PO_DET AS po ON po.IT_Code = pr.IT_Code
WHERE po.IT_Status = 'UnderPurchase'
GROUP BY pr.IT_Code, pr.ReqQty, pr.ReqDate, pr.IT_Desc

For Joins： [ ]



如果你使用聚合函数（比如 SUM 就是一个）你要显示的所有其他列都需要既可以是聚合函数也可以是GROUP BY子句。



编辑：修正了SQL（缺少）;））

For Joins: http://technet.microsoft.com/en-us/library/aa213233(v=sql.80).aspx[^]

If you use an aggregate function (like SUM is one) all other columns you want to show need to be either an aggregate function too or be in the GROUP BY clause.

Fixed the SQL (was missing a ")" ;) )

这篇关于如何获得这个select语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！