zt:j9988rowid flag
----------- -----------
1 1
2 1
3 1
4 2
5 2
6 2
7 1
8 1如何使用sql语句得到以下结果:
beginid endid flag
1 3 1
4 6 2
7 8 1select a.rowid,b.rowid,a.flag from t2 a,t2 b where a.rowid<b.rowid and a.flag=b.flag
and not exists(select 1 from t2 where rowid=a.rowid-1 and flag=a.flag)
and not exists(select 1 from t2 where rowid=b.rowid+1 and flag=b.flag)
and not exists(select 1 from t2 where rowid>a.rowid and rowid<b.rowid and flag<>a.flag)
select * from OPENDATASOURCE('SQLOLEDB','Data Source=218.5.165.5;User ID=sa;Password=ruirui').pubs.dbo.jobs
-----------------
set XACT_ABORT on
set ANSI_NULL_DFLT_ON on
set ANSI_WARNINGS on
BEGIN DISTRIBUTED TRANSACTION
select * from OPENDATASOURCE('SQLOLEDB','Data Source=218.5.165.5;User ID=sa;Password=ruirui').pubs.dbo.jobs
commit tran
----------------
set XACT_ABORT on
set ANSI_NULL_DFLT_ON on
set ANSI_WARNINGS on
BEGIN DISTRIBUTED TRANSACTION
select * from OPENDATASOURCE('MSDASQL','DRIVER={SQL Server};SERVER=218.5.165.5;UID=sa;PWD=ruirui;').pubs.dbo.jobs
commit tran
----------- -----------
1 1
2 1
3 1
4 2
5 2
6 2
7 1
8 1如何使用sql语句得到以下结果:
beginid endid flag
1 3 1
4 6 2
7 8 1select a.rowid,b.rowid,a.flag from t2 a,t2 b where a.rowid<b.rowid and a.flag=b.flag
and not exists(select 1 from t2 where rowid=a.rowid-1 and flag=a.flag)
and not exists(select 1 from t2 where rowid=b.rowid+1 and flag=b.flag)
and not exists(select 1 from t2 where rowid>a.rowid and rowid<b.rowid and flag<>a.flag)
select * from OPENDATASOURCE('SQLOLEDB','Data Source=218.5.165.5;User ID=sa;Password=ruirui').pubs.dbo.jobs
-----------------
set XACT_ABORT on
set ANSI_NULL_DFLT_ON on
set ANSI_WARNINGS on
BEGIN DISTRIBUTED TRANSACTION
select * from OPENDATASOURCE('SQLOLEDB','Data Source=218.5.165.5;User ID=sa;Password=ruirui').pubs.dbo.jobs
commit tran
----------------
set XACT_ABORT on
set ANSI_NULL_DFLT_ON on
set ANSI_WARNINGS on
BEGIN DISTRIBUTED TRANSACTION
select * from OPENDATASOURCE('MSDASQL','DRIVER={SQL Server};SERVER=218.5.165.5;UID=sa;PWD=ruirui;').pubs.dbo.jobs
commit tran
from 你的表
group by 操作员号
progress99:你得代码没有考虑中间断号的情况啊。
拜托帮帮忙。
where a.id<b.id and a.操作员号=b.操作员号
and not exists(select 1 from table12 where id=a.id-1 and 操作员号=a.操作员号)
and not exists(select 1 from table12 where id=b.id+1 and 操作员号=b.操作员号)
and not exists(select 1 from table12 where id>a.id and id<b.id and 操作员号<>a.操作员号)把table12换成你的表名就可以了
我在你的句子里加了一个时间区间。select a.fph,b.fph,a.czyh from SF_BRJSK a,SF_BRJSK b
where a.fph<b.fph and a.czyh=b.czyh ---and a.rq>='2004020100:00:00' and a.rq<='2004022923:59:59'
and not exists(select 1 from SF_BRJSK where fph=a.fph-1 and czyh=a.czyh)
and not exists(select 1 from SF_BRJSK where fph=b.fph+1 and czyh=b.czyh)
and not exists(select 1 from SF_BRJSK where fph>a.fph and fph<b.fph and czyh<>a.czyh)
order by a.czyh