我现在有一个这样的表
LineID SeqNo RegID
---------------------------
743 1 101
743 99 102
744 1 101
744 2 404
744 99 501其中lineID表示线路ID.比如743这条线路表示的是从1->99的路线,启始101,到102.
744这条线路表示的是从1->99的路线,启始101,到404,到501请问有没有办法用一条sql语句取出,启始101,到102的这条线路呢?高手求教!!!!!
LineID SeqNo RegID
---------------------------
743 1 101
743 99 102
744 1 101
744 2 404
744 99 501其中lineID表示线路ID.比如743这条线路表示的是从1->99的路线,启始101,到102.
744这条线路表示的是从1->99的路线,启始101,到404,到501请问有没有办法用一条sql语句取出,启始101,到102的这条线路呢?高手求教!!!!!
where regID>100 and regid<103还有不理解你什么意思!
---------------------------
743 1 101
743 99 102
744 1 101
744 2 404
744 99 501 我意思很简单,请你取出743这个ID
条件是你只知道: 当RegID=101的时候SeqNo=1,当RegID=102的时候SeqNo=99
select * from 表 where RegID=102 and SeqNo=99
go
create table [tb]([LineID] int,[SeqNo] int,[RegID] int)
insert [tb]
select 743,1,101 union all
select 743,99,102 union all
select 744,1,101 union all
select 744,2,404 union all
select 744,99,501
go
--select * from [tb]select distinct LineID
from tb t
where exists(select 1 from tb where LineID=t.LineID and SeqNo=1 and RegID=101)
and exists(select 1 from tb where LineID=t.LineID and SeqNo=99 and RegID=102)
/*
LineID
-----------
743(1 行受影响)
*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(lineID int,seqNO int ,RegID int)
go
insert into tb
select 743,1,101 union all
select 743,99,102 union all
select 744,1,101 union all
select 744,2,404 union all
select 744,99,501
go
alter function poof(@n int)
returns varchar(100)
as
begin
declare @s varchar(100)
set @s=''
select @s=@s+'-'+convert(varchar(100),SEQNO)
from tb
where lineID =@n
return stuff(@s,1,1,'')
end
go
select LINEID,dbo.poof(LINEID)as 路线
from tb
group by LINEID
having MAX(RegID)<=102
/*------------
743 1-99
-------*/
insert [tb]
select 743,1,101 union all
select 743,99,102 union all
select 744,1,101 union all
select 744,2,404 union all
select 744,99,501SELECT LineID FROM TB WHERE RegID IN (101 ,102 ) GROUP BY LINEID HAVING COUNT(LINEID)>=2LineID
-----------
743(所影响的行数为 1 行)
where exists(select 1 from tb where LineID=t.LineID and SeqNo=1 and RegID=101)
and exists(select 1 from tb where LineID=t.LineID and SeqNo=99 and RegID=102)
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(lineID int,seqNO int ,RegID int)
go
insert into tb
select 743,1,101 union all
select 743,99,102 union all
select 744,1,101 union all
select 744,2,404 union all
select 744,99,501
go
select LINEID
from tbgroup by LINEID
having min(RegID)=101 and MAX(RegID)<=102
/*------------
743
-------*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(lineID int,seqNO int ,RegID int)
go
insert into tb
select 743,1,101 union all
select 743,99,102 union all
select 744,1,101 union all
select 744,2,404 union all
select 744,99,501
go
select LINEID
from tbgroup by LINEID
having min(RegID)=101 and MAX(RegID)<=102--因为REGID是INT 类型 ,所以 限定 最小为101 最大为 102 路线就确定了
/*------------
743
-------*/
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([LineID] int,[SeqNo] int,[RegID] int)
Insert #T
select 743,1,101 union all
select 743,99,102 union all
select 744,1,101 union all
select 744,2,404 union all
select 744,99,501
SELECT LineID from #T
where RegId=101 AND SeqNo=1 and LineID in(
SELECT LineID from #T
where RegId=102 AND SeqNo=99)
insert @t
select 743,1,101 union all
select 743,99,102 union all
select 744,1,101 union all
select 744,2,404 union all
select 744,99,501select [LineID]
from
(
select [LineID],B_RegID=min([RegID]),E_RegID=max([RegID])
from @t
group by [LineID]
) A
where B_RegID=101 and E_RegID=102LineID
-----------
743
(1 行受影响)
insert [tb]
select 743,1,101 union all
select 743,99,102 union all
select 744,1,101 union all
select 744,2,404 union all
select 744,99,501
select
distinct LineID
from tb t
where exists(select 1 from tb where LineID=t.LineID and RegID=101 and exists(select 1 from tb where LineID=t.LineID and RegID=102))SELECT LineID FROM TB WHERE RegID IN (101 ,102 ) GROUP BY LINEID HAVING COUNT(LINEID)>=2--DROP TABLE TB[/code]
select 743, 1, 101 union all
select 743, 99, 102 union all
select 744, 1, 101 union all
select 744, 2, 404 union all
select 744, 99, 501LineID Seqno RegID
----------- ----------- -----------
743 1 101
743 99 102
744 1 101
744 2 404
744 99 501(5 行受影响)---select LineID from
(
select LineID,RegID from #temp
where RegID in (101,102)) as a
group by LineID having count(lineID)>1LineID
-----------
743(1 行受影响)
where exists(select 1 from tb where LineID=t.LineID and SeqNo=1 and RegID=101)
and exists(select 1 from tb where LineID=t.LineID and SeqNo=99 and RegID=102)
group by LineID