请教各位大大，怎样修改这个存储过程？

Question

我要写一个发票领用存储过程
BID    BCode（代码） BName(名称)   BNum(详细号码)   BOldState（原始）  BNewState(操作后状态) 
1       110          一般发票          001              库存             开具
2       110          一般发票          002              库存             开具
3       110          一般发票          003              库存             开具
（假如4被删除）
5       110          一般发票          005              库存             库存
6       110          一般发票          006              库存             库存
7       110          一般发票          007              库存             开具
8       110          一般发票          008              库存             开具
9       110          一般发票          009              库存             开具
10      110          一般发票          010              库存             开具
要得到的结果是：
代码        库存   名称     发票号码    开具  具体号码  
0000001      3   一般发票    01-03       3      01-03 
0000001      6   一般发票    05-10       4      07-10 我自己是这样写的，但是没有达到下面的效果
if exists(select * from sysobjects where name='pro_BNamePrintInfo')
drop proc pro_BNamePrintInfo
go
create proc pro_BNamePrintInfo
 @B_Name varchar(16),--发票据名称
 @B_start varchar(16), --起始时间
 @B_End   varchar(16) --终止时间
as
begin
select distinct b.BCode as 代码, b.BName as 名称,
库存      =z.库存,
发票号码      =z.发票号码,
开具      =开具,
发票号码      =k.发票号码
from BillDate b 
 join (select BCode,count(*) as 库存, rtrim(MIN(bnum))+'-'+rtrim(Max(bnum))  as 发票号码       from BillDate where  BOldState='库存'group by all bcode) z on z.BCode=b.BCode
 join (select BCode,rtrim(MIN(bnum))+'-'+rtrim(Max(bnum)) as 发票号码,count(*)   as 开具       from BillDate where  BNewState='开具'  group by all BCode) k on k.BCode=b.BCode
where b.BName=@B_Name and b.BDate between @B_start and @B_End 
end
go
--exec pro_BNamePrintInfo '一般发票','2009-01-01','2009-12-12'
执行后，得到的结果是：
代码        库存   名称     发票号码    开具  具体号码  
0000001      9   一般发票   001-0010     7     001-010 
请问各位前辈，要怎么改？

Answer 1

之前有人给过我思路，说是断缺号问题，并给了我列子，小弟不才，没改出来
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-04 15:08:18
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--    Nov 24 2008 13:01:59 
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int)
insert [tb]
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
--------------开始查询--------------------------
select case when min(t.id)<>max(t.id) then cast(min(t.id) as varchar)+'-'+cast(max(t.id) as varchar) else cast(min(t.id) as varchar) end
from
(
select id,id-(select count(*) from tb b where a.id>=b.id)  groupid
from tb a
) t
group by groupid 
----------------结果----------------------------
/*-------------------------------------------------------------
1-4
6-10(2 行受影响)
 
*/

Answer 2

--> 测试数据: @tb
declare @tb table (BID int,BCode int,BName varchar(8),BNum varchar(3),BOldState varchar(4),BNewState varchar(4))
insert into @tb
select 1,110,'一般发票','001','库存','开具' union all
select 2,110,'一般发票','002','库存','开具' union all
select 3,110,'一般发票','003','库存','开具' union all
select 5,110,'一般发票','005','库存','库存' union all
select 6,110,'一般发票','006','库存','库存' union all
select 7,110,'一般发票','007','库存','开具' union all
select 8,110,'一般发票','008','库存','开具' union all
select 9,110,'一般发票','009','库存','开具' union all
select 10,110,'一般发票','010','库存','开具'---->sql 2005
;with szy as(
select px=bid-row_number()over(order by bid asc ),BName ,BNum ,BOldState ,BNewState
from @tb

)
select 代码='0000001',BName ,BNum ,BOldState ,BNewState from (
select px,BName=max(BName),BNum=min(BNum)+'-'+max(BNum),BOldState=count(*),BNewState=sum(case when BNewState='开具' then 1 else 0 end) from szy
group by px
)t
代码      BName    BNum    BOldState   BNewState
------- -------- ------- ----------- -----------
0000001 一般发票     001-003 3           3
0000001 一般发票     005-010 6           4(2 行受影响)

Answer 3

