表A中有很多单：
如：
ID 开始日期 结束日期 金额
1 2007-5-6 2007-8-23 30000
2 2007-7-16 2007-8-19 30000
3 2007-8-23 2007-12-11 30000
4 2007-5-9 2007-8-21 30000
5 2007-11-6 2008-11-23 30000
如果查询中输入起止日期：如：从2007-5-18至2008-1-23按周来统计金额，这个SQL应该怎样写呢？
那么记录5就只能为2007－11－6到2008-1-23了，金额变为：(30000/(2008-11-23)-2007-11-6)) *((2008-1-23)-2007-11-6
如：
ID 开始日期 结束日期 金额
1 2007-5-6 2007-8-23 30000
2 2007-7-16 2007-8-19 30000
3 2007-8-23 2007-12-11 30000
4 2007-5-9 2007-8-21 30000
5 2007-11-6 2008-11-23 30000
如果查询中输入起止日期：如：从2007-5-18至2008-1-23按周来统计金额，这个SQL应该怎样写呢？
那么记录5就只能为2007－11－6到2008-1-23了，金额变为：(30000/(2008-11-23)-2007-11-6)) *((2008-1-23)-2007-11-6
--你没说出具体的计算方法,各种情况应该如何计算,这个是例,自己参照着改.
create table tb(ID int,开始日期 datetime , 结束日期 datetime , 金额 int)
insert into tb values(1, '2007-5-6' , '2007-8-23' , 30000 )
insert into tb values(2, '2007-7-16', '2007-8-19' , 30000 )
insert into tb values(3, '2007-8-23', '2007-12-11', 30000 )
insert into tb values(4, '2007-5-9' , '2007-8-21' , 30000 )
insert into tb values(5, '2007-11-6', '2008-11-23', 30000 )
go
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2007-5-18'
set @dt2 = '2008-1-23'select *,计算值=
case when 开始日期 > @dt1 then 金额 / datediff(day , 开始日期 , 结束日期) * datediff(day , 开始日期 , @dt2) end
from tbdrop table tb/*
ID 开始日期 结束日期 金额 计算值
-- ----------------------- ----------------------- ----- -----
1 2007-05-06 00:00:00.000 2007-08-23 00:00:00.000 30000 NULL
2 2007-07-16 00:00:00.000 2007-08-19 00:00:00.000 30000 168462
3 2007-08-23 00:00:00.000 2007-12-11 00:00:00.000 30000 41616
4 2007-05-09 00:00:00.000 2007-08-21 00:00:00.000 30000 NULL
5 2007-11-06 00:00:00.000 2008-11-23 00:00:00.000 30000 6084
（所影响的行数为 5 行）
*/
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/-------------------------------------------------------------------
/*加个平均分，总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/drop table tb ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1
月，这是该月第几周，该周起始日期，该周结束日期。或者和邹老大的书里，查询指定日期段内过生日的人员的查询类似，改下那个查询也可以。如下：
--测试数据
DECLARE @t TABLE(ID int,Name varchar(10),Birthday datetime)
INSERT @t SELECT 1,'aa','1999-01-01'
UNION ALL SELECT 2,'bb','1996-02-29'
UNION ALL SELECT 3,'bb','1934-03-01'
UNION ALL SELECT 4,'bb','1966-04-01'
UNION ALL SELECT 5,'bb','1997-05-01'
UNION ALL SELECT 6,'bb','1922-11-21'
UNION ALL SELECT 7,'bb','1989-12-11'DECLARE @dt1 datetime,@dt2 datetime--查询 2003-12-05 至 2004-02-28 生日的记录
SELECT @dt1='2003-12-05',@dt2='2004-02-28'
SELECT * FROM @t
WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
BETWEEN @dt1 AND @dt2
OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
BETWEEN @dt1 AND @dt2
/*--结果
ID Name Birthday
---------------- ---------------- --------------------------
1 aa 1999-01-01 00:00:00.000
7 bb 1989-12-11 00:00:00.000
--*/--查询 2003-12-05 至 2006-02-28 生日的记录
SET @dt2='2006-02-28'
SELECT * FROM @t
WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
BETWEEN @dt1 AND @dt2
OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
BETWEEN @dt1 AND @dt2
/*--查询结果
ID Name Birthday
---------------- ----------------- --------------------------
1 aa 1999-01-01 00:00:00.000
2 bb 1996-02-29 00:00:00.000
7 bb 1989-12-11 00:00:00.000
--*/
declare @A table (ID int,Date1 datetime,Date2 datetime,Money money)
insert into @A
select 1,'2007-5-6','2007-8-23',30000 union all
select 2,'2007-7-16','2007-8-19',30000 union all
select 3,'2007-8-23','2007-12-11',30000 union all
select 4,'2007-5-9','2007-8-21',30000 union all
select 5,'2007-11-6','2008-11-23',30000declare @Date1 datetime,@Date2 datetime
select @Date1='2007-5-18',@Date2='2008-1-23'
declare @Top int,@Seed int
set @Top=datediff(day,@Date1,@Date2)+1
set @Seed=(@@datefirst-1+datepart(weekday,@Date1))%7
if @Seed = 0 set @Seed = 7if object_id('tempdb.dbo.#') is not null drop table #
select top (@Top) id=identity(int,0,1),WD=cast(null as int),Date=cast(null as datetime) into # from syscolumns,sysobjects
update # set WD=id+@Seed-1,Date=id+datediff(day,0,@Date1)select a.ID,Date1=min(b.Date),Date2=max(b.Date),Money=sum(a.Money/datediff(day,a.Date1,a.Date2)) from @A a,# b where a.Date1<=b.Date and a.Date2>=b.Date group by a.ID,b.WD/7 order by 1,2/*
ID Date1 Date2 Money
---- ---------- ---------- ---------------------
1 2007-05-18 2007-05-20 825.6879
1 2007-05-21 2007-05-27 1926.6051
1 2007-05-28 2007-06-03 1926.6051
1 2007-06-04 2007-06-10 1926.6051
1 2007-06-11 2007-06-17 1926.6051
1 2007-06-18 2007-06-24 1926.6051
1 2007-06-25 2007-07-01 1926.6051
1 2007-07-02 2007-07-08 1926.6051
1 2007-07-09 2007-07-15 1926.6051
1 2007-07-16 2007-07-22 1926.6051
1 2007-07-23 2007-07-29 1926.6051
1 2007-07-30 2007-08-05 1926.6051
1 2007-08-06 2007-08-12 1926.6051
1 2007-08-13 2007-08-19 1926.6051
1 2007-08-20 2007-08-23 1100.9172
2 2007-07-16 2007-07-22 6176.4703
2 2007-07-23 2007-07-29 6176.4703
2 2007-07-30 2007-08-05 6176.4703
2 2007-08-06 2007-08-12 6176.4703
2 2007-08-13 2007-08-19 6176.4703
3 2007-08-23 2007-08-26 1090.9088
3 2007-08-27 2007-09-02 1909.0904
3 2007-09-03 2007-09-09 1909.0904
3 2007-09-10 2007-09-16 1909.0904
3 2007-09-17 2007-09-23 1909.0904
3 2007-09-24 2007-09-30 1909.0904
3 2007-10-01 2007-10-07 1909.0904
3 2007-10-08 2007-10-14 1909.0904
3 2007-10-15 2007-10-21 1909.0904
3 2007-10-22 2007-10-28 1909.0904
3 2007-10-29 2007-11-04 1909.0904
3 2007-11-05 2007-11-11 1909.0904
3 2007-11-12 2007-11-18 1909.0904
3 2007-11-19 2007-11-25 1909.0904
3 2007-11-26 2007-12-02 1909.0904
3 2007-12-03 2007-12-09 1909.0904
3 2007-12-10 2007-12-11 545.4544
4 2007-05-18 2007-05-20 865.3845
4 2007-05-21 2007-05-27 2019.2305
4 2007-05-28 2007-06-03 2019.2305
4 2007-06-04 2007-06-10 2019.2305
4 2007-06-11 2007-06-17 2019.2305
4 2007-06-18 2007-06-24 2019.2305
4 2007-06-25 2007-07-01 2019.2305
4 2007-07-02 2007-07-08 2019.2305
4 2007-07-09 2007-07-15 2019.2305
4 2007-07-16 2007-07-22 2019.2305
4 2007-07-23 2007-07-29 2019.2305
4 2007-07-30 2007-08-05 2019.2305
4 2007-08-06 2007-08-12 2019.2305
4 2007-08-13 2007-08-19 2019.2305
4 2007-08-20 2007-08-21 576.923
5 2007-11-06 2007-11-11 469.9734
5 2007-11-12 2007-11-18 548.3023
5 2007-11-19 2007-11-25 548.3023
5 2007-11-26 2007-12-02 548.3023
5 2007-12-03 2007-12-09 548.3023
5 2007-12-10 2007-12-16 548.3023
5 2007-12-17 2007-12-23 548.3023
5 2007-12-24 2007-12-30 548.3023
5 2007-12-31 2008-01-06 548.3023
5 2008-01-07 2008-01-13 548.3023
5 2008-01-14 2008-01-20 548.3023
5 2008-01-21 2008-01-23 234.9867
*/
create table tb(ID int,开始日期 datetime , 结束日期 datetime , 金额 int)
insert into tb values(1, '2007-5-6' , '2007-8-23' , 30000 )
insert into tb values(2, '2007-7-16', '2007-8-19' , 30000 )
insert into tb values(3, '2007-8-23', '2007-12-11', 30000 )
insert into tb values(4, '2007-5-9' , '2007-8-21' , 30000 )
insert into tb values(5, '2007-11-6', '2008-11-23', 30000 )
go
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2007-5-18'
set @dt2 = '2008-1-23'select *,计算值=
case when 开始日期 > @dt1 then 金额 / datediff(day , 开始日期 , 结束日期) * datediff(day , 开始日期 , @dt2) end
from tbdrop table tb/*
ID 开始日期 结束日期 金额 计算值
-- ----------------------- ----------------------- ----- -----
1 2007-05-06 00:00:00.000 2007-08-23 00:00:00.000 30000 NULL
2 2007-07-16 00:00:00.000 2007-08-19 00:00:00.000 30000 168462
3 2007-08-23 00:00:00.000 2007-12-11 00:00:00.000 30000 41616
4 2007-05-09 00:00:00.000 2007-08-21 00:00:00.000 30000 NULL
5 2007-11-06 00:00:00.000 2008-11-23 00:00:00.000 30000 6084
（所影响的行数为 5 行）
*/