SQL Server SQL query to find all occurrence in table

mbzjlibv  于 2023-11-16  发布在  其他
关注(0)|答案(1)|浏览(98)

I need a query that can return all the occurrence of specific condition pairs in a table. for example, I have this table. I need to return all the "start to stop" leg where the startPointType is either L or D and the endPointType is not L or D.
| leg | StartPoint | StartPointType | EndPoint | EndPointType |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | xxx | L | aaa | D |
| 2 | aaa | L | eee | Z |
| 3 | eee | Z | ttt | C |
| 4 | ttt | C | uuu | D |
| 5 | uuu | L | ooo | L |
| 6 | ooo | L | hhh | C |
| 7 | hhh | C | kkk | B |
| 8 | kkk | B | ppp | C |
| 9 | ppp | C | fff | L |
| 10 | fff | L | www | L |

enter image description here

In the example above, it should return
| Start_Leg | StartPoint | End_Leg | EndPoint |
| ------------ | ------------ | ------------ | ------------ |
| 2 | aaa | 4 | uuu |
| 6 | ooo | 9 | fff |

I'm able to find the first pair, but can't figure how to get all the pairs.. Many thanks for your help.

I tried self join.

I'm expecting to get all the pairs.

fdbelqdn

fdbelqdn1#

Considering the desired output in the post I think the request should be:

  1. the start record has StartPointType in ('L','D') and EndPointType not in ('L','D')
  2. the stop record has StartPointType not in ('L','D') and EndPointType in ('L','D')

If I am not misunderstanding, the following query gives the desired result:

select top 1 with ties StartLeg = a.leg
  ,a.StartPoint
  ,EndLeg = b.leg
  ,b.EndPoint
from MyTable a
inner join MyTable b
on a.leg<b.leg
 and b.StartPointType not in ('L','D')
 and b.EndPointType in ('L','D')
where a.StartPointType in ('L','D')
 and a.EndPointType not in ('L','D')
order by row_number() over (partition by a.leg order by b.leg)

where MyTable is a table like the one in the post. I'm joining it with itself and where the alias is "a" I'm looking for start records and where is "b" I'm looking for stop records: this is the explanation why I wrote a.leg<b.leg in the join condition; the remaining part of the join condition is point (2) above, whereas where condition is point (1).

The reason why I wrote select top 1 with ties and that order by at the end is because I want to have only one record for each partition key, a leg in our case. In case of many records with the same a.leg I want to keep only the one with the smaller b.leg (that's why in the row_number() i orber by b.leg).

This solution could not work in sql server 2008, in that case I would use CTE (or temp tables as you prefer):

with tab as (select StartLeg = a.leg
  ,EndLeg = min(b.leg)
from MyTable a
inner join MyTable b
on a.leg<b.leg
 and b.StartPointType not in ('L','D')
 and b.EndPointType in ('L','D')
where a.StartPointType in ('L','D')
 and a.EndPointType not in ('L','D')
group by a.leg)

select t.StartLeg
  ,a.StartPoint
  ,t.EndLeg
  ,b.EndPoint
from tab t
inner join MyTable a
on t.Startleg=a.leg
inner join MyTable b
on t.Endleg=b.leg

相关问题