问题如下:
billform表如下:
注意:billnumber为字符串型!!!
billnumber billclass billconditon applyperson
001 财务票 1 张三
002 财务票 1 张三
003 财务票 1 张三
004 财务票 1 张三
005 财务票 1 李四
006 财务票 1 李四
010 后勤票 2 李四
011 后勤票 2 李四
012 后勤票 2 李四
020 财务票 1 李四
021 财务票 1 李四
022 财务票 1 李四
要求查询出来后显示如下:
billnumber billclass billcondition applyperson
001-004 财务票 1 张三
005-006 财务票 1 李四
010-012 后勤票 2 李四
020-022 财务票 1 李四要求按照billclass ,billcondition,applyperson分组,并把billnumber字段连续的部分集合起来,并在此谢谢各位了!!!!可能比较复杂!!!万分感谢!!
billform表如下:
注意:billnumber为字符串型!!!
billnumber billclass billconditon applyperson
001 财务票 1 张三
002 财务票 1 张三
003 财务票 1 张三
004 财务票 1 张三
005 财务票 1 李四
006 财务票 1 李四
010 后勤票 2 李四
011 后勤票 2 李四
012 后勤票 2 李四
020 财务票 1 李四
021 财务票 1 李四
022 财务票 1 李四
要求查询出来后显示如下:
billnumber billclass billcondition applyperson
001-004 财务票 1 张三
005-006 财务票 1 李四
010-012 后勤票 2 李四
020-022 财务票 1 李四要求按照billclass ,billcondition,applyperson分组,并把billnumber字段连续的部分集合起来,并在此谢谢各位了!!!!可能比较复杂!!!万分感谢!!
select (min(billnumber)+max(billnumber)) as billnumber,
billclass,
max(billconditon ) as billconditon,
applyperson
from billform
group by billclass,applyperson
billclass,
max(billconditon ) as billconditon,
applyperson
from billform
group by billclass,applyperson
,billclass,billcondition ,applyperson From billform Group by billclass,billcondition ,applyperson
结果为:
005-022 财务票 1 李四
010-012 后勤票 2 李四
001-004 财务票 1 张三
还是没有解决啊啊!!1%>_<%%>_<%谁来帮帮我
insert into @t select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 12
union all select 17
union all select 18
union all select 19
union all select 20
union all select 25 select
rtrim(a.num)+(case when min(b.num)!=a.num then '-'+rtrim(min(b.num)) else '' end)
from
(select t.num from @t t where not exists(select 1 from @t where num=t.num-1)) a,
(select t.num from @t t where not exists(select 1 from @t where num=t.num+1)) b
where
a.num<=b.num
group by
a.num/*
-------------------------
1-5
12
17-20
25
*/
我好笨啊,6楼写的怎么看不懂啊,这是sql语句吗?可以直接放到asp.net中吗?谁教教我
select
MIN(billnumber)+'-'+MAX(billnumber) billnumber,billclass,billconditon,applyperson
from (
select a.*,b.s from billform a,(
select a.billnumber s,MIN(b.billnumber) e
from
(
select t.billnumber
from billform t
where not exists(select 1 from billform where billnumber=t.billnumber-1)
) a,
(
select t.billnumber
from billform t
where not exists(select 1 from billform where billnumber=t.billnumber+1)
) b
where a.billnumber<=b.billnumber
group by a.billnumber
) b
where a.billnumber>=b.s and a.billnumber<=b.e
) billform
group by billclass,billconditon,applyperson,s
order by billnumber接着6楼写的
as
select
MIN(billnumber)+'-'+MAX(billnumber) billnumber,billclass,billconditon,applyperson
from (
select a.*,b.s from billform a,(
select a.billnumber s,MIN(b.billnumber) e
from
(
select t.billnumber
from billform t
where not exists(select 1 from billform where billnumber=t.billnumber-1)
) a,
(
select t.billnumber
from billform t
where not exists(select 1 from billform where billnumber=t.billnumber+1)
) b
where a.billnumber<=b.billnumber
group by a.billnumber
) b
where a.billnumber>=b.s and a.billnumber<=b.e
) billform
group by billclass,billconditon,applyperson,s
order by billnumber
return
go
再在asp.net中调用时写
exec aa
请教8楼!!正解!
create proc aa
as
return
go
这作用是什么
再在asp.net中调用时写
exec aa
具体怎么调用,这段代码应该写在哪边
--建立测试环境
create table #t
(
billnumber varchar(3),
billclass varchar(10),
billconditon int,
applyperson varchar(10)
)
insert into #t select '001','财务票', 1,'张三'
insert into #t select '002','财务票', 1,'张三'
insert into #t select '003','财务票', 1,'张三'
insert into #t select '004','财务票', 1,'张三'
insert into #t select '005','财务票', 1,'李四'
insert into #t select '006','财务票', 1,'李四'
insert into #t select '010','后勤票', 2,'李四'
insert into #t select '011','后勤票', 2,'李四'
insert into #t select '012','后勤票', 2,'李四'
insert into #t select '020','财务票', 1,'李四'
insert into #t select '021','财务票', 1,'李四'
insert into #t select '022','财务票', 1,'李四'--查询select billnumber = billnumber + '-' + (select min(billnumber) from #t t2 where billnumber > t1.billnumber and not exists (select 1 from #t
where billclass = t2.billclass and billconditon = t2.billconditon
and applyperson = t2.applyperson and cast(billnumber as int) = cast(t2.billnumber as int) + 1)),
billclass,billconditon,applyperson
from #t t1
where not exists (select 1 from #t
where billclass = t1.billclass and billconditon = t1.billconditon
and applyperson = t1.applyperson and cast(billnumber as int) = cast(t1.billnumber as int) - 1)
--结果
/*
billnumber billclass billconditon applyperson
---------- ---------- ------------ -----------
001-004 财务票 1 张三
005-006 财务票 1 李四
010-012 后勤票 2 李四
020-022 财务票 1 李四(4 行受影响)
*/
drop table #t
--修正一下,增加特殊数据判断
--增加特殊数据
insert into #t select '025','财务票', 1,'李四'--查询语句
select billnumber = billnumber + '-' + (select isnull(min(billnumber),'') from #t t2 where billnumber >= t1.billnumber and not exists (select 1 from #t
where billclass = t2.billclass and billconditon = t2.billconditon
and applyperson = t2.applyperson and cast(billnumber as int) = cast(t2.billnumber as int) + 1)),
billclass,billconditon,applyperson
from #t t1
where not exists (select 1 from #t
where billclass = t1.billclass and billconditon = t1.billconditon
and applyperson = t1.applyperson and cast(billnumber as int) = cast(t1.billnumber as int) - 1)--执行结果
/*billnumber billclass billconditon applyperson
---------- ---------- ------------ -----------
001-004 财务票 1 张三
005-006 财务票 1 李四
010-012 后勤票 2 李四
020-022 财务票 1 李四
025-025 财务票 1 李四(5 行受影响)*/