有2个表格
A:
Shop_cd mon Fromdate Todate
01 1 20060101 20060731
01 2 20060101 20060731
01 3 20060101 20060731
02 4 20060101 20060731
B:
Shop_cd year_A month_A value
01 2006 1 1
01 2006 2 2
01 2006 3 1
01 2006 6 1
01 2006 7 1
01 2006 8 1
02 2006 8 1然后我想得到下面的表格
Shop_cd year_A mon valueFrom_TO
01 2006 1 6
01 2006 2 6
01 2006 3 6
02 2006 4 NULL上面的month_A表示月份,year_A表示年,valueFrom_TO表示相同商店的情况下,时间范围内value的和 sql问应该怎写,谢谢
A:
Shop_cd mon Fromdate Todate
01 1 20060101 20060731
01 2 20060101 20060731
01 3 20060101 20060731
02 4 20060101 20060731
B:
Shop_cd year_A month_A value
01 2006 1 1
01 2006 2 2
01 2006 3 1
01 2006 6 1
01 2006 7 1
01 2006 8 1
02 2006 8 1然后我想得到下面的表格
Shop_cd year_A mon valueFrom_TO
01 2006 1 6
01 2006 2 6
01 2006 3 6
02 2006 4 NULL上面的month_A表示月份,year_A表示年,valueFrom_TO表示相同商店的情况下,时间范围内value的和 sql问应该怎写,谢谢
form a,b where 条件
group by mon
这句不理解...
insert A
select '01', 1, '20060101', '20060731'
union all select '01', 2, '20060101', '20060731'
union all select '01', 3, '20060101', '20060731'
union all select '02', 4, '20060101', '20060731'create table B(Shop_cd char(2), year_A int, month_A int, value int)
insert B
select '01', 2006, 1, 1
union all select '01', 2006, 2, 2
union all select '01', 2006, 3, 1
union all select '01', 2006, 6, 1
union all select '01', 2006, 7, 1
union all select '01', 2006, 8, 1
union all select '02', 2006, 8, 1select A.Shop_cd, tmp.year_A, mon, tmp.valueFrom_TO from A
left join
(
select Shop_cd, year_A, sum(value) as valueFrom_TO from B
where month_A between 1 and 7
group by Shop_cd, year_A
)tmp on A.Shop_cd=tmp.Shop_cd and year(cast(A.Fromdate as datetime))=tmp.year_Adrop table A
drop table B
INSERT INTO a
SELECT '01', 1, '20060101', '20060731'
UNION ALL SELECT '01', 2, '20060101', '20060731'
UNION ALL SELECT '01', 3, '20060101', '20060731'
UNION ALL SELECT '02', 4, '20060101', '20060731'CREATE TABLE b([Shop_cd] varchar(10), [year_A] int, [month_A] int, [value] int)
INSERT INTO b
SELECT '01', 2006, 1, 1
UNION ALL SELECT '01', 2006, 2, 2
UNION ALL SELECT '01', 2006, 3, 1
UNION ALL SELECT '01', 2006, 6, 1
UNION ALL SELECT '01', 2006, 7, 1
UNION ALL SELECT '01', 2006, 8, 1
UNION ALL SELECT '02', 2006, 8, 1SELECT Shop_cd, MON,
(SELECT SUM(value) FROM B WHERE YEAR_A>=DATEPART(YEAR,A.Fromdate) AND YEAR_A<=DATEPART(YEAR,A.Todate)
AND month_A>=DATEPART(MONTH,A.Fromdate) AND month_A<=DATEPART(MONTH,A.Todate) AND Shop_cd = A.Shop_cd) FROM A A
DROP TABLE a,b
month_A>=DATEPART(MONTH,A.Fromdate) AND month_A<=DATEPART(MONTH,A.Todate)
SELECT Shop_cd, MON,
(SELECT SUM(value) FROM B WHERE
CAST(CAST(YEAR_A AS VARCHAR) + '/' + CAST(month_A AS VARCHAR) + '/01' AS DATETIME) BETWEEN A.Fromdate AND A.Todate
AND Shop_cd = A.Shop_cd) FROM A A
转换一下时间select convert(varchar(8),getdate(),112)--8为显示的字符数,112时间格式
楼主可以查看一下联机帮助里很详细了关于convert的时间转换