34,590
社区成员
发帖
与我相关
我的任务
分享
select 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)
group by LineID
drop table #temp
create table #temp(LineID int, Seqno int, RegID int)
insert into #temp
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
LineID 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)>1
LineID
-----------
743
(1 行受影响)
[code=SQL]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
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]declare @t table ([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
(
select [LineID],B_RegID=min([RegID]),E_RegID=max([RegID])
from @t
group by [LineID]
) A
where B_RegID=101 and E_RegID=102
LineID
-----------
743
(1 行受影响)
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)
-- =========================================
-- -----------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 tb
group by LINEID
having min(RegID)=101 and MAX(RegID)<=102--因为REGID是INT 类型 ,所以 限定 最小为101 最大为 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 tb
group by LINEID
having min(RegID)=101 and MAX(RegID)<=102
/*------------
743
-------*/
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)
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
SELECT LineID FROM TB WHERE RegID IN (101 ,102 ) GROUP BY LINEID HAVING COUNT(LINEID)>=2
LineID
-----------
743
(所影响的行数为 1 行)
SELECT LineID FROM TB WHERE RegID IN (101 ,102 ) GROUP BY LINEID HAVING COUNT(LINEID)>=2
-- =========================================
-- -----------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
-------*/
if object_id('[tb]') is not null drop table [tb]
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 行受影响)
*/
select * from 表 where RegID=101 and SeqNo=1 union all
select * from 表 where RegID=102 and SeqNo=99