能改成SQL2000嘛？我用的是SQL2000。

Answer 4

declare @tb table (bid int,bcode int,bname varchar(10),bnum int,
           blodstate nvarchar(10),bnewstate nvarchar(10))
insert into @tb select 1,110,'一般发票',001,'库存','开具'
      union all select 2,110,'一般发票',002,'库存','开具'
      union all select 3,110,'一般发票',003,'库存','开具'
      union all select 5,110,'一般发票',005,'库存','库存'
      union all select 6,110,'一般发票',006,'库存','库存'
      union all select 7,110,'一般发票',007,'库存','开具'
      union all select 8,110,'一般发票',008,'库存','开具'
      union all select 9,110,'一般发票',009,'库存','开具'
      union all select 10,110,'一般发票',010,'库存','开具'
select bcode,发票号码=ltrim(min(bnum))+'-'+ltrim(max(bnum)) ,
   具体号码=ltrim(min(case when bnewstate='开具' then bnum end))+'-'+ltrim(max(case when bnewstate='开具' then bnum end)) from (
select *,px=bid-row_number()over(order by bid) from @tb) tb group by bcode,px
    /*
bcode       发票号码                      具体号码
----------- ------------------------- -------------------------
110         1-3                       1-3
110         5-10                      7-10
警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)*/

Answer 5

--SQL 2000
/*=============================================*/
--> author:Ken Wong
--> Add date:2009-12-06 10:24:03
/*=============================================*/
--> 测试数据：[tb]
if object_id('[BillDate]') is not null drop table [BillDate]
create table [BillDate]([BID] int,[BCode] varchar(20),[BName] varchar(8),[BNum] varchar(3),[BOldState] varchar(4),[BNewState] varchar(4))
insert [BillDate]
select 1,'0000001','一般发票','001','库存','开具' union all
select 2,'0000001','一般发票','002','库存','开具' union all
select 3,'0000001','一般发票','003','库存','开具' union all
select 5,'0000001','一般发票','005','库存','库存' union all
select 6,'0000001','一般发票','006','库存','库存' union all
select 7,'0000001','一般发票','007','库存','开具' union all
select 8,'0000001','一般发票','008','库存','开具' union all
select 9,'0000001','一般发票','009','库存','开具' union all
select 10,'0000001','一般发票','010','库存','开具'select BCode,
sum(case BOldState when '库存' then 1 else 0 end) as 库存,
BName,
min(BNum) + '-' + max(BNum) as 发票号码,
sum(case BNewState when '开具' then 1 else 0 end) as 开具
from (
select case BID when (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) then 1 else 2 end as [groupcode],* 
from [BillDate] t
) h
group by groupcode,BCode,BName
------------------------------
0000001 3 一般发票 001-003 3
0000001 6 一般发票 005-010 4

Answer 6

这种效果之前我就有问到，关键是我要把这语句插到我上面的存储过程里面的join语句，然后得到我要的效果，麻烦各位高人帮忙看看

Answer 7

if exists(select * from sysobjects where name='pro_BNamePrintInfo') 
drop proc pro_BNamePrintInfo 
go create proc pro_BNamePrintInfo 
@B_Name varchar(16),--发票据名称 
@B_start varchar(16), --起始时间 
@B_End  varchar(16) --终止时间 
as 
begin 
select  bcode,BName ,BNum ,BOldState ,BNewState from (
select px=bid-px,bcode=max(bcode),BName=max(BName),BNum=min(BNum)+'-'+max(BNum),BOldState=count(*),BNewState=sum(case when BNewState='开具' then 1 else 0 end) 
from (
select *,px=(select count(*) from BillDate where bcode=t.bcode and bid<=t.bid) from BillDate t
)t
group by bid-px 
)tt 
end
go 
 

Answer 8

