【求助】一个貌似简单的SQL语句，折磨了我一下午，请高手指点，谢谢！(50分尽快结贴)

[renchao]

假设有数据表T1，包含3个字段TYPE（类型）、DATE（日期）和JE（金额），如下所示（T1表）：
TYPE DATE JE
----------------------
A 200202 10
B 200203 20
B 200203 30
A 200204 40
B 200205 50
A 200206 60

想要通过GROUP BY TYPE, DATE分组后，产生3个字段SUM_JE、LJ_JE和BFB_LJ_JE，如下所示（T2表）：
TYPE DATE SUM_JE LJ_JE BFB_LJ_JE
-----------------------------------------------
A 200202 10 10 10/170
A 200204 20 50 50/170
A 200206 60 110 110/170
B 200203 50 50 50/160
B 200206 60 110 110/160

其中，
SUM_JE是金额和，表示：当前TYPE的当前DATE组中，T1表中的数据和
如：T2的第4行(50)=T1的第2行(20)+T1的第3行(30)
LJ_JE是当年累计金额，表示：当前TYPE组中，T1.DATE小于等于当前DATE的数据和
如：T2的第2行(50)=T1的第1行(10)+T1的第4行(40)
BFB_LJ_JE表累计金额百分比，表示：当年累计金额(LJ_JE) / 当前TYPE组中LJ_JE之和
如：T2的第1行(10/170)= T2的LJ_JE字段(10) / T2的1、2、3行之和(10+50+110=170)

请问：如何用一个SELECT语句从T1生成T2？
想了好多办法都没成功，请高手帮个忙，先谢谢了！

[renchao]

曾尝试用子查询输出LJ_JE：
SELECT
T.*,
(SELECT SUM(T1.JE) FROM T AS T1 WHERE T1.DATE <= T.DATE) AS LJ_JE
FROM T
可以成功，但一旦加上GROUP BY DATE就不能只在当前DATE组里面正确输出了...

请指教：）

[bugchen888]

SELECT TYPE,DATE,SUM(je) AS sum_je,
(SELECT SUM(je) FROM t1 x WHERE x.type=y.TYPE AND x.date<y.DATE) AS lj_je,
(SELECT SUM(je) FROM t1 x WHERE x.type=y.TYPE AND x.date<y.DATE)/(SELECT SUM(je) FROM t1 z WHERE z.type=y.TYPE) AS bfb_lj_je
FROM t1 y
GROUP BY TYPE,DATE

[ReViSion]

--一条语句可能很难实现,能实现效率也不高

[ReViSion]

Create table T(type varchar(10),date char(6),JE money)

insert into T
select 'A' , '200202' , 10
union all select
'B' , '200203' , 20
union all select
'B' , '200203' , 30
union all select
'A' , '200204' , 40
union all select
'B' , '200205' , 50
union all select
'A' , '200206' , 60


Select T.TYPE,T.DATE,sum_je=SUM(je),LJ_JE=SUM(je),b.BFB_LJ_JE
into #1 from T
left join (Select type,BFB_LJ_JE=sum(je) from T
--where date 在这里加入年条件
group by type)b
on T.type=b.type
--where date --在这里加上年的条件
group by t.type,date,b.BFB_LJ_JE

--select * from #1
Declare @sum_je money
Declare @type varchar(10)

--更新字段LJ_JE
update #1 set @sum_je=LJ_JE=case type when @type then LJ_JE+@sum_je else LJ_JE end,@type=type

--更新字段BFB_LJ_JE
update #1 set BFB_LJ_JE=LJ_JE/BFB_LJ_JE

select * from #1

--删除临时表
drop table #1

[liujx_1999]

用一條SQL的話﹐BFB_LJ_JE 很難實現

[renchao]

如果不考虑BFB_LJ_JE，有好的办法吗？

[liujx_1999]

SELECT TYPE,DATE,Sum_je=SUM(je),
Lj_je=(SELECT SUM(je) FROM T1 A WHERE A.type=B.TYPE AND convert(varchar(10),A.date,120)<=convert(varchar(10),B.DATE,120) FROM T1 B GROUP BY TYPE,DATE

[ReViSion]

也不大好实现，能实现也会很慢

[renchao]

在bugchen888(臭虫)的指点下，找到一个简单的方法：

SELECT
type, dt, SUM(je) AS sum_je,
cast(SUM(je) as nvarchar(9,2) )*100 / cast((SELECT SUM(z.je) FROM t1 z
WHERE z.type=y.type) as nvarchar(9,2) ) as bfb_je,
(SELECT SUM(x.je) FROM t1 x WHERE x.type=y.type AND x.dt<=y.dt) AS lj_je
FROM t1 y
GROUP BY type,dt

但没实现bfb_lj_je，感觉在一个SELECT中实现bfb_lj_je确实很困难:(

[mislrb]

楼主的T2表是不是有问题？？若是用多语句解决会易读许多。

Create table #t(type varchar(1),date varchar(6),je money)
insert into #t
select 'A','200202',10 union all
select 'B','200203',20 union all
select 'B','200203',30 union all
select 'A','200204',40 union all
select 'B','200205',50 union all
select 'A','200206',60

select aa.type,
[date]=aa.[date],
sumje=aa.sumje,
lj_je=aa.lj_je,
bfb_lj_je=convert(varchar(10),aa.lj_je)+'/'+convert(varchar(10),aa.total)
from (select tt.type,
[date]=tt.[date],
sumje=max(tt.sumje),
lj_je=(select sum(a.sum_je) from (select type,date,sum_je=sum(je) from #t group by type,date
) a where a.type=tt.type and a.date<=tt.date),
total=(select sum(a.lj_je) from (select type,date,
lj_je=(select sum(je) from #t where type=t.type and date<=t.date)
from #t t
group by type,date
) a where a.type=tt.type)
from (select type,date,
sumje=sum(je)
from #t t
group by type,date
) tt
group by tt.type,tt.date
) aa
order by aa.type,aa.date

drop table #t

/*
type date sumje lj_je bfb_lj_je
---------- ------ --------------------- --------------------- ---------------------
A 200202 10.0000 10.0000 10.00/170.00
A 200204 40.0000 50.0000 50.00/170.00
A 200206 60.0000 110.0000 110.00/170.00
B 200203 50.0000 50.0000 50.00/150.00
B 200205 50.0000 100.0000 100.00/150.00
*/

[renchao]

用表變量可以做嗎？
好像不支持SELECT INTO啊？