SQL 达人进。

Question

表:tb_Offc
Start_Dt End_Dt Pro_Qty Pro_Sales Name Address Tel Desc
2008-01-28 2008-02-28 300 50 Eva Xia China 021-54781478 
2008-11-01 2009-01-31 50000 500 Sabrina Gu USA 562-010-141-8796 
2009-03-01 2009-03-31 9000 350 Tank Zhou China 15978458871 表tb_temp
Start_Dt End_Dt Qty Sales Name Address Tel Desc Month Year
2008-01-28 2008-01-31 37.5 6.25 Eva Xia China 021-54781478  January 2008
2008-02-01 2008-02-28 262.5 43.75 Eva Xia China 021-54781478  Februcanry 2008
2008-11-01 2008-11-30 1630.43478 163.043478 Sabrina Gu USA 562-010-141-8796  November 2008
2008-12-01 2008-12-31 1684.7826 168.47826 Sabrina Gu USA 562-010-141-8796  December 2008
2009-01-01 2009-01-31 1684.7826 1684.7826 Sabrina Gu USA 562-010-141-8796  Februanry 2009
2008-02-01 2008-02-28 9000 350 Tank Zhou China 15978458871  Februanry 2008
把tb_Offc里面的数据根据月份拆分到tb_temp表里面，并计算每个月的Pro_Qty,Pro_Sales值

Answer 1

Start_Dt和End_Dt会不会有跨月、跨年的情况？

Answer 2

select 
  [Year],
  [Month],
  sum(Pro_Qty) as Pro_Qty,
  sum(Pro_Sales) as Pro_Sales
from
(
select
  Start_Dt,End_Dt,Pro_Qty,Pro_Sales,Name,Address,Tel,Desc,month(Start_Dt) as [Month],Year(Start_Dt) as [Year]
from tb_Offc 
union all
select
  Start_Dt,End_Dt,Pro_Qty,Pro_Sales,Name,Address,Tel,Desc,[Month],[Year]
from tb_Offc 
) t
group by [Year],[Month]
order by [Year],[Month]

Answer 3

Pro_Qty,Pro_Sales值按天平均值拆分，比如tb_Offc 第一行Pro_Qty值300按天拆分就变成tb_temp里37.5和262.5

Answer 4

这里只是实现了插入功能，并没有实现拆分的功能，并且5楼做错了一点，不是计算Qty和Sales的和，而是根据Start_Dt和End_Dt中间的月份，分别计算每个月的Qty和Sales。

Answer 5

就是group by 后按照你的计算倒到另外一张表就可以

Answer 6

把LZ的数据整理了一下，便于查看，望高手指点
表:tb_Offc 
 Start_Dt    End_Dt   Pro_Qty  Pro_Sales    Name    Address    Tel Desc 
2008-01-28 2008-02-28   300       50      Eva Xia    China   021-54781478 
2008-11-01 2009-01-31  50000     500     Sabrina Gu   USA   562-010-141-8796 
2009-03-01 2009-03-31  9000      350     Tank Zhou   China   15978458871 表tb_temp 
 Start_Dt    End_Dt        Qty      Sales          Name       Address       Tel Desc        Month      Year 
2008-01-28 2008-01-31     37.5      6.25          Eva Xia      China      021-54781478     January     2008 
2008-02-01 2008-02-28     262.5     43.75         Eva Xia      China      021-54781478    Februcanry   2008 
2008-11-01 2008-11-30   1630.43478 163.043478   Sabrina Gu      USA     562-010-141-8796   November    2008 
2008-12-01 2008-12-31   1684.7826  168.47826    Sabrina Gu      USA     562-010-141-8796   December    2008 
2009-01-01 2009-01-31   1684.7826  1684.7826    Sabrina Gu      USA     562-010-141-8796  Februanry    2009 
2008-02-01 2008-02-28     9000       350        Tank Zhou      China     15978458871      Februanry    2008 

Answer 7

从2008-01-28 到 2008-02-28 一共31天,平均值:select 300.00/31 = 9.677419
从2008-1-28 到2008-1-31 计3开,按说他的值应该是:  9.677419*3 = 29.032257
不知道37.5是怎么来的.

Answer 8

--select * from tb_Offc
--truncate table tb_tempdeclare @sales decimal(18,5)
declare @sdate datetime
declare @edate datetime
declare @id int
declare @name nvarchar(100)
declare @address nvarchar(100)
declare @tel nvarchar(100)declare cur cursor for select id,start_dt,end_dt,sales,name,address,tel from tb_Offc
open cur
fetch next from cur into @id,@sdate,@edate,@sales,@name,@address,@tel
while @@fetch_status =0
begin
declare @months int --相隔月份数select @months = datediff(month,@sdate,@edate) +1 
declare @i int 
set @i = 1
declare @lastDayForThisMonth datetime --此月最后一天declare @sdate_2 datetime --实际的每月第一天while @i<=@months
begin 
    if @i = 1
    begin
set  @sdate_2 = @sdate
    end
    else
    begin