--改一下，使用于多处断点的情况
/*=============================================*/
--> author:Ken Wong
--> Add date:2009-12-06 10:24:03
/*=============================================*/
--> 测试数据：[tb]
if object_id('[BillDate]') is not null drop table [BillDate]
create table [BillDate]([BID] int,[BCode] varchar(20),[BName] varchar(8),[BNum] varchar(3),[BOldState] varchar(4),[BNewState] varchar(4))
insert [BillDate]
select 1,'0000001','一般发票','001','库存','开具' union all
select 2,'0000001','一般发票','002','库存','开具' union all
select 3,'0000001','一般发票','003','库存','开具' union all
select 5,'0000001','一般发票','005','库存','库存' union all
select 6,'0000001','一般发票','006','库存','库存' union all
--select 7,'0000001','一般发票','007','库存','开具' union all
select 8,'0000001','一般发票','008','库存','开具' union all
--select 9,'0000001','一般发票','009','库存','开具' union all
select 10,'0000001','一般发票','010','库存','开具'
select BCode,
sum(case BOldState when '库存' then 1 else 0 end) as 库存,
BName,
min(BNum) + '-' + max(BNum) as 发票号码,
sum(case BNewState when '开具' then 1 else 0 end) as 开具
from (
select case BID when (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) then 0 else BID - (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) end as [groupcode],* 
from [BillDate] t
) h
group by groupcode,BCode,BName
-------------------------------
0000001 3 一般发票 001-003 3
0000001 2 一般发票 005-006 0
0000001 1 一般发票 008-008 1
0000001 1 一般发票 010-010 1

Answer 9

--> 测试数据：[tb]
if object_id('[BillDate]') is not null drop table [BillDate]
create table [BillDate]([BID] int,[BCode] varchar(20),[BName] varchar(8),[BNum] varchar(3),[BOldState] varchar(4),[BNewState] varchar(4))
insert [BillDate]
select 1,'0000001','一般发票','001','库存','开具' union all
select 2,'0000001','一般发票','002','库存','开具' union all
select 3,'0000001','一般发票','003','库存','开具' union all
select 5,'0000001','一般发票','005','库存','库存' union all
select 6,'0000001','一般发票','006','库存','库存' union all
select 7,'0000001','一般发票','007','库存','开具' union all
select 8,'0000001','一般发票','008','库存','开具' union all
select 9,'0000001','一般发票','009','库存','开具' union all
select 10,'0000001','一般发票','010','库存','开具'
select BCode,
sum(case BOldState when '库存' then 1 else 0 end) as 库存,
BName,
min(BNum) + '-' + max(BNum) as 发票号码,
sum(case h.BNewState when '开具' then 1 else 0 end) as 开具,
(select min(BNum)+'-'+max(BNum) from (
select case BID when (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) then 0 else BID - (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) end as [groupcode],* 
from [BillDate] t
) r where BNewState = '开具' and r.groupcode = h.groupcode) as 具体号码
from (
select case BID when (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) then 0 else BID - (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) end as [groupcode],* 
from [BillDate] t
) h 
group by h.groupcode,h.BCode,h.BName
----------------------------------
0000001 3 一般发票 001-003 3 001-003
0000001 6 一般发票 005-010 4 007-010

Answer 10

--存储过程
create proc pro_BNamePrintInfo 
@B_Name varchar(16),--发票据名称 
@B_start varchar(16), --起始时间 
@B_End  varchar(16) --终止时间 
as 
begin 
select BCode,
sum(case BOldState when '库存' then 1 else 0 end) as 库存,
BName,
min(BNum) + '-' + max(BNum) as 发票号码,
sum(case h.BNewState when '开具' then 1 else 0 end) as 开具,
(select min(BNum)+'-'+max(BNum) from (
select case BID when (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) then 0 else BID - (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) end as [groupcode],* 
from [BillDate] t where BName = @B_Name and BDate between @B_start and @B_End 
) r where BNewState = '开具' and r.groupcode = h.groupcode) as 具体号码
from (
select case BID when (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) then 0 else BID - (select count(1) from [BillDate] where 
BCode = t.BCode and BID <= t.BID) end as [groupcode],* 
from [BillDate] t where BName = @B_Name and BDate between @B_start and @B_End 
) h 
group by h.groupcode,h.BCode,h.BNameend 

Answer 11

我如果还有其他状态的呢？比如还有一个BNewState='退回'的状态呢，语句怎么加？

Answer 12

对了，还有个问题，sum(case BOldState when '库存' then 1 else 0 end) as 库存,
    BName,
    min(BNum) + '-' + max(BNum) as 发票号码 ， 我表里BOldState原始状态还有一种情况就是等于“领购”：BOldState='领购'，这要怎么改？