情况是这样的:
num name qua price fee
1 a 1 1 1
1 b 2 1 2
1 c 0.1 null 0.3 -- (1+2)*0.1
2 d 2 1 2
2 e 2 1 2
2 f 1 2 2
2 c 0.2 null 1.2 -- (2+2+2)*0.2num编号 name名称 qua数量 price价格 fee费用运算规则是:
当name不是c时: fee = qua * price
当name是c: fee = qua * 相应num其他几行fee的总和
(红色的为注释)现在要一个查询求出c的各个num中c的fee,然后求出各个num的总fee,如1的总fee为1+2+0.3=3.3我同学拿着一个excel表格,要在数据库中实现excel中的这种公式关系
本来我想直接把c做成一个列加在price后面最后求总的,蛮方便的,但是表的结构就和excel中的不一样了,我同学不干...请问下高手能否写下查询代码
num name qua price fee
1 a 1 1 1
1 b 2 1 2
1 c 0.1 null 0.3 -- (1+2)*0.1
2 d 2 1 2
2 e 2 1 2
2 f 1 2 2
2 c 0.2 null 1.2 -- (2+2+2)*0.2num编号 name名称 qua数量 price价格 fee费用运算规则是:
当name不是c时: fee = qua * price
当name是c: fee = qua * 相应num其他几行fee的总和
(红色的为注释)现在要一个查询求出c的各个num中c的fee,然后求出各个num的总fee,如1的总fee为1+2+0.3=3.3我同学拿着一个excel表格,要在数据库中实现excel中的这种公式关系
本来我想直接把c做成一个列加在price后面最后求总的,蛮方便的,但是表的结构就和excel中的不一样了,我同学不干...请问下高手能否写下查询代码
select num,name,qua,price,
price*(case when name = 'c' then (select sum(isnull(qua,0)) from tb where num = t.num)
else qua end) as psnum
from tb t
declare @t table
(num int,name varchar(1),qua numeric(2,1),price int,fee numeric(4,2))
insert into @t
select 1,'a',1,1,1 union all
select 1,'b',2,1,2 union all
select 1,'c',0.1,null,null union all
select 2,'d',2,1,2 union all
select 2,'e',2,1,2 union all
select 2,'f',1,2,2 union all
select 2,'c',0.2,null,nullupdate @t set fee=b.col1*a.qua from @t a
left join (select num,sum(isnull(fee,0)) as col1 from @t group by num)
b on a.num=b.num where a.name='c'select * from @t
/*
num name qua price fee
----------- ---- --------------------------------------- ----------- ---------------------------------------
1 a 1.0 1 1.00
1 b 2.0 1 2.00
1 c 0.1 NULL 0.30
2 d 2.0 1 2.00
2 e 2.0 1 2.00
2 f 1.0 2 2.00
2 c 0.2 NULL 1.20
*/
+sum(qua * isnull(price,0))
from tb a group by num
select sum(qua * isnull(price,0))*(select qua from tb where num=a.num and name='c')
+sum(qua * isnull(price,0))
能解释下么...不是很懂