这样的存储过程应该如何写

这样的存储过程应该怎么写？
有两张表
销售表
id 车架号 销售金额 销售时间 销售员 
1  LVGB123  1980 2012-09-02 17:42:57.000  张三
2  LVGB545  2980 2012-09-12 11:42:57.000  李四
4  LVGB623  1980 2012-09-23 13:42:57.000  张三
5  LVGB532  2980 2012-09-25 14:42:57.000  王五
8  LVGB234  2980 2012-09-26 15:42:57.000  王五
10  LVGB623  1980 2012-09-27 13:42:57.000  李四
充值表
id 车架号 充值金额 充值时间 销售员 次数
1  LVGB123  1980 2012-09-06 17:42:57.000  张三
2  LVGB545  2980 2012-09-15 11:42:57.000  李四
4  LVGB623  1980 2012-09-26 13:42:57.000  张三
5  LVGB532  -2980 2012-09-27 14:42:57.000  王五
8  LVGB234  2980 2012-09-28 15:42:57.000  王五
10  LVGB623  1980 2012-09-28 13:42:57.000  王五
我想统计出一个销售员在2012-09-01 到2012-09-30 期间所销售出去或者充值的次数：
得出下表：
id  1980 2980  销售员 次数
1   4    0    张三    4
2   1    2    李四    3
3   1    2    王五    3


注意：1、销售表中的 5  LVGB532  2980 2012-09-25 14:42:57.000  王五  充值表中的5  LVGB532  -2980 2012-09-27 14:42:57.000  王五 记录意思是车架号为 LVGB532  在时间 2012-09-25 14:42:57.000购买  2980元的充值卡，又在时间2012-09-27 14:42:57.000退掉了这2980元的充值卡，相当于没有这次次数为零。也就是说这次王五没有业绩。

[解决办法]

if object_id('[销售表]') is not null drop table [销售表]
go
create table [销售表] (id int,车架号 nvarchar(14),销售金额 int,销售时间 datetime,销售员 nvarchar(4))
insert into [销售表]
select 1,'LVGB123',1980,'2012-09-02 17:42:57.000','张三' union all
select 2,'LVGB545',2980,'2012-09-12 11:42:57.000','李四' union all
select 4,'LVGB623',1980,'2012-09-23 13:42:57.000','张三' union all
select 5,'LVGB532',2980,'2012-09-25 14:42:57.000','王五' union all
select 8,'LVGB234',2980,'2012-09-26 15:42:57.000','王五' union all
select 10,'LVGB623',1980,'2012-09-27 13:42:57.000','李四'

if object_id('[充值表]') is not null drop table [充值表]
go
create table [充值表] (id int,车架号 nvarchar(14),充值金额 int,充值时间 datetime,销售员 nvarchar(4),次数 sql_variant)
insert into [充值表]
select 1,'LVGB123',1980,'2012-09-06 17:42:57.000','张三',null union all
select 2,'LVGB545',2980,'2012-09-15 11:42:57.000','李四',null union all
select 4,'LVGB623',1980,'2012-09-26 13:42:57.000','张三',null union all
select 5,'LVGB532',-2980,'2012-09-27 14:42:57.000','王五',null union all
select 8,'LVGB234',2980,'2012-09-28 15:42:57.000','王五',null union all
select 10,'LVGB623',1980,'2012-09-28 13:42:57.000','王五',null

select * from [销售表]
select * from [充值表]


WITH TT
AS(
SELECT * FROM [销售表]
UNION ALL  
 
SELECT id,车架号,充值金额,充值时间,销售员 from [充值表]),

T1 AS(
SELECT 
SUM(CASE WHEN 销售金额 =1980 THEN 销售金额 ELSE 0 END)/1980 AS [1980],
SUM(CASE WHEN 销售金额 =2980 THEN 销售金额 ELSE 0 END)/2980 AS [2980],
销售员
FROM TT
WHERE 销售时间 BETWEEN '2012-09-01' AND '2012-09-30'
GROUP BY 销售员)

SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) AS id ,*,次数=[1980]+[2980] FROM T1 

/*
id19802980销售员次数
112李四3
213王五4
340张三4*/

[解决办法]
with
tba(id,车架号,销售金额,销售时间,销售员)
as(
select 1,'LVGB123',1980,'2012-09-02,17:42:57.000','张三'  union all
select 2,'LVGB545',2980,'2012-09-12,11:42:57.000','李四'  union all
select 4,'LVGB623',1980,'2012-09-23,13:42:57.000','张三'  union all
select 5,'LVGB532',2980,'2012-09-25,14:42:57.000','王五'  union all
select 8,'LVGB234',2980,'2012-09-26,15:42:57.000','王五'  union all
select 10,'LVGB623',1980,'2012-09-27,13:42:57.000','李四'),
tbb(id,车架号,充值金额,充值时间,销售员)
as(
select 1,'LVGB123',1980,'2012-09-06,17:42:57.000','张三'  union all
select 2,'LVGB545',2980,'2012-09-15,11:42:57.000','李四'  union all
select 4,'LVGB623',1980,'2012-09-26,13:42:57.000','张三'  union all
select 5,'LVGB532',-2980,'2012-09-27,14:42:57.000','王五'  union all
select 8,'LVGB234',2980,'2012-09-28,15:42:57.000','王五'  union all
select 10,'LVGB623',1980,'2012-09-28,13:42:57.000','王五'
)select id=row_number()over(order by sum(次数) desc),sum([1980])[1980],sum([2980])[1980],销售员,sum(次数)次数
from
(
select sum(case when 销售金额=1980 then 1 else 0 end) [1980],sum(case when 销售金额=2980 then 1 else 0 end) [2980],
销售员,count(1)次数 from tba group by 销售员
union all
select sum(case when 充值金额=-1980 then -1 when 充值金额=1980 then 1 else 0 end) [1980],sum(case when 充值金额=-2980 then -1 when 充值金额=2980 then 1  else 0 end) [2980],
销售员,sum(case when 充值金额<0 then -1 when 充值金额>0 then 1 else 0 end)次数 from tbb group by 销售员
)t group by 销售员
[解决办法]

