首页 新闻 会员 周边 捐助

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语句将统计页面写出来??

wq_vincent的主页 wq_vincent | 初学一级 | 园豆:5
提问于:2016-03-07 22:52
< >
分享
最佳答案
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
Rich.T | 老鸟四级 |园豆:3440 | 2016-03-08 11:35

t填写人不一样,第一列还要出现填写人列的

wq_vincent | 园豆:5 (初学一级) | 2016-03-08 12:03

 数量(新),和数量(翻),数量(扣) 无法关联

wq_vincent | 园豆:5 (初学一级) | 2016-03-08 12:04

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

Rich.T | 园豆:3440 (老鸟四级) | 2016-03-08 13:40
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册