SELECT a.Code AS Code, a.Name AS Name, a.Amount AS Amount, SUM(b.SellAmount) AS SumSellAmount, SUM(c.BuyAmount) AS SumBuyAmount FROM TABLE1 a, TABLE2 b, TABLE3 c WHERE b.Code = a.Code AND c.Code = a.Code
select 表1.code,name,amount,sumsellamount,sumbuyamout from 表1,表2,表3 where 表1.code=表2.code,表2.code=表3.code
SELECT a.Code , a.Name , a.Amount , SUM(b.SellAmount) AS SumSellAmount, SUM(c.BuyAmount) AS SumBuyAmount FROM TABLE1 a, TABLE2 b, TABLE3 c WHERE b.Code = a.Code and c.Code = a.Code group by a.Code.a.Amount order by a.Code
方法1: SELECT a.Code , a.Name , a.Amount , SumSellAmount=(select SUM(b.SellAmount) from table2 b where a.code=b.code), SumBuyAmount=(select SUM(c.BuyAmount) from table3 c where a.code=c.code) FROM TABLE1 a order by a.Code方法2: 1.建视图 goodview: select a.code as code , a.amount as amount, 0 as sellamount, 0 as buyamount from table1 a union select b.code, 0, b.sellamount, 0 from table2 b union select c.code, 0, 0, c.buyamount from table3 c 2.程序中这样调用 select d.code, name=(select e.name from table1 e where e.code=d.code), sum(d.amount), sum(sellamount), sum(buyamount) from goodview d order by d.code 这样肯定是可以的 如还行,请给我EML:[email protected]
我有类似的操作,代码如下: SELECT SELECT 表1.Code , 表1.Name , 表1.Amount , SUM(表2.SellAmount) AS SumSellAmount, SUM(表3.BuyAmount) AS SumBuyAmount FROM 表1 INNER JOIN 表2 ON 表1.Code = 表2.Code INNER JOIN 表3 ON 表1.Code =表3.Code GROUP BY 表1.Code order by 表1.Code
表1.code=表2.code,表2.code=表3.code
SELECT a.Code , a.Name , a.Amount , SUM(b.SellAmount) AS SumSellAmount, SUM(c.BuyAmount) AS SumBuyAmount
FROM TABLE1 a, TABLE2 b, TABLE3 c
WHERE b.Code = a.Code and
c.Code = a.Code
group by a.Code.a.Amount
order by a.Code
SELECT a.Code , a.Name , a.Amount , SumSellAmount=(select SUM(b.SellAmount) from table2 b where a.code=b.code), SumBuyAmount=(select SUM(c.BuyAmount) from table3 c where a.code=c.code)
FROM TABLE1 a
order by a.Code方法2:
1.建视图 goodview:
select a.code as code , a.amount as amount, 0 as sellamount, 0 as buyamount
from table1 a
union
select b.code, 0, b.sellamount, 0 from table2 b
union
select c.code, 0, 0, c.buyamount from table3 c
2.程序中这样调用
select d.code, name=(select e.name from table1 e where e.code=d.code),
sum(d.amount), sum(sellamount), sum(buyamount) from goodview d
order by d.code
这样肯定是可以的
如还行,请给我EML:[email protected]
SELECT SELECT 表1.Code , 表1.Name , 表1.Amount , SUM(表2.SellAmount) AS SumSellAmount, SUM(表3.BuyAmount) AS SumBuyAmount
FROM 表1 INNER JOIN 表2 ON 表1.Code = 表2.Code INNER JOIN 表3 ON 表1.Code =表3.Code
GROUP BY 表1.Code
order by 表1.Code