我一个字段是1-10,如果删除5,我要得到的结果是
代码 详细号码
001 1-4
001 6-10
两行数据,这要怎么写?
(我之前是用min和max关键字拼凑起来,但是就只能拼成1-10这种结果,中间删除的5在结果中体现不出来:select BCode as 代码, rtrim(MIN(bnum))+'-'+rtrim(Max(bnum)) as 详细号码 from 表名 where BName='张三'group by all BCode)
代码 详细号码
001 1-4
001 6-10
两行数据,这要怎么写?
(我之前是用min和max关键字拼凑起来,但是就只能拼成1-10这种结果,中间删除的5在结果中体现不出来:select BCode as 代码, rtrim(MIN(bnum))+'-'+rtrim(Max(bnum)) as 详细号码 from 表名 where BName='张三'group by all BCode)
MSSQL求連續ID內數量合計-常見解決方案
-- 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 行受影响)
*/
from (
select bnum,bnum-num as n
form (
select bnum,row_number() over(order by bnum) as num
from tb
)T1
)T2
group by n
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
当我执行这个存储过程的时候(数据没有05,所以期初库存只有9条)
exec pro_BNamePrintInfo '建筑安装','2009-01-01','2009-12-12'
得到的结果是:
发票号码 发票名称 期初库存 具体号码 本期开具 具体号码
0000001 一般发票 9 01-10 9 01-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 先用子查询分组,然后连接再min,max...