--没测试过,试试先,中间连续的"-"仅保留1个select identity(int,1,1)id,aaz020 into #t from tablename order by aaz020select o.aaz020,o.aaz021 from table o inner join (select id=1,aaz020='-' union all select id=id+1,aaz020 from table) p on o.id=b.id and o.aaz020<>p.aaz020 inner join (select id=id-1,aaz020 from table union all select id=(select max(id) from #t),aaz020='-') q on o.id=q.id and o.aaz020<>q.aaz020drop table #t
declare @t table(aaz020 varchar(10),aaz021 varchar(30)) insert into @t select 'm_06','业务处理(&O)' union all select 'm_0603','汇缴缴款书处理(&E)' union all select 'm_0604','汇缴缴款书列表' union all select 'm_0605','生成汇缴缴款书' union all select 'm_0606','打印汇缴缴款书' union all select 'm_0607','手工票处理' union all select 'm_0608','缴款书删除' union all select 'm_0609','与财政核对' union all select 'm_0610','与银行核对' union all select 'm_0611','收费数据导入' union all select 'm_07','数据申请(&D)' union all select 'm_0701','退付申请' union all select 'm_0702','调整申请' union all select 'm_0703','票据认证' union all select 'm_0704','数据交换' union all select 'm_0705','文件交换' union all select 'm_08','统计查询(&C)' union all select 'm_0802','缴款书信息查询' union all select 'm_0803','收费情况查询' union all select 'm_0804','分成统计查询' union all select 'm_0805','收费项目收入分类查询' union all select 'm_0806','缴款书使用情况查询' union all select 'm_0807','基础数据查询' union all select 'm_0808','单位收入台账' union all select 'm_0809','银行网点收费情况统计表' union all select 'm_09','基础数据维护(&B)' union all select 'm_0902','缴款人维护(&O)' union all select 'm_0903','辅助核算维护' union all select 'm_0904','票据入库(&I)' union all select 'm_0905','库存处理(&S)' union all select 'm_10','系统维护(&S)' union all select 'm_1001','关于(&A)' union all select 'm_1002','权限管理(&O)' union all select 'm_1003','修改密码(&C)' union all select 'm_1005','数据库备份(&B)' union all select 'm_1006','数据库恢复(&R)' union all select 'm_1007','打印机设置(&P)' union all select 'm_1008','-' union all select 'm_1009','-' union all select 'm_1019','重新登录(&L)' union all select 'm_1020','退出系统(&E)' union all select 'm_1021','-'select identity(int,1,1)id,aaz020,aaz021 into #t from @t order by aaz020select o.aaz020,o.aaz021 from #t o inner join (select id=1,aaz021='-' union all select id=id+1,aaz021 from #t) p on o.id=p.id and o.aaz021<>p.aaz021 inner join (select id=id-1,aaz021 from #t union all select id=(select max(id) from #t),aaz021='-') q on o.id=q.id and o.aaz021<>q.aaz021drop table #t--返回(所影响的行数为 42 行) (所影响的行数为 42 行)aaz020 aaz021 ---------- ------------------------------ m_06 业务处理(&O) m_0603 汇缴缴款书处理(&E) m_0604 汇缴缴款书列表 m_0605 生成汇缴缴款书 m_0606 打印汇缴缴款书 m_0607 手工票处理 m_0608 缴款书删除 m_0609 与财政核对 m_0610 与银行核对 m_0611 收费数据导入 m_07 数据申请(&D) m_0701 退付申请 m_0702 调整申请 m_0703 票据认证 m_0704 数据交换 m_0705 文件交换 m_08 统计查询(&C) m_0802 缴款书信息查询 m_0803 收费情况查询 m_0804 分成统计查询 m_0805 收费项目收入分类查询 m_0806 缴款书使用情况查询 m_0807 基础数据查询 m_0808 单位收入台账 m_0809 银行网点收费情况统计表 m_09 基础数据维护(&B) m_0902 缴款人维护(&O) m_0903 辅助核算维护 m_0904 票据入库(&I) m_0905 库存处理(&S) m_10 系统维护(&S) m_1001 关于(&A) m_1002 权限管理(&O) m_1003 修改密码(&C) m_1005 数据库备份(&B) m_1006 数据库恢复(&R) m_1007 打印机设置(&P) m_1019 重新登录(&L) m_1020 退出系统(&E)
lxrxyz(无忧十年) m_1008,m_1009保留任意一个就可以了 m_1021要删掉
select identity(int,1,1) id,aaz020,aaz021 into #t from t1 where 1=1 --其他条件 order by aaz020select * from #t a where not exists (select 1 from #t b where a.id=b.id+1 and a.aaz021='-' and b.aaz021='-') --排除连续是-的 and not (a.aaz021='-' and (a.id=1 or a.id=(select max(id) from #t))) --排除首位是-的drop table #t
select * from yourtable a where not (exists( select 1 from yourtable aa where aa.aaz021='-' and (select count(*) from yourtable where aaz020<=a.aaz020)= (select count(*) from yourtable where aaz020<= (select min(aaz020) from yourtable where aaz020>aa.aaz020 and aaz021='-') )-1) or (a.aaz021='-' and (a.aaz020=(select min(aaz020) from yourtable) or a.aaz020=(select max(aaz020) from yourtable) )))
create table yourtable (aaz020 varchar(20), aaz021 varchar(20)) insert into yourtable select 'm_1005', '数据库备份(&B)' union all select 'm_1006', '数据库恢复(&R)' union all select 'm_1007', '打印机设置(&P)' union all select 'm_1008', '-' union all select 'm_1009', '-' union all select 'm_1019', '重新登录(&L)' union all select 'm_1020', '退出系统(&E)' union all select 'm_1021', '-'select * from yourtable a where not (exists( select 1 from yourtable aa where aa.aaz021='-' and (select count(*) from yourtable where aaz020<=a.aaz020)= (select count(*) from yourtable where aaz020<= (select min(aaz020) from yourtable where aaz020>aa.aaz020 and aaz021='-') )-1) or (a.aaz021='-' and (a.aaz020=(select min(aaz020) from yourtable) or a.aaz020=(select max(aaz020) from yourtable) )))/* aaz020 aaz021 -------------------- -------------------- m_1005 数据库备份(&B) m_1006 数据库恢复(&R) m_1007 打印机设置(&P) m_1009 - m_1019 重新登录(&L)(所影响的行数为 5 行) */
to zheninchangjiang: 需要改进一下,如果开始2行连续的-的话,有点问题
select o.aaz020,o.aaz021 from #t o inner join (select id=1,aaz021='-' union all select id=id+1,aaz021 from #t) p on o.id=p.id and o.aaz021<>p.aaz021 where o.id not in (select id from #t where id=(select max(id) from #t) and aaz021='-')
create table yourtable (aaz020 varchar(20), aaz021 varchar(20))insert into yourtable select 'm_1005', '数据库备份(&B)' union all select 'm_1006', '数据库恢复(&R)' union all select 'm_1007', '打印机设置(&P)' union all select 'm_1008', '-' union all select 'm_1009', '-' union all select 'm_1019', '重新登录(&L)' union all select 'm_1020', '退出系统(&E)' union all select 'm_1021', '-'
insert into yourtable select 'm_1004', '-' union all select 'm_1022', '-'--select * from yourtable order by aaz020 select * from yourtable a where not (exists( select 1 from yourtable aa where aa.aaz021='-' and (select count(*) from yourtable where aaz020<=a.aaz020)= (select count(*) from yourtable where aaz020<= (select min(aaz020) from yourtable where aaz020>aa.aaz020 and aaz021='-') )-1) or (a.aaz021='-' and (a.aaz020=(select min(aaz020) from yourtable) or a.aaz020=(select max(aaz020) from yourtable) ))) 结果 m_1005 数据库备份(&B) m_1006 数据库恢复(&R) m_1009 - m_1019 重新登录(&L)
select aaz020,aaz021 from ( select aaz020,aaz021 ,(select top 1 aaz021 from ( select * from a where aaz020>=(select min(aaz020) from a where aaz021<>'-') and aaz020<=(select max(aaz020) from a where aaz021<>'-') ) bb where aaz020>b.aaz020 order by aaz020 ) as 'aaz021_' from ( select * from a where aaz020>=(select min(aaz020) from a where aaz021<>'-') and aaz020<=(select max(aaz020) from a where aaz021<>'-') ) as b ) c where isnull(aaz021,'0')<>isnull(aaz021_,'0') order by aaz020基本搞定了
zjmym(缘木) 的可以 我也写了一个: select aaz020,aaz021 from t1 a where a.aaz021<>'-' or not ( --排除开始的- exists( select 1 from (select sum(case b.aaz021 when '-' then 1 else 0 end) as sum1,sum(1) as sum2 from t1 b where b.aaz020<=a.aaz020) z where z.sum1=z.sum2) or -- 排除结尾的- exists( select 1 from (select sum(case b.aaz021 when '-' then 1 else 0 end) as sum1,sum(1) as sum2 from t1 b where b.aaz020>=a.aaz020) z where z.sum1=z.sum2) or -- 排除中间重复的- exists( select 1 from t1 z where z.aaz021='-' and z.aaz020=(select min(b.aaz020) from t1 b where b.aaz020>a.aaz020)) )
hudan(中文昵称) 还没有睡觉吗?如果在的话准备领分
select c.aaz020,c.aaz021 from ( select aaz020,aaz021 from ( select aaz020,aaz021, case when aaz021='-' then (select top 1 aaz021 from @t where aaz020<a.aaz020 order by aaz020 desc) else '' end as aaz022 from @t as a ) as b where aaz021<>isnull(aaz022,'-') ) as c inner join (select aaz020,aaz021 from @t where aaz020>=(select min(aaz020) from @t where aaz021<>'-') and aaz020<=(select max(aaz020) from @t where aaz021<>'-') ) as d on c.aaz020=d.aaz020
--再简化些select aaz020,aaz021 from ( select aaz020,aaz021, case when aaz020>(select max(aaz020) from @t where aaz021<>'-') then '-' else case when aaz021='-' then (select top 1 aaz021 from @t where aaz020<a.aaz020 order by aaz020 desc) else '' end end as aaz022 from @t as a ) as b where aaz021<>isnull(aaz022,'-')
select * from yourtable a where not (exists( select 1 from yourtable aa where aa.aaz021='-' and (select count(*) from yourtable where aaz020<=a.aaz020)= (select count(*) from yourtable where aaz020<= (select min(aaz020) from yourtable where aaz020>aa.aaz020 and aaz021='-') )-1) or (a.aaz021='-' and (a.aaz020=(select min(aaz020) from yourtable))) or (a.aaz021='-' and not exists(select 1 from yourtable where aaz020>a.aaz020 and aaz021<>'-')))
from tablename
order by aaz020select o.aaz020,o.aaz021
from table o
inner join
(select id=1,aaz020='-' union all
select id=id+1,aaz020 from table) p
on o.id=b.id and o.aaz020<>p.aaz020
inner join
(select id=id-1,aaz020 from table
union all select id=(select max(id) from #t),aaz020='-') q
on o.id=q.id and o.aaz020<>q.aaz020drop table #t
insert into @t select 'm_06','业务处理(&O)'
union all select 'm_0603','汇缴缴款书处理(&E)'
union all select 'm_0604','汇缴缴款书列表'
union all select 'm_0605','生成汇缴缴款书'
union all select 'm_0606','打印汇缴缴款书'
union all select 'm_0607','手工票处理'
union all select 'm_0608','缴款书删除'
union all select 'm_0609','与财政核对'
union all select 'm_0610','与银行核对'
union all select 'm_0611','收费数据导入'
union all select 'm_07','数据申请(&D)'
union all select 'm_0701','退付申请'
union all select 'm_0702','调整申请'
union all select 'm_0703','票据认证'
union all select 'm_0704','数据交换'
union all select 'm_0705','文件交换'
union all select 'm_08','统计查询(&C)'
union all select 'm_0802','缴款书信息查询'
union all select 'm_0803','收费情况查询'
union all select 'm_0804','分成统计查询'
union all select 'm_0805','收费项目收入分类查询'
union all select 'm_0806','缴款书使用情况查询'
union all select 'm_0807','基础数据查询'
union all select 'm_0808','单位收入台账'
union all select 'm_0809','银行网点收费情况统计表'
union all select 'm_09','基础数据维护(&B)'
union all select 'm_0902','缴款人维护(&O)'
union all select 'm_0903','辅助核算维护'
union all select 'm_0904','票据入库(&I)'
union all select 'm_0905','库存处理(&S)'
union all select 'm_10','系统维护(&S)'
union all select 'm_1001','关于(&A)'
union all select 'm_1002','权限管理(&O)'
union all select 'm_1003','修改密码(&C)'
union all select 'm_1005','数据库备份(&B)'
union all select 'm_1006','数据库恢复(&R)'
union all select 'm_1007','打印机设置(&P)'
union all select 'm_1008','-'
union all select 'm_1009','-'
union all select 'm_1019','重新登录(&L)'
union all select 'm_1020','退出系统(&E)'
union all select 'm_1021','-'select identity(int,1,1)id,aaz020,aaz021 into #t
from @t
order by aaz020select o.aaz020,o.aaz021
from #t o
inner join
(select id=1,aaz021='-' union all
select id=id+1,aaz021 from #t) p
on o.id=p.id and o.aaz021<>p.aaz021
inner join
(select id=id-1,aaz021 from #t
union all select id=(select max(id) from #t),aaz021='-') q
on o.id=q.id and o.aaz021<>q.aaz021drop table #t--返回(所影响的行数为 42 行)
(所影响的行数为 42 行)aaz020 aaz021
---------- ------------------------------
m_06 业务处理(&O)
m_0603 汇缴缴款书处理(&E)
m_0604 汇缴缴款书列表
m_0605 生成汇缴缴款书
m_0606 打印汇缴缴款书
m_0607 手工票处理
m_0608 缴款书删除
m_0609 与财政核对
m_0610 与银行核对
m_0611 收费数据导入
m_07 数据申请(&D)
m_0701 退付申请
m_0702 调整申请
m_0703 票据认证
m_0704 数据交换
m_0705 文件交换
m_08 统计查询(&C)
m_0802 缴款书信息查询
m_0803 收费情况查询
m_0804 分成统计查询
m_0805 收费项目收入分类查询
m_0806 缴款书使用情况查询
m_0807 基础数据查询
m_0808 单位收入台账
m_0809 银行网点收费情况统计表
m_09 基础数据维护(&B)
m_0902 缴款人维护(&O)
m_0903 辅助核算维护
m_0904 票据入库(&I)
m_0905 库存处理(&S)
m_10 系统维护(&S)
m_1001 关于(&A)
m_1002 权限管理(&O)
m_1003 修改密码(&C)
m_1005 数据库备份(&B)
m_1006 数据库恢复(&R)
m_1007 打印机设置(&P)
m_1019 重新登录(&L)
m_1020 退出系统(&E)
m_1008,m_1009保留任意一个就可以了
m_1021要删掉
from t1 where 1=1 --其他条件
order by aaz020select * from #t a where not exists
(select 1 from #t b where a.id=b.id+1 and a.aaz021='-' and b.aaz021='-')
--排除连续是-的
and not (a.aaz021='-' and (a.id=1 or a.id=(select max(id) from #t)))
--排除首位是-的drop table #t
m_1008,m_1009要保留一个
1。不能用临时表,
2。首位末位所有连续带‘-’的都要删掉。
‘-’是表示做分割,所以开头结尾不需要,中间不需要重复的
not (exists( select 1 from yourtable aa where aa.aaz021='-' and
(select count(*) from yourtable where aaz020<=a.aaz020)=
(select count(*) from yourtable where aaz020<=
(select min(aaz020) from yourtable where aaz020>aa.aaz020 and aaz021='-')
)-1)
or (a.aaz021='-' and
(a.aaz020=(select min(aaz020) from yourtable)
or a.aaz020=(select max(aaz020) from yourtable) )))
实际开始的结果也是联合查询得出的,我只是需要进一步过滤结果
(aaz020 varchar(20), aaz021 varchar(20))
insert into yourtable select
'm_1005', '数据库备份(&B)' union all select
'm_1006', '数据库恢复(&R)' union all select
'm_1007', '打印机设置(&P)' union all select
'm_1008', '-' union all select
'm_1009', '-' union all select
'm_1019', '重新登录(&L)' union all select
'm_1020', '退出系统(&E)' union all select
'm_1021', '-'select * from yourtable a where
not (exists( select 1 from yourtable aa where aa.aaz021='-' and
(select count(*) from yourtable where aaz020<=a.aaz020)=
(select count(*) from yourtable where aaz020<=
(select min(aaz020) from yourtable where aaz020>aa.aaz020 and aaz021='-')
)-1)
or (a.aaz021='-' and
(a.aaz020=(select min(aaz020) from yourtable)
or a.aaz020=(select max(aaz020) from yourtable) )))/*
aaz020 aaz021
-------------------- --------------------
m_1005 数据库备份(&B)
m_1006 数据库恢复(&R)
m_1007 打印机设置(&P)
m_1009 -
m_1019 重新登录(&L)(所影响的行数为 5 行)
*/
需要改进一下,如果开始2行连续的-的话,有点问题
from #t o
inner join
(select id=1,aaz021='-' union all
select id=id+1,aaz021 from #t) p
on o.id=p.id and o.aaz021<>p.aaz021
where o.id not in
(select id from #t where id=(select max(id) from #t) and aaz021='-')
(aaz020 varchar(20), aaz021 varchar(20))insert into yourtable select
'm_1005', '数据库备份(&B)' union all select
'm_1006', '数据库恢复(&R)' union all select
'm_1007', '打印机设置(&P)' union all select
'm_1008', '-' union all select
'm_1009', '-' union all select
'm_1019', '重新登录(&L)' union all select
'm_1020', '退出系统(&E)' union all select
'm_1021', '-'
insert into yourtable select
'm_1004', '-' union all select
'm_1022', '-'--select * from yourtable order by aaz020
select * from yourtable a where
not (exists( select 1 from yourtable aa where aa.aaz021='-' and
(select count(*) from yourtable where aaz020<=a.aaz020)=
(select count(*) from yourtable where aaz020<=
(select min(aaz020) from yourtable where aaz020>aa.aaz020 and aaz021='-')
)-1)
or (a.aaz021='-' and
(a.aaz020=(select min(aaz020) from yourtable)
or a.aaz020=(select max(aaz020) from yourtable) )))
结果
m_1005 数据库备份(&B)
m_1006 数据库恢复(&R)
m_1009 -
m_1019 重新登录(&L)
select aaz020,aaz021
,(select top 1 aaz021 from ( select * from a where aaz020>=(select min(aaz020) from a where aaz021<>'-')
and aaz020<=(select max(aaz020) from a where aaz021<>'-')
) bb where aaz020>b.aaz020 order by aaz020 ) as 'aaz021_'
from (
select * from a where aaz020>=(select min(aaz020) from a where aaz021<>'-')
and aaz020<=(select max(aaz020) from a where aaz021<>'-')
) as b
) c
where isnull(aaz021,'0')<>isnull(aaz021_,'0')
order by aaz020基本搞定了
我也写了一个:
select aaz020,aaz021 from t1 a where
a.aaz021<>'-' or
not (
--排除开始的-
exists( select 1 from (select sum(case b.aaz021 when '-' then 1 else 0 end) as sum1,sum(1) as sum2 from t1 b where b.aaz020<=a.aaz020) z where z.sum1=z.sum2)
or
-- 排除结尾的-
exists( select 1 from (select sum(case b.aaz021 when '-' then 1 else 0 end) as sum1,sum(1) as sum2 from t1 b where b.aaz020>=a.aaz020) z where z.sum1=z.sum2)
or
-- 排除中间重复的-
exists( select 1 from t1 z where z.aaz021='-' and z.aaz020=(select min(b.aaz020) from t1 b where b.aaz020>a.aaz020))
)
还没有睡觉吗?如果在的话准备领分
from (
select aaz020,aaz021
from (
select aaz020,aaz021,
case when aaz021='-' then
(select top 1 aaz021
from @t
where aaz020<a.aaz020
order by aaz020 desc)
else ''
end as aaz022
from @t as a
) as b
where aaz021<>isnull(aaz022,'-')
) as c
inner join
(select aaz020,aaz021
from @t
where aaz020>=(select min(aaz020) from @t where aaz021<>'-')
and aaz020<=(select max(aaz020) from @t where aaz021<>'-')
) as d
on c.aaz020=d.aaz020
from (
select aaz020,aaz021,
case when aaz020>(select max(aaz020) from @t where aaz021<>'-') then
'-'
else
case when aaz021='-' then
(select top 1 aaz021
from @t
where aaz020<a.aaz020
order by aaz020 desc)
else ''
end
end as aaz022
from @t as a
) as b
where aaz021<>isnull(aaz022,'-')
not (exists( select 1 from yourtable aa where aa.aaz021='-' and
(select count(*) from yourtable where aaz020<=a.aaz020)=
(select count(*) from yourtable where aaz020<=
(select min(aaz020) from yourtable where aaz020>aa.aaz020 and aaz021='-')
)-1)
or (a.aaz021='-' and
(a.aaz020=(select min(aaz020) from yourtable)))
or (a.aaz021='-'
and not exists(select 1 from yourtable where aaz020>a.aaz020 and aaz021<>'-')))