update的一道题.txt

SQL语言常用的一些命令各代码

tab1

-----------------------------------------------

c1	c2	c3	c4	c5	c100
 ------------------------------------------

1	2	3	1	100	
4
2	3	5	2	300
	9
3	6	5	2	350
	6
3	5	1	5	150	
5	
4	6	4	2	250	
7
------------------------------------------------

tab2
------------------
c1	
sAll
1	null
2	null	
3	
null
4	null

------------------- 


现在要做一统计，将tab1中的统计以后加到tab2中
统计方法如下：

首先判断c5，
如果c5>200,那么  sc1=sum(c2/c3)
		sc2=sum((case(when c100<5 then c4*2) else c4*3) end)
		
如果c5<=200,那么  sc1=sum(3*c2/c3+6)
		sc2=sum((case(when c100<5 then c4*20) else c4*30) end)

SALL=SC1+SC2


--解答：
create table tab1(c1 int,c2 int ,c3 int,c4 int,c5 int ,c100 int)
insert into tab1 select 1,	2	,3,	1	,100,	4
union all        select 2,	3,	5,	2,	300	,9
union all        select 3,	6,	5	,2,	350,	6
union all        select 3	,5,	1,	5,	150,	5
union all        select 4,	6,	4,	2,	250,	7
create table tab2(c1 int,sAll decimal(10,4))
insert into tab2 select 1,null
union  all       select 2,null
union all        select 3,null
union all        select 4,null


--更新TAB2的SALL
update tab2 set sall=sc1+sc2 from (select c1,c5,c100,c4, sc1=cast(sum(c2/c3)as decimal(10,4)),sc2=sum(c4*2)from (select distinct(c1),sum(c5) as c5,sum(c2)as c2 ,sum(c3)as c3,sum(c4)as c4,sum(c100)as c100 from tab1
group by c1) b
where c5>200 and c100<5
group by c1,c5,c100,c4
union 
select c1,c5,c100,c4,sc1=cast(sum(c2/c3)as decimal(10,4)),sc2=sum(c4*20) from (select distinct(c1),sum(c5) as c5,sum(c2)as c2 ,sum(c3)as c3,sum(c4)as c4,sum(c100)as c100 from tab1
group by c1) b
where c5>200 and c100>=5
group by c1,c5,c100,c4
union 
select c1,c5,c100,c4,sc1=cast(sum((3*c2)/(c3+6))as decimal(10,4)),sc2=c4*20 from (select distinct(c1),sum(c5) as c5,sum(c2)as c2 ,sum(c3)as c3,sum(c4)as c4,sum(c100)as c100 from tab1
group by c1) b
where c5<=200 and c100<5
group by c1,c5,c100,c4
union 
select c1,c5,c100,c4,sc1=cast(sum((3*c2)/(c3+6))as decimal(10,4)),sc2=c4%30 from (select distinct(c1),sum(c5) as c5,sum(c2)as c2 ,sum(c3)as c3,sum(c4)as c4,sum(c100)as c100 from tab1
group by c1) b
where c5<=200 and c100>=5
group by c1,c5,c100,c4
)a
where tab2.c1=a.c1
select * from tab2