於一句 同查本月存和上月存的
表1

品 存 日期
A001 as200 220 2005.04.06
A003 as210 0 2005.04.12
A003 as230 203 2005.04.18
A005 as230 240 2005.05.06
A006 as240 230 2005.05.25

怎按品同查 本月存和上月存？

如查2005年5月的果如下:

品 本月存 上月存
as200 220 0
as210 0 0
as230 240 203
....

要求用一句

[解决办法]
CREATE TABLE tb([] varchar(10), [品] varchar(10), [存] INT, [日期] DATETIME)
INSERT INTO tb
SELECT 'A001 ', 'as200 ', 220, '2005.04.06 '
UNION ALL SELECT 'A003 ', 'as210 ', 0, '2005.04.12 '
UNION ALL SELECT 'A003 ', 'as230 ', 203, '2005.04.18 '
UNION ALL SELECT 'A005 ', 'as230 ', 240, '2005.05.06 '
UNION ALL SELECT 'A006 ', 'as240 ', 230, '2005.05.25 '

DECLARE @date DATETIME
SET @date = '2005/05/01 '
SELECT T.品, 本月存 = ISNULL((SELECT SUM(存) FROM tb WHERE 品 = T.品 AND YEAR(日期) = YEAR(@date) AND MONTH(日期) = MONTH(@date)), 0)
, 上月存 = ISNULL((SELECT SUM(存) FROM tb WHERE 品 = T.品 AND YEAR(日期) = YEAR(DATEADD(MONTH, -1, @date)) AND MONTH(日期) = MONTH(DATEADD(MONTH, -1, @date))), 0)
FROM tb T

DROP TABLE tb
[解决办法]
结果：
品 本月存 上月存
---------- ----------- -----------
as200 0 220
as210 0 0
as230 240 203
as230 240 203
as240 230 0
[解决办法]
declare @a table( varchar(10),品 varchar(10), 存 int, 日期 datetime)
insert into @a
select 'A001 ', 'as200 ', 220 , '2005-04-06 '
union all
select 'A003 ', 'as210 ', 0 , '2005-04-12 '
union all
select 'A003 ', 'as230 ', 203 , '2005-04-18 '
union all
select 'A005 ', 'as230 ', 240 , '2005-05-06 '
union all
select 'A006 ', 'as240 ', 230 , '2005-05-25 '
select * from @a
select
[品]
,sum(select [存] from @a where datediff(month,[日期],getdate())=0 and T.[品]=[品]) as N '本月存 '
,sum(select [存] from @a where datediff(month,[日期],getdate())=-1 and T.[品]=[品]) as N '上月存 '
from @a T
group by [品]
[解决办法]
select 品
,isnull((select sum(isnull(存,0)) from T1 where datediff(month,日期,getdate())=0 and T.品=品),0) as 上月存
,isnull((select sum(isnull(存,0)) from T1 where datediff(month,日期,getdate())=-1 and T.品=品),0) as 本月存
from T1 T

[解决办法]
declare @ta TABLE ([] varchar(10), [品] varchar(10), [存] INT, [日期] DATETIME)
INSERT INTO @ta
SELECT 'A001 ', 'as200 ', 220, '2005.04.06 '

UNION ALL SELECT 'A003 ', 'as210 ', 0, '2005.04.12 '
UNION ALL SELECT 'A003 ', 'as230 ', 203, '2005.04.18 '
UNION ALL SELECT 'A005 ', 'as230 ', 240, '2005.05.06 '
UNION ALL SELECT 'A006 ', 'as240 ', 230, '2005.05.25 '

declare @time datetime
set @time= '2005.05.12 '--这里定义为系统时间就行getdate()就行了

select [品],
[本月存]=sum(case convert(varchar(7),@time,120) when convert(varchar(7),[日期],120)
then [存] else 0 end),
[上月存]=sum(case convert(varchar(7),dateadd(month,-1,@time),120) when convert(varchar(7),[日期],120)
then [存] else 0 end)
from @ta group by 品


(5 行受影响)
品 本月存 上月存
---------- ----------- -----------
as200 0 220
as210 0 0
as230 240 203
as240 230 0

(4 行受影响)