这两个查询有办法合在一起吗?(50分)

  • 主题发起人 主题发起人 Adnil
  • 开始时间 开始时间
A

Adnil

Unregistered / Unconfirmed
GUEST, unregistred user!
--取上月总公司销售量
SELECT @iLastAmount = ISNULL(SUM(B.Amount), 0) FROM SaleGoods A, SaleGoodsInfo B WHERE A.SaleGoodsID = B.SaleGoodsID AND A.MakeDate BETWEEN DateAdd(Month, -1, @Date1) AND DateAdd(Month, -1, @Date2)
PRINT @iLastAmount

--取当月总公司销售量
SELECT @iThisAmount = ISNULL(SUM(B.Amount), 0) FROM SaleGoods A, SaleGoodsInfo B WHERE A.SaleGoodsID = B.SaleGoodsID AND A.MakeDate BETWEEN @Date1 AND @Date2
PRINT @iThisAmount
 
select aa.lastamount,bb.thisamount
from
(SELECT @iLastAmount = (ISNULL(SUM(B.Amount), 0)) as lastamount FROM SaleGoods A, SaleGoodsInfo B WHERE A.SaleGoodsID = B.SaleGoodsID AND A.MakeDate BETWEEN DateAdd(Month, -1, @Date1) AND DateAdd(Month, -1, @Date2)) as aa,
(SELECT @iThisAmount = (ISNULL(SUM(B.Amount), 0)) as thisamount FROM SaleGoods A, SaleGoodsInfo B WHERE A.SaleGoodsID = B.SaleGoodsID AND A.MakeDate BETWEEN @Date1 AND @Date2) as bb
 
感觉很困难.因为SELECT 中是求和,而不是表中的某个字段,
无法在WHERE子句中建立两个求和值各自与表的联系...
 
SELECT (
SELECT
@iLastAmount = ISNULL(SUM(B.Amount), 0)
FROM
SaleGoods A, SaleGoodsInfo B
WHERE
A.SaleGoodsID = B.SaleGoodsID
AND A.MakeDate BETWEEN DateAdd(Month, -1, @Date1) AND DateAdd(Month, -1, @Date2)
PRINT @iLastAmount) AS @iLastAmount,
(
SELECT
@iThisAmount = ISNULL(SUM(B.Amount), 0)
FROM
SaleGoods A, SaleGoodsInfo B
WHERE
A.SaleGoodsID = B.SaleGoodsID
AND A.MakeDate BETWEEN @Date1 AND @Date2
PRINT @iThisAmount) AS @iThisAmount
FROM SaleGoods A, SaleGoodsInfo B
PRINT @iLastAmount, @iThisAmount
 
上面的两个合并意义不是很大,仍然有两个需要连接的查询,我是想提高效率。

还是谢谢上面各位 :)

期待更好的方法。。。
 
用存储过程建立一个temp table ,然后insert into xxxx(tt) select * ………………

最后select * from xxxx
 
提高效率的話,你的代碼中就有現成的:將BETWEEN 換成 >= <=
另這兩個語句當然可以合並:你參考一下下面的這個語句:
select sum(Move_Qty),datepart(YYYY,Move_Time),datepart(MM,Move_Time) from Proc_Moveh
Group by datepart(YYYY,Move_Time),datepart(MM,Move_Time)

按年月分組統計數量,你要語句自己再改寫一下吧
 
SELECT ISNULL(SUM(B.Amount), 0)
FROM SaleGoods A,SaleGoodsInfo B
WHERE A.SaleGoodsID = B.SaleGoodsID
GROUP BY TO_DATE(A.MakeDate,'YYYYMM')
就是取月份为分组条件,可以一次查出每个月的总公司销售量,
如果需要的月份不多,可以在WHERE里加条件限制。
 
luojf999 的回答考慮不夠完整:如果數據表裡面的數據跨年了呢?
 
select 的多层嵌套吧。多调试。
 
SELECT tt, (DATENAME(yyyy,dt)+''.''+DATENAME(mm,dt)) as 月份,sum(count) as sl7,sum(mon) as je7 from syfpk
group by tt,(DATENAME(yyyy,dt)+''.''+DATENAME(mm,dt))
这样可以
 
楼上各位,我只要查询本月和上月的数据,不用把所有的月都取出来,就算都能取出来,
我还得创建游标来判断读取数据,效率更低了。
 
select
union
select
 
SELECT
@iLastAmount = ISNULL(SUM(CASE WHEN A.MakeDate BETWEEN @Date1 AND @Date2 THEN 0 ELSE B.Amount END), 0),
@iThisAmount = ISNULL(SUM(CASE WHEN A.MakeDate BETWEEN @Date1 AND @Date2 THEN B.Amount ELSE 0 END), 0)
FROM
SaleGoods A, SaleGoodsInfo B
WHERE
A.SaleGoodsID = B.SaleGoodsID
AND (
A.MakeDate BETWEEN DateAdd(Month, -1, @Date1) AND DateAdd(Month, -1, @Date2)
OR A.MakeDate BETWEEN @Date1 AND @Date2
)
 
SELECT ISNULL(SUM(B.Amount), 0)
FROM SaleGoods A,SaleGoodsInfo B
WHERE A.SaleGoodsID = B.SaleGoodsID
AND ( A.MakeDate BETWEEN
TO_DATE(add_months(SYSDATE,-1),'YYYYMM') AND TO_DATE(SYSDATE,'YYYYMM') )
GROUP BY TO_DATE(A.MakeDate,'YYYYMM')
 
接受答案了
 
后退
顶部