这次没人能拿满分，题目有难度，同时也有陷阱，在入库明细表中输入两个记录日期为
2009-12-25
2009-12-27
这样只有dcw0402出来12月，在25号盘点中没有2010年1月的结余，同时期初日期设置为‘2008-12-31’是个很大的错误，日期大于25号，记录会移到2009年1月去，
小蚁雄兵的优点新颖，用窗体控件链接到单元格，定义两个区域名称，当做数据源在SQL语句中调用，因为数据源已作为OLE DB的连接源，所以这两个定义名称的数据源就要加上文件路径了。
附上各个SQL语句生成的数据透视表结余效果和源文件。
这里提供两个查询效果的各两个SQL语句（共4个）：
写法1：利用内层的数据同外层数据进行比较分别求出数量和金额的和，实现的相当于一旦有记录后，每个月进行数据累加功能
select * from
(
select W, Z,存货名称,单位,
(
select sum(D) from
(
select 存货编码 as W,year('1/1/2000')&"-"& month('1/1/2000') as Z,datevalue('1/1/2000')*1 as T,数量*1 as D, 金额*1 as P from [期初余额$]
UNION ALL
SELECT 存货编码, year(入库日期)&"-"& month(入库日期),datevalue(入库日期)*1 as T,数量*1,单价 *数量 FROM [入库明细表$]
UNION ALL
SELECT 存货编码, year(出库日期)&"-"& month(出库日期),datevalue(出库日期)*1 , 数量*(-1),单价*数量*(-1)
FROM [出库明细表$]
)
where T<=b.S and W=b.W
) as D2,
(
select sum(P) from
(
select 存货编码 as W,year('1/1/2000')&"-"& month('1/1/2000') as Z,datevalue('1/1/2000')*1 as T,数量*1 as D, 金额*1 as P
from [期初余额$]
UNION ALL
SELECT 存货编码, year(入库日期)&"-"& month(入库日期),datevalue(入库日期)*1 as T,数量*1,单价 *数量 FROM [入库明细表$]
UNION ALL
SELECT 存货编码, year(出库日期)&"-"& month(出库日期),datevalue(出库日期)*1 , 数量*(-1),单价*数量*(-1)
FROM [出库明细表$]
)
where T<=b.S and W=b.W
) as P2
from
(
select * from
(
select Z,max(T) as S from
(
SELECT '1/1/2000' as R,year(R)&"-"& month(R) as Z,datevalue(R)*1 as T
from [期初余额$]
UNION ALL
SELECT 入库日期 as R,year(R)&"-"& month(R),datevalue(R)*1
FROM [入库明细表$]
UNION ALL
SELECT 出库日期 as R,year(R)&"-"& month(R),datevalue(R)*1
FROM [出库明细表$]
)
group by Z
) e,
(
SELECT 存货编码 as W
from [期初余额$]
UNION
SELECT 存货编码
FROM [入库明细表$]
UNION
SELECT 存货编码
FROM [出库明细表$]
)
) b, [物料编码$] f
where b.W=f.存货编码
)
where D2<>0
写法2： format(T,'yyyy-mm') 这里用了两个m就是为了使字符数完全相同，为后面进行比较提供数据。
该方法就是将几个数据源进行组合，再有条件地将有用的数据框出来，然后对数据进行分组求和。
select w,z,D2,P2,存货名称,单位 from
(
select a.w,b.z, sum(D) as D2,sum(P) as P2
from
(
select 存货编码 as W,datevalue('1/1/2000')*1 as T,format(T,'yyyy-mm') as z,数量*1 as D, 金额*1 as P
from [期初余额$]
UNION ALL
SELECT 存货编码,datevalue(入库日期)*1 as T,format(T,'yyyy-mm'),数量*1,单价 *数量 FROM [入库明细表$]
UNION ALL
SELECT 存货编码,datevalue(出库日期)*1 as T,format(T,'yyyy-mm'), 数量*(-1),单价*数量*(-1)
FROM [出库明细表$]
) a,
(
select Z
from
(
SELECT datevalue('1/1/2000')*1 as T,format(T,'yyyy-mm') as z
from [期初余额$]
UNION ALL
SELECT datevalue(入库日期)*1 as T,format(T,'yyyy-mm')
FROM [入库明细表$]
UNION ALL
SELECT datevalue(出库日期)*1 as T,format(T,'yyyy-mm')
FROM [出库明细表$]
)
group by Z
) b
where a.z<=b.z
group by a.w,b.z
) j,[物料编码$] f
where D2<>0 and j.W=f.存货编码
25号盘点：
写法1：利用内层的数据同外层数据进行比较分别求出数量和金额的和，实现的相当于一旦有记录后，每个月进行数据累加功能，IIF(day(R)>25,7,0) 确定累加值多少。7=31-25+1，DateAdd('d',IIF(day(R)>25,7,0),R) 如果当月日期小于25就不加，大于25就加7，使其日期落到下个月
select * from
(
select W, Z,存货名称,单位,
(
select sum(D)
from
(
select '1/1/2000' as R,存货编码 as W,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-m') as Z,数量*1 as D,金额*1 as P
from [期初余额$]
UNION ALL
SELECT 入库日期 as R,存货编码,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-m') ,数量*1,单价 *数量 FROM [入库明细表$]
UNION ALL
SELECT 出库日期 as R,存货编码,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-m'),数量*(-1),单价*数量*(-1)
FROM [出库明细表$]
)
where T<=b.S and W=b.W
) as D2,
(
select sum(P) from
(
select '1/1/2000' as R,存货编码 as W,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-m') as Z,数量*1 as D,金额*1 as P
from [期初余额$]
UNION ALL
SELECT 入库日期 as R,存货编码,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-m') ,数量*1,单价 *数量 FROM [入库明细表$]
UNION ALL
SELECT 出库日期 as R,存货编码,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-m'),数量*(-1),单价*数量*(-1)
FROM [出库明细表$]
)
where T<=b.S and W=b.W
) as P2 from
(
select * from (
select Z,max(T) as S
from
(
select '1/1/2000' as R,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-m') as Z
from
[期初余额$]
UNION ALL
SELECT 入库日期 as R,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-m')
FROM [入库明细表$]
UNION ALL
SELECT 出库日期 as R,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-m')
FROM [出库明细表$]
)
group by Z
) e,
(
SELECT 存货编码 as W
from [期初余额$]
UNION
SELECT 存货编码
FROM [入库明细表$]
UNION
SELECT 存货编码
FROM [出库明细表$]
)
) b, [物料编码$] f
where b.W=f.存货编码
)
where D2<>0
写法2： IIF(day(R)>25,7,0) 确定累加值多少。7=31-25+1，DateAdd('d',IIF(day(R)>25,7,0),R) 如果当月日期小于25就不加，大于25就加7，使其日期落到下个月，format(T,'yyyy-mm') 这里用了两个m就是为了使字符数完全相同，为后面进行比较提供数据。
该方法就是将几个数据源进行组合，再有条件地将有用的数据框出来，然后对数据进行分组求和。
select w,z,D2,P2,存货名称,单位 from
(
select a.w,b.z, sum(D) as D2,sum(P) as P2
from
(
select 存货编码 as W,'1/1/2000' as R,dateadd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-mm') as z,数量*1 as D, 金额*1 as P
from [期初余额$]
UNION ALL
SELECT 存货编码,入库日期 as R,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-mm'),数量*1,单价 *数量 FROM [入库明细表$]
UNION ALL
SELECT 存货编码,出库日期 as R,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-mm'), 数量*(-1),单价*数量*(-1)
FROM [出库明细表$]
) a,
(
select Z
from
(
SELECT '1/1/2000' as R,dateadd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-mm') as z
from [期初余额$]
UNION ALL
SELECT 入库日期 as R,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-mm')
FROM [入库明细表$]
UNION ALL
SELECT 出库日期 as R,DateAdd('d',IIF(day(R)>25,7,0),R) as T,format(T,'yyyy-mm')
FROM [出库明细表$]
)
group by Z
) b
where a.z<=b.z
group by a.w,b.z
) j,[物料编码$] f
where D2<>0 and j.W=f.存货编码 |
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-6-21 00:51
楼主