5楼也不能说不对,但是我用UNION不能排序了。--> 测试数据:#v if object_id('tempdb.dbo.#v') is not null drop table #v create table #v(ID1 varchar(8), ID2 varchar(8), ID3 varchar(8), URL varchar(8)) insert into #v select '001', '002', '003', 'url1' union all select '002', '002', '004', 'url2' union all select '003', '003', '005', 'url3' union all select '004', '004', '006', 'url4' union all select '005', '003', '007', 'url5' --> 测试数据:#t if object_id('tempdb.dbo.#t') is not null drop table #t create table #t(ID2 varchar(8)) insert into #t select '002'select v.* from #v v left join #t t on v.ID2=t.ID2 where t.ID2 is not null or not exists (select 1 from #v where ID2=v.ID2 and URL<v.URL)/* ID1 ID2 ID3 URL -------- -------- -------- -------- 001 002 003 url1 002 002 004 url2 003 003 005 url3 004 004 006 url4 */ 这个正确的而且后面加WHERE和排序很好加,但是就是因为多了一个条件就是stepid在某些值的时候无视T表所以这句语句不对了。希望能在这上面修改
/*if object_id('TestaA') is not null drop table TestaA create table TestaA (id1 int,id2 int,id3 int,url varchar(5),stepid int)insert TestaA select 1,2,3,'url1',1 union all select 2,2,4,'url2',1 union all select 3,3,5,'url3',3 union all select 4,4,6,'url4',4 union all select 5,3,7,'url5',5 */--insert TestaA --select 6,5,5,'url1',1 union all --select 7,5,4,'url2',1 /*if object_id('TestaB') is not null drop table TestaB create table TestaB (id int)insert TestaB select 2 union all select 3*/--存在 select * from TestaA where id1 in --3,5合 (select min(id1) from TestaA where stepid in (3,5) and TestaA.id2 in (select id from TestaB) group by id2 ) union all select * from TestaA where stepid in (1,4) and TestaA.id2 in (select id from TestaB) --1,4分 union all --不存在的合,无视stepid? select * from TestaA where id1 in (select min(id1) from TestaA where id2 not in (select id from TestaB) group by id2) order by id1 /* 1 2 3 url1 1 2 2 4 url2 1 3 3 5 url3 3 4 4 6 url4 4 6 5 5 url1 1 */ 其中我加了2条测试数据,来测试id2不存在合并的情况...其实lz的意思我依旧不是很清楚。
to #17 & #18举一反三:--> 测试数据:#V if object_id('tempdb.dbo.#V') is not null drop table #V create table #V(ID1 varchar(8), ID2 varchar(8), ID3 varchar(8), URL varchar(8)) insert into #V select '001', '002', '003', 'url1' union all select '002', '002', '004', 'url2' union all select '003', '003', '005', 'url3' union all select '004', '004', '006', 'url4' union all select '005', '003', '007', 'url5' union all select '006', '003', '008', 'url6' --> 测试数据:#T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T(ID2 varchar(8), ID3 varchar(8), GroupID3 varchar(8)) insert into #T select '002', '003', '003' union all select '002', '004', '003' union all select '003', '005', '005' union all select '003', '007', '005' union all select '003', '008', '008'select v.* from #V v left join #T t on v.ID2=t.ID2 and v.ID3=t.ID3 where not exists (select 1 from #T where ID2=t.ID2 and GroupID3=t.GroupID3 and ID3<t.ID3)/* ID1 ID2 ID3 URL -------- -------- -------- -------- 001 002 003 url1 003 003 005 url3 004 004 006 url4 006 003 008 url6 */
to #21是的select v.* from #V v left join #T t on v.ID2=t.ID2 and v.ID3=t.ID3 where t.ID2 is not null and not exists (select 1 from #T where ID2=t.ID2 and GroupID3=t.GroupID3 and ID3<t.ID3) or not exists (select 1 from #V where ID2=v.ID2 and ID3<v.ID3) 先看看效率,如果觉得慢再想办法,分拆成两步查询
005,003,007,url5,5 咋没的?
select * from V where stepid in (1,4)
union
select * from V t where stepid in (3,5)
and ID1=(select min(ID1) from V where ID2=T.ID2)
stepid在(1,4)拆分
(3,5)合并?那就见#5
if object_id('tempdb.dbo.#v') is not null drop table #v
create table #v(ID1 varchar(8), ID2 varchar(8), ID3 varchar(8), URL varchar(8))
insert into #v
select '001', '002', '003', 'url1' union all
select '002', '002', '004', 'url2' union all
select '003', '003', '005', 'url3' union all
select '004', '004', '006', 'url4' union all
select '005', '003', '007', 'url5'
--> 测试数据:#t
if object_id('tempdb.dbo.#t') is not null drop table #t
create table #t(ID2 varchar(8))
insert into #t
select '002'select v.* from #v v left join #t t on v.ID2=t.ID2
where t.ID2 is not null or
not exists (select 1 from #v where ID2=v.ID2 and URL<v.URL)/*
ID1 ID2 ID3 URL
-------- -------- -------- --------
001 002 003 url1
002 002 004 url2
003 003 005 url3
004 004 006 url4
*/
这个正确的而且后面加WHERE和排序很好加,但是就是因为多了一个条件就是stepid在某些值的时候无视T表所以这句语句不对了。希望能在这上面修改
create table TestaA (id1 int,id2 int,id3 int,url varchar(5),stepid int)insert TestaA
select 1,2,3,'url1',1 union all
select 2,2,4,'url2',1 union all
select 3,3,5,'url3',3 union all
select 4,4,6,'url4',4 union all
select 5,3,7,'url5',5
*/--insert TestaA
--select 6,5,5,'url1',1 union all
--select 7,5,4,'url2',1 /*if object_id('TestaB') is not null drop table TestaB
create table TestaB (id int)insert TestaB
select 2 union all
select 3*/--存在
select * from TestaA where id1 in --3,5合
(select min(id1) from TestaA where stepid in (3,5) and TestaA.id2 in (select id from TestaB) group by id2 )
union all
select * from TestaA where stepid in (1,4) and TestaA.id2 in (select id from TestaB) --1,4分
union all
--不存在的合,无视stepid?
select * from TestaA where id1 in
(select min(id1) from TestaA where id2 not in (select id from TestaB) group by id2) order by id1
/*
1 2 3 url1 1
2 2 4 url2 1
3 3 5 url3 3
4 4 6 url4 4
6 5 5 url1 1
*/
其中我加了2条测试数据,来测试id2不存在合并的情况...其实lz的意思我依旧不是很清楚。
(1,4)看情况,ID2存在--分, ID2不存在,合?
新需求如下:
本人现在有一张视图和一张表结构如下
V ID1,ID2,ID3,URL
T ID2,ID3,GroupID3
V里面数据大致如下
001,002,003,url1
002,002,004,url2
003,003,005,url3
004,004,006,url4
005,003,007,url5
006,003,008,url6
T里面主要存放
002,003,003
002,004,003
003,005,005
003,007,005
003,008,008
现在的意思是,比如 V表中ID2字段相同的表示同一个项目,ID3表示同一个项目下的子项目
T表中的数据表示 只要ID2在T表中存在,那么V表中ID2的项目根据T表的GroupID3来拆分,而如果ID2在T表中不存在那么V表中的相同ID2要合并,也就是说原先的根据ID3拆分,现在是根据T表中的组织去拆分,比如T表中ID2为003的后面的005和007的Group都是005所以这两个合并而而008单独分开 比如现在 T表和V表的要的结果是。
001,002,003,url1(002的003和004合并)
003,003,005,url3(003的005和007合并)
004,004,006,url4
006,003,008,url6(003的008单独)
if object_id('tempdb.dbo.#V') is not null drop table #V
create table #V(ID1 varchar(8), ID2 varchar(8), ID3 varchar(8), URL varchar(8))
insert into #V
select '001', '002', '003', 'url1' union all
select '002', '002', '004', 'url2' union all
select '003', '003', '005', 'url3' union all
select '004', '004', '006', 'url4' union all
select '005', '003', '007', 'url5' union all
select '006', '003', '008', 'url6'
--> 测试数据:#T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T(ID2 varchar(8), ID3 varchar(8), GroupID3 varchar(8))
insert into #T
select '002', '003', '003' union all
select '002', '004', '003' union all
select '003', '005', '005' union all
select '003', '007', '005' union all
select '003', '008', '008'select v.* from #V v left join #T t on v.ID2=t.ID2 and v.ID3=t.ID3
where not exists (select 1 from #T where ID2=t.ID2 and GroupID3=t.GroupID3 and ID3<t.ID3)/*
ID1 ID2 ID3 URL
-------- -------- -------- --------
001 002 003 url1
003 003 005 url3
004 004 006 url4
006 003 008 url6
*/
where t.ID2 is not null and not exists (select 1 from #T where ID2=t.ID2 and GroupID3=t.GroupID3 and ID3<t.ID3)
or not exists (select 1 from #V where ID2=v.ID2 and ID3<v.ID3)
先看看效率,如果觉得慢再想办法,分拆成两步查询