1月
21
2006
分類:
Programming
標籤:
最近更新: 2006-01-21

SQL~~以 AS 將子查詢結果作為主查詢的參考表格

在子查詢中，作每一訂單的個別商品銷售數量與單項價格之加總 (求個別訂單下個別商品銷售總金額) ，將子查詢結果以 AS 命名為新表格 (只存在於查詢期間的表格)，做主查詢的參考表格後，再求總店銷售額。 

select pos324_tamt.shop, pos324_tamt.sname, pos324_tamt.pluno, pos324_tamt.pname, 
sum(pos324_tamt.qty) as total_qty,
sum(pos324_tamt.tamt) as total_tamt
from (
    select pos324.shop, pos104.sname, pos116.pluno, pos110.pname, pos324.qty, (pos324.qty*pos324.sale_price) as tamt from pos324 
    inner join (pos116 inner join pos110 on pos116.pluno = pos110.pluno) on pos324.pluno = pos116.pluno 
    inner join pos104 on pos324.shop = pos104.shop
where pos324.ecrdate >= '20060101' and pos324.ecrdate <= '20060119') as pos324_tamt
group by pos324_tamt_shop, pos324_tamt.sname, pos324_tamt.pluno, pos324_tamt.pname
order by pos324_tamt.shop, pos324_tamt.pluno
相關文章
樂多舊網址: http://blog.roodo.com/rocksaying/archives/1031693.html