select * into #a from(
select 1 id,'LVGB123' 车架号,1980 销售金额,'2012-09-02,17:42:57.000' 销售时间,'张三' 销售员
union all select 2,'LVGB545',2980,'2012-09-12,11:42:57.000','李四'
union all select 4,'LVGB623',1980,'2012-09-23,13:42:57.000','张三'
union all select 5,'LVGB532',2980,'2012-09-25,14:42:57.000','王五'
union all select 8,'LVGB234',2980,'2012-09-26,15:42:57.000','王五'
union all select 10,'LVGB623',1980,'2012-09-27,13:42:57.000','李四'
)a
select * into #b from (
select 1 id,'LVGB123' 车架号,1980 充值金额,'2012-09-06,17:42:57.000' 充值时间,'张三'销售员
union all select 2,'LVGB545',2980,'2012-09-15,11:42:57.000','李四'
union all select 4,'LVGB623',1980,'2012-09-26,13:42:57.000','张三'
union all select 5,'LVGB532',-2980,'2012-09-27,14:42:57.000','王五'
union all select 8,'LVGB234',2980,'2012-09-28,15:42:57.000','王五' 
 
union all select 10,'LVGB623',1980,'2012-09-28,13:42:57.000','王五'
union all select 11,'LVGB623',2980,'2012-09-28,13:43:57.000','张三'
)b

select 销售员,[1980],[2980],([1980]+[2980])次数 from (
select 销售员,sum(case when abs(销售金额)=1980 then 销售金额 else 0 end)/1980 as [1980]
,sum(case when abs(销售金额)=2980 then 销售金额 else 0 end)/2980 as [2980] from (
select * from #a a
where id in(select min(id) from #a group by 车架号,convert(varchar(10),销售时间,23))
union all
select * from #b b 
where id in(select min(id) from #b group by 车架号,convert(varchar(10),充值时间,23))
)a 
group by 销售员
)b

/*
销售员         1980        2980          次数
---- ----------- ----------- -----------
李四             1           2           3
王五             1           2           3
张三             4           0           4

(3 行受影响)

*/

[解决办法]
我也贴个

create table #tb1 (id int,车架号 nvarchar(14),销售金额 int,销售时间 datetime,销售员 nvarchar(4))
insert into #tb1
select 1,'LVGB123',1980,'2012-09-02 17:42:57.000','张三' union all
select 2,'LVGB545',2980,'2012-09-12 11:42:57.000','李四' union all
select 4,'LVGB623',1980,'2012-09-23 13:42:57.000','张三' union all
select 5,'LVGB532',2980,'2012-09-25 14:42:57.000','王五' union all
select 8,'LVGB234',2980,'2012-09-26 15:42:57.000','王五' union all
select 10,'LVGB623',1980,'2012-09-27 13:42:57.000','李四'
 

create table #tb2 (id int,车架号 nvarchar(14),充值金额 int,充值时间 datetime,销售员 nvarchar(4),次数 sql_variant)
insert into #tb2
select 1,'LVGB123',1980,'2012-09-06 17:42:57.000','张三',null union all
select 2,'LVGB545',2980,'2012-09-15 11:42:57.000','李四',null union all
select 4,'LVGB623',1980,'2012-09-26 13:42:57.000','张三',null union all
select 5,'LVGB532',-2980,'2012-09-27 14:42:57.000','王五',null union all
select 8,'LVGB234',2980,'2012-09-28 15:42:57.000','王五',null union all
select 10,'LVGB623',1980,'2012-09-28 13:42:57.000','王五',NULL
SELECT a.销售员,
SUM(a.[1980]) AS [1980],
SUM(a.[2980]) AS [2980],
SUM(a.[1980])+SUM(a.[2980]) AS 次数
FROM 
(SELECT 销售员,
SUM(CASE 销售金额 WHEN 1980 THEN 1 ELSE 0 END) AS [1980],
SUM(CASE 销售金额 WHEN 2980 THEN 1 ELSE 0 END) AS [2980]
FROM #tb1 GROUP BY 销售员
UNION ALL
SELECT 销售员,
SUM(CASE 充值金额 WHEN 1980 THEN 1 WHEN -1980 THEN -1 ELSE 0 END) AS [1980], 
 
SUM(CASE 充值金额 WHEN 2980 THEN 1 WHEN -2980 THEN -1 ELSE 0 END) AS [2980]
FROM #tb2 GROUP BY 销售员) AS a GROUP BY a.销售员

销售员19802980总次数
李四123
王五123
张三404

查看更多 下一篇