SQL Server SQL query to find all occurrence in table

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

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:

  1. select top 1 with ties StartLeg = a.leg
  2. ,a.StartPoint
  3. ,EndLeg = b.leg
  4. ,b.EndPoint
  5. from MyTable a
  6. inner join MyTable b
  7. on a.leg<b.leg
  8. and b.StartPointType not in ('L','D')
  9. and b.EndPointType in ('L','D')
  10. where a.StartPointType in ('L','D')
  11. and a.EndPointType not in ('L','D')
  12. 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):

  1. with tab as (select StartLeg = a.leg
  2. ,EndLeg = min(b.leg)
  3. from MyTable a
  4. inner join MyTable b
  5. on a.leg<b.leg
  6. and b.StartPointType not in ('L','D')
  7. and b.EndPointType in ('L','D')
  8. where a.StartPointType in ('L','D')
  9. and a.EndPointType not in ('L','D')
  10. group by a.leg)
  11. select t.StartLeg
  12. ,a.StartPoint
  13. ,t.EndLeg
  14. ,b.EndPoint
  15. from tab t
  16. inner join MyTable a
  17. on t.Startleg=a.leg
  18. inner join MyTable b
  19. on t.Endleg=b.leg
展开查看全部

相关问题