假设有如下两张表:
表A:
id type begin_date end_date count
---------------------------------------------
1 A 2007-5-12 2007-5-14 30
2 A 2007-5-11 2007-5-13 20
3 B 2007-5-12 2007-5-15 50
4 B 2007-5-13 2007-5-14 30
表示 从begin_date到end_date的时间段内,每天都会有30数量的A,其它行都是相同的意思
表B:(消耗表)
id type use_date count
------------------------------
1 A 2007-5-11 15
2 A 2007-5-12 15
3 B 2007-5-12 20
4 B 2007-5-13 30
表A和表B的id没有关系,现在要根据某一时间段,查询剩余数,比如:2007-5-11到2007-5-14的结余数量:
得到余下结果:
id type 2007-5-11 2007-5-12 2007-5-13 2007-5-14
1 A 5 35 50 30
2 B 0 30 50 80
其中2007-5-12,5-13,5-14是根据条件动态生成。。。。
怎么比较高效的实现上述功能。。。我想到的需要循环好几次,实在是够理想,请朋友们一起想想,或者,通过修改表结构,达到相同的目的
解决方法:
create
table
TableA(id
int
, type
char
(
1
), begin_date
smalldatetime
, end_date
smalldatetime
,
[
count
]
int
)
insert
TableA
select
1
,
'
A
'
,
'
2007-5-12
'
,
'
2007-5-14
'
,
30
union
all
select
2
,
'
A
'
,
'
2007-5-11
'
,
'
2007-5-13
'
,
20
union
all
select
3
,
'
B
'
,
'
2007-5-12
'
,
'
2007-5-15
'
,
50
union
all
select
4
,
'
B
'
,
'
2007-5-13
'
,
'
2007-5-14
'
,
30
go
create
table
TableB(id
int
, type
char
(
1
), use_date
smalldatetime
,
[
count
]
int
)
insert
TableB
select
1
,
'
A
'
,
'
2007-5-11
'
,
15
union
all
select
2
,
'
A
'
,
'
2007-5-12
'
,
15
union
all
select
3
,
'
B
'
,
'
2007-5-12
'
,
20
union
all
select
4
,
'
B
'
,
'
2007-5-13
'
,
30
go
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DECLARE
@BeginDate
smalldatetime
--
开始日期
DECLARE
@EndDate
smalldatetime
--
结束日期
DECLARE
@TmpDate
smalldatetime
DECLARE
@EXECUTE_SQL
nvarchar
(
4000
)
--
SELECT
@BeginDate
=
'
2007-5-11
'
,
@EndDate
=
'
2007-5-14
'
,
@TmpDate
=
@BeginDate
,
@EXECUTE_SQL
=
'
SELECT type
'
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE
TABLE
#T(TDate
smalldatetime
)
--
构造临时表,用于分类统计,和构造行列转换语句
WHILE
@TmpDate
<=
@EndDate
BEGIN
INSERT
INTO
#T
SELECT
@TmpDate
SELECT
@EXECUTE_SQL
=
@EXECUTE_SQL
+
'
,SUM(CASE TDATE WHEN
'''
+
CONVERT
(
nchar
(
10
),
@TmpDate
,
120
)
+
'''
THEN [count] ELSE 0 END) AS [
'
+
CONVERT
(
nchar
(
10
),
@TmpDate
,
120
)
+
'
]
'
,
@TmpDate
=
DATEADD
(
day
,
1
,
@TmpDate
)
END
SET
@EXECUTE_SQL
=
@EXECUTE_SQL
+
CHAR
(
10
)
+
'
FROM #T1 GROUP BY type
'
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--
没有行列转换前统计,插入表#T1
SELECT
type,TDate,
SUM
(
[
count
]
)
AS
[
count
]
INTO
#T1
FROM
(
SELECT
type,TDate,
[
count
]
FROM
TableA
CROSS
JOIN
#T
WHERE
(begin_date
BETWEEN
@BeginDate
AND
@EndDate
OR
end_date
BETWEEN
@BeginDate
AND
@EndDate
)
AND
TDate
BETWEEN
begin_date
AND
end_date
UNION
ALL
SELECT
type,use_date,
-
[
count
]
FROM
TableB
WHERE
use_date
BETWEEN
@BeginDate
AND
@EndDate
)
AS
A
GROUP
BY
type,TDate
ORDER
BY
type,TDate
EXECUTE
(
@EXECUTE_SQL
)
DROP
TABLE
TableA,TableB,#T,#T1
go
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/*
type 2007-05-11 2007-05-12 2007-05-13 2007-05-14
---------------------------------------------------------------------------
A 5 35 50 30
B 0 30 50 80
*/
以上方法没有使用到游标,只是使用到2张临时表就可以拷定,其实也可以使用1个临时表就可以,只不过为了方便更好的了解计算方法,把分类统计过程独立出来。
这方法虽然语句有点长吧,但我相信比使用游标更快。
问题来源:http://community.csdn.net/Expert/topic/5532/5532084.xml?temp=.7621729