sql语句，我想了几个小时了也没有想到简单的写法

[zjmym]

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_1008 -
m_1009 -
m_1019 重新登录(&L)
m_1020 退出系统(&E)
m_1021 -
要求按照aaz020排序后，最顶层，和最底层的aaz021不包含‘-’列，
中间不能有连续的‘-’列

[zjmym]

hudan(中文昵称)
1。不能用临时表，
2。首位末位所有连续带‘-’的都要删掉。
‘-’是表示做分割，所以开头结尾不需要，中间不需要重复的

[hudan]

你的表有没有主键？

[zheninchangjiang]

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) )))

[zheninchangjiang]

测试了一下部分数据能成

[zjmym]

和主键没有关系，主键设置在aaz020上的话根本就影响不了aaz021，
实际开始的结果也是联合查询得出的，我只是需要进一步过滤结果

[zheninchangjiang]

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 行）
*/

[hudan]

to zheninchangjiang:
需要改进一下，如果开始2行连续的-的话，有点问题

[huwgao]

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='-')

[zjmym]

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)

[zjmym]

丢失数据了

[huwgao]

徐的有两个问题：
1、末尾连续两个“-”，只能删除1个（我的也存在这个问题）
2、如果首位是“-”，中间的“-”都被删除了。

[zheninchangjiang]

那倒是忘了，没有考虑，我再想想

[zjmym]

看来这个问题比较罗嗦，谢谢大家，大家再帮忙想一下，不行我就在程序里处理，
如果能解决的话，另开帖加分

[zjmym]

hudan(中文昵称)
还没有睡觉吗？如果在的话准备领分

[huwgao]

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

[huwgao]

--再简化些

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,'-')

[zheninchangjiang]

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<>'-')))

[zjmym]

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

基本搞定了

[hudan]

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))
)

[zjmym]

呵呵，也可以，谢谢