set @sdate_2 = dateadd(month,@i-1,@sdate-day(@sdate)+1)  --下一月第一天
    end set @lastDayForThisMonth =   dateadd(month,@i,@sdate-day(@sdate)+1)-1 --当月最后一天

    if @lastDayForThisMonth > @edate
       set @lastDayForThisMonth = @edate declare @dayCount int
select @dayCount = datediff(day,@sdate,@edate) +1
    
    insert  into tb_temp(start_dt,end_dt,sales,[name],address,tel,[desc],create_month, Create_Year)
    select @sdate_2,@lastDayForThisMonth,@sales/@dayCount*(datediff(day,@sdate_2,@lastDayForThisMonth) +1),@name,@address,@tel,'',month(getdate()),year(getdate())
set @i = @i +1
endfetch next from cur into @id,@sdate,@edate,@sales,@name,@address,@tel
endclose cur 
deallocate curselect * from tb_tempId          Start_Dt                End_Dt                  Sales                                   Name                     Address                                            Tel                      Desc                                                                                                                                                                                                                                                             Create_Month                                       Create_Year
----------- ----------------------- ----------------------- --------------------------------------- ------------------------ -------------------------------------------------- ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1           2008-01-28 00:00:00.000 2008-01-31 00:00:00.000 2500.0000000000                         Eva Xia                  China.shanghai                                     021-54781478                                                                                                                                                                                                                                                                              4                                                  2009
2           2008-02-01 00:00:00.000 2008-02-28 00:00:00.000 17500.0000000000                        Eva Xia                  China.shanghai                                     021-54781478                                                                                                                                                                                                                                                                              4                                                  2009
3           2008-01-31 00:00:00.000 2008-01-31 00:00:00.000 14590.1639344262                        Sabrina                  Gu USA                                             562-010-141-8796                                                                                                                                                                                                                                                                          4                                                  2009
4           2008-02-01 00:00:00.000 2008-02-29 00:00:00.000 423114.7540983607                       Sabrina                  Gu USA                                             562-010-141-8796                                                                                                                                                                                                                                                                          4                                                  2009
5           2008-03-01 00:00:00.000 2008-03-31 00:00:00.000 452295.0819672131                       Sabrina                  Gu USA                                             562-010-141-8796                                                                                                                                                                                                                                                                          4                                                  2009
6           2009-01-01 00:00:00.000 2009-01-31 00:00:00.000 339092.3076923077                       Tank Zhou                China.guangzhou                                    15978458871                                                                                                                                                                                                                                                                               4                                                  2009
7           2009-02-01 00:00:00.000 2009-02-28 00:00:00.000 306276.9230769231                       Tank Zhou                China.guangzhou                                    15978458871                                                                                                                                                                                                                                                                               4                                                  2009
8           2009-03-01 00:00:00.000 2009-03-31 00:00:00.000 339092.3076923077                       Tank Zhou                China.guangzhou                                    15978458871                                                                                                                                                                                                                                                                               4                                                  2009
9           2009-04-01 00:00:00.000 2009-04-01 00:00:00.000 10938.4615384615                        Tank Zhou                China.guangzhou                                    15978458871                                                                                                                                                                                                                                                                               4                                                  2009(9 row(s) affected)

Answer 9

表结构还是你昨天给我的表结构,只有一个sales字段.另外我现在做的是你的字段是固定的形式.

Answer 10

id          start_dt   end_dt     name                     address                                            tel                      desc                                                                                                                                                                                                                                                             create_month                                       Create_Year
----------- ---------- ---------- ------------------------ -------------------------------------------------- ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1           2008-01-28 2008-01-31 Eva Xia                  China.shanghai                                     021-54781478                                                                                                                                                                                                                                                                              4                                                  2009
2           2008-02-01 2008-02-28 Eva Xia                  China.shanghai                                     021-54781478                                                                                                                                                                                                                                                                              4                                                  2009
3           2008-01-31 2008-01-31 Sabrina                  Gu USA                                             562-010-141-8796                                                                                                                                                                                                                                                                          4                                                  2009
4           2008-02-01 2008-02-29 Sabrina                  Gu USA                                             562-010-141-8796                                                                                                                                                                                                                                                                          4                                                  2009
5           2008-03-01 2008-03-31 Sabrina                  Gu USA                                             562-010-141-8796                                                                                                                                                                                                                                                                          4                                                  2009
6           2009-01-01 2009-01-31 Tank Zhou                China.guangzhou                                    15978458871                                                                                                                                                                                                                                                                               4                                                  2009
7           2009-02-01 2009-02-28 Tank Zhou                China.guangzhou                                    15978458871                                                                                                                                                                                                                                                                               4                                                  2009
8           2009-03-01 2009-03-31 Tank Zhou                China.guangzhou                                    15978458871                                                                                                                                                                                                                                                                               4                                                  2009
9           2009-04-01 2009-04-01 Tank Zhou                China.guangzhou                                    15978458871                                                                                                                                                                                                                                                                               4                                                  2009(9 row(s) affected)

Answer 11

但针对你的字段个数不能确定的情况,这个过程还需要做一些改动,游标只取ID值,然后具体的其它的值去表里取,加where条件id= @id