求一个sql,等大神

Persistence_x 2013-10-15 11:19:07 
select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,

Consumer1,Consumer2,Consumer3,Consumer4,

case when rec>a_FixedFee then rec else a_FixedFee end as rec,

case when pay>c_FixedFee then pay else c_FixedFee end as pay,

case when rec>a_FixedFee then rec else a_FixedFee end-case when 

pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段

from 

(

select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,

b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,

case a.FeeType

when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2

when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4

end as rec,

case c.FeeType

when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2

when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4

end as pay

from S_SaleManager a

inner join S_MonthlyData b on a.Number400=b.Number400

inner join S_Manager400 c on b.Number400=c.Number400

)t


我执行这个sql显示出如下图,请问有没有方法可以解决 在下面加一行 算出 rec的总和,pay的总和,profit的总和
不知道sql能否实现 第4行前面的值都不要,只要一个最后的统计算出 rec的总和,pay的总和,profit的总和
...全文
96 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
Landa_Jimmy 2013-10-16
  • 打赏
  • 举报
 回复 

;with cte as
(
select convert(varchar(100),Number400)Number400,convert(varchar(100),_year)_year ,convert(varchar(100),_Month)_Month ,
convert(varchar(100),FeeType)FeeType, convert(varchar(100),a_FixedFee) a_FixedFee,convert(varchar(100),FeeTypeName)FeeTypeName,
convert(varchar(100),Fee1)Fee1,convert(varchar(100),Fee2)Fee2,convert(varchar(100),Fee3)Fee3,convert(varchar(100),Fee4)Fee4,
convert(varchar(100),Consumer1)Consumer1,convert(varchar(100),Consumer2)Consumer2,convert(varchar(100),Consumer3)Consumer3,
convert(varchar(100),Consumer4)Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t
)
select * from cte
union all
select '','','','','','','','','','','','','',sum( rec),sum(pay),sum(profit) from cte
Landa_Jimmy 2013-10-16
  • 打赏
  • 举报
 回复
引用 7 楼 xiaohuaidan1988 的回复:
经过2位的建议 我进行了测试因为有的值是有默认值的,好像用sql不太好实现总和 谢谢2位 其实我要的结果是空值的 就像400号码这列似的 但是我的有些字段是有默认值的 不行的话还是用程序来实现吧！
;with cte as ( select convert(varchar(100),Number400)Number400,convert(varchar(100),_year)_year ,convert(varchar(100),_Month)_Month , convert(varchar(100),FeeType)FeeType, convert(varchar(100),a_FixedFee) a_FixedFee,convert(varchar(100),FeeTypeName)FeeTypeName, convert(varchar(100),Fee1)Fee1,convert(varchar(100),Fee2)Fee2,convert(varchar(100),Fee3)Fee3,convert(varchar(100),Fee4)Fee4, convert(varchar(100),Consumer1)Consumer1,convert(varchar(100),Consumer2)Consumer2,convert(varchar(100),Consumer3)Consumer3, convert(varchar(100),Consumer4)Consumer4, case when rec>a_FixedFee then rec else a_FixedFee end as rec, case when pay>c_FixedFee then pay else c_FixedFee end as pay, case when rec>a_FixedFee then rec else a_FixedFee end-case when pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段 from ( select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4, b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee, case a.FeeType when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2 when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4 end as rec, case c.FeeType when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2 when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4 end as pay from S_SaleManager a inner join S_MonthlyData b on a.Number400=b.Number400 inner join S_Manager400 c on b.Number400=c.Number400 )t ) select * from cte union all select '','','','','','','','','','','','','',sum( rec),sum(pay),sum(profit) from cte 试试这个方法。
Persistence_x 2013-10-15
  • 打赏
  • 举报
 回复
引用 5 楼 u012173239 的回复:
[quote=引用 3 楼 xiaohuaidan1988 的回复:] [quote=引用 1 楼 u012173239 的回复:] 

;with cte as
(
select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t
)
select * from cte
union all
select '','','','','','','','','','','','','',sum( rec),sum(pay),sum(profit) from cte

select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t
union  all 
select '','','','','','','','','','','','','','',sum(rec),sum(pay),sum(profit) from t
执行之后说t无效 这个是sqlserver 数据库[/quote] 你用个临时表，不然按你的那个写法是有问题的。[/quote] 看下7楼的这个问题能用sql解决吗
Persistence_x 2013-10-15
  • 打赏
  • 举报
 回复
引用 6 楼 hdhai9451 的回复:
[quote=引用 4 楼 xiaohuaidan1988 的回复:] [quote=引用 2 楼 hdhai9451 的回复:] 

--因为sql语句比较复杂,新建视图代替
create view vie_test
as
select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t

--查询
select * from vie_test
union all
select null,null ,null ,null, null ,null,null,null,null,null,
null,null,null,null,sum(rec) as rec,sum(pay) as pay,sum(profit) as profit
from vie_test
试图不可以传入参数吧！ 最终我这个语句还要根据年和月份来查询相应的数据的！ 最终我这个要提供给一个报表[/quote] 改用with可以加入参数条件,结果是一样的[/quote] 看下7楼的这个方式能用sql解决吗
Persistence_x 2013-10-15
  • 打赏
  • 举报
 回复
经过2位的建议 我进行了测试因为有的值是有默认值的,好像用sql不太好实现总和 谢谢2位 其实我要的结果是空值的 就像400号码这列似的 但是我的有些字段是有默认值的 不行的话还是用程序来实现吧！
Andy__Huang 2013-10-15
  • 打赏
  • 举报
 回复
引用 4 楼 xiaohuaidan1988 的回复:
[quote=引用 2 楼 hdhai9451 的回复:] 

--因为sql语句比较复杂,新建视图代替
create view vie_test
as
select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t

--查询
select * from vie_test
union all
select null,null ,null ,null, null ,null,null,null,null,null,
null,null,null,null,sum(rec) as rec,sum(pay) as pay,sum(profit) as profit
from vie_test
试图不可以传入参数吧！ 最终我这个语句还要根据年和月份来查询相应的数据的！ 最终我这个要提供给一个报表[/quote] 改用with可以加入参数条件,结果是一样的
Landa_Jimmy 2013-10-15
  • 打赏
  • 举报
 回复
引用 3 楼 xiaohuaidan1988 的回复:
[quote=引用 1 楼 u012173239 的回复:] 

;with cte as
(
select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t
)
select * from cte
union all
select '','','','','','','','','','','','','',sum( rec),sum(pay),sum(profit) from cte

select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t
union  all 
select '','','','','','','','','','','','','','',sum(rec),sum(pay),sum(profit) from t
执行之后说t无效 这个是sqlserver 数据库[/quote] 你用个临时表，不然按你的那个写法是有问题的。
Persistence_x 2013-10-15
  • 打赏
  • 举报
 回复
引用 2 楼 hdhai9451 的回复:

--因为sql语句比较复杂,新建视图代替
create view vie_test
as
select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t

--查询
select * from vie_test
union all
select null,null ,null ,null, null ,null,null,null,null,null,
null,null,null,null,sum(rec) as rec,sum(pay) as pay,sum(profit) as profit
from vie_test
试图不可以传入参数吧！ 最终我这个语句还要根据年和月份来查询相应的数据的！ 最终我这个要提供给一个报表
Persistence_x 2013-10-15
  • 打赏
  • 举报
 回复
引用 1 楼 u012173239 的回复:

;with cte as
(
select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t
)
select * from cte
union all
select '','','','','','','','','','','','','',sum( rec),sum(pay),sum(profit) from cte

select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t
union  all 
select '','','','','','','','','','','','','','',sum(rec),sum(pay),sum(profit) from t
执行之后说t无效 这个是sqlserver 数据库
Andy__Huang 2013-10-15
  • 打赏
  • 举报
 回复 

--因为sql语句比较复杂,新建视图代替
create view vie_test
as
select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t

--查询
select * from vie_test
union all
select null,null ,null ,null, null ,null,null,null,null,null,
null,null,null,null,sum(rec) as rec,sum(pay) as pay,sum(profit) as profit
from vie_test
Landa_Jimmy 2013-10-15
  • 打赏
  • 举报
 回复 

;with cte as
(
select Number400,_year ,_Month ,FeeType, a_FixedFee ,FeeTypeName,Fee1,Fee2,Fee3,Fee4,
Consumer1,Consumer2,Consumer3,Consumer4,
case when rec>a_FixedFee then rec else a_FixedFee end as rec,
case when pay>c_FixedFee then pay else c_FixedFee end as pay,
case when rec>a_FixedFee then rec else a_FixedFee end-case when 
pay>c_FixedFee then pay else c_FixedFee end as profit -->加利润字段
from 
(
select a.Number400,b._year ,b._Month ,a.FeeType, a.FixedFee as a_FixedFee,a.FeeTypeName,a.Fee1,a.Fee2,a.Fee3,a.Fee4,
b.Consumer1,b.Consumer2,b.Consumer3,b.Consumer4,c.FixedFee as c_FixedFee,
case a.FeeType
when 1 then a.Fee1*b.Consumer1+a.Fee2*b.Consumer2
when 2 then a.Fee3*b.Consumer3+a.Fee4*b.Consumer4
end as rec,
case c.FeeType
when 1 then c.Fee1*b.Consumer1+c.Fee2*b.Consumer2
when 2 then c.Fee3*b.Consumer3+c.Fee4*b.Consumer4
end as pay
from S_SaleManager a
inner join S_MonthlyData b on a.Number400=b.Number400
inner join S_Manager400 c on b.Number400=c.Number400
)t
)
select * from cte
union all
select '','','','','','','','','','','','','',sum( rec),sum(pay),sum(profit) from cte
SQL注入2023最新版(系统学习0基础到大神)
SQL注入漏洞解析 等多方面 , 深入探讨SQL注入漏洞技术，并提供有效的防御策略。通过理论与实践相结合的方式，学员将全面了解SQL注入漏洞的原理、利用方法以及对应的安全防护措施。 课程特色：1 . 深度探索：该课程...
sql进行两个关联表,根据其中一个表的一个属性进行条件查询查询
我打算自己写一个sql专栏，特意讲解sql的使用，来帮助大家 这篇文章技术指导为sql进行两个关联表,根据其中一个表的一个属性进行条件查询查询 假设只有两张表，其中一张表最后一个外键连接到另一张表的主键，那我想...
求大神帮忙 Sql server 2008 服务启动10048异常
求大神帮忙 Sql server 2008 服务启动10048错误 Sql server 2008 服务启动时 报 10048错误怎么办？？ ------解决方案-------------------------------------------------------- 转到sqls
Mosh大神的SQL课-一些要点总结（适合有SQL基础者）
编写SQL语句之前，选中对应的数据库 选中方式：①双击；②USE Database_name; 在SQL语句中，FROM子句也是可选的（即可写也可不写），SELECT的内容是什么，结果就是什么。 SELECT 'first_name' -- 结果显示first_...
数据库SQL超实用10篇博客大神之路
一、基础 数据库基本操作：https://blog.csdn.net/libusi001/article/details/100090972 SQL基本命令：https://blog.csdn.net/libusi001/article/details/100092126 SQL超实用查询汇总：...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章