SQL统计页面
0
悬赏园豆：15
[已解决问题]
解决于 2016-03-15 13:04
表设计如下:
CREATE TABLE [dbo].[Table_pp填写](
[Id] [int] IDENTITY(1,1) NOT NULL,
[billNo] [nvarchar](50) NULL,
[applyDate] [datetime] NULL,
[inspectionTime] [datetime] NULL,
[sNo] [nvarchar](50) NULL,
[pNo] [nvarchar](50) NULL,
[cNo] [nvarchar](50) NULL,
[pName] [nvarchar](50) NULL,
[repeatOrder] [nvarchar](50) NULL,
[musicWithoutFunction] [nvarchar](50) NULL,
[result] [nvarchar](50) NULL,
[returnReason] [nvarchar](50) NULL,
[factory] [nvarchar](50) NULL,
[note] [nvarchar](50) NULL,
[biller] [nvarchar](10) NULL,
[billTime] [datetime] NULL,
[billNote] [nvarchar](50) NULL,
[checker] [nvarchar](50) NULL,
[checkTime] [datetime] NULL,
[checkNote] [nvarchar](50) NULL,
[flagColumn] [int] NULL CONSTRAINT [DF_Table_pp填写_flagColumn] DEFAULT ((0))
) ON [PRIMARY]
GO
现在想通过SQL做一个统计页面:格式如下
biller 数量(新) 金额(新) 数量(翻) 金额(翻) 数量(扣) 金额(扣) 合计
其中:数量(新)的条件是result='OK'且repeatOrder is null;
金额(新)=数量(新)*50;
数量(翻)的条件是result='OK'且repeatOrder='翻单';
金额(翻)=数量(翻)*25;
数量(扣)的统计条件是returnReason='2';
金额(扣)=数量(扣)*(-25);
合计=金额(新)+金额(翻)+金额(扣);
请问如何通过SQL语句将统计页面写出来??
最佳答案
0
select
数量(新),
数量(新)*50 as 金额(新) ,
数量(翻),
数量(翻)*25 as 金额(翻) ,
数量(扣),
数量(扣)*-25 as 金额(扣) ,
(数量(新)*50 + 数量(翻)*25 + 数量(扣)*-25) as 合计
from(
(select count(1) from 表 where result='OK' and repeatOrder is null) as 数量(新),
(select count(1) from 表 where result='OK' and repeatOrder='翻单') as 数量(翻),
(select count(1) from 表 where returnReason='2') as 数量(扣)
) a
收获园豆：15
t填写人不一样,第一列还要出现填写人列的
数量(新),和数量(翻),数量(扣) 无法关联
select
biller,
count_x,
(count_x * 50) as money_x,
count_f,
(count_f * 25) as money_f,
count_k,
(count_k * -25) as money_k,
(count_x * 50 + count_f * 25 + count_k * -25) as money_sum
from(
select
b.biller,
(select count(1) from Table_pp where biller=b.biller and result='OK' and repeatOrder is null) as count_x,
(select count(1) from Table_pp where biller=b.biller and result='OK' and repeatOrder='翻单') as count_f,
(select count(1) from Table_pp where biller=b.biller and returnReason='2') as count_k
from (select distinct biller from Table_pp) b
) a
