SQL查询以选择我的第一列常见的记录 [英] SQL query to select records which is common for my first column

问题描述

SEG_AIRLINE	                SEG_ORIGIN_CODE  	SEG_DESTINATION_CODE
        G9				ALA                             DEL
        G9				ALA				DAC
        G9				BKK				OOL
        SG				IXA				MAA
        SG				BKK				OOL		
        SG				IXA				GAU
        6E				IXA				AMD
        6E				BKK				OOL
        6E				IXA				DEL
        AK				BKK				MEL
        AK				BKK				OOL
        AK				BKK				PER

我有一张如上所示的表格。现在我想选择SEG_ORIGIN_CODE和SEG_DESTINATION_CODE

，这对所有SEG_AIRLINE来说都是通用的。

我希望输出像

I have a table as like above. Now i want to select SEG_ORIGIN_CODE AND SEG_DESTINATION_CODE
which is common to all SEG_AIRLINE.
I want output like

SEG_AIRLINE	SEG_ORIGIN_CODE	             SEG_DESTINATION_CODE
G9				BKK     		        OOL        
SG				BKK				OOL        
6E				BKK				OOL        
AK				BKK				OOL

推荐答案

快速执行此操作的方法是使用子查询。

The quick way to do this, is with subqueries.

select seg_airline, seg_origin_code, seg_destination_code from seg
where
(Select count(distinct seg_airline) from seg s where s.seg_origin_code = seg.seg_origin_code) = 4
and
(Select count(distinct seg_airline) from seg s where s.seg_destination_code = seg.seg_destination_code) = 4

无论添加多少航空公司，都可以继续工作，你可以这样做;

To make it continue to work no matter how many airlines get added, you can do this;

declare @airlineCount int
select @airlineCount = count(distinct seg_airline) from seg

select seg_airline, seg_origin_code, seg_destination_code from seg
where
(Select count(distinct seg_airline) from seg s where s.seg_origin_code = seg.seg_origin_code) = @airlineCount
and
(Select count(distinct seg_airline) from seg s where s.seg_destination_code = seg.seg_destination_code) = @airlineCount

如果您不希望所有航空公司拥有相同的出发地和目的地，只有多于一个同样，你可以摆脱子查询并执行此操作：

If you didn't want it where ALL the airlines had the same origin and destination, only where more than one had the same, you could get rid of the subqueries and do this:

select distinct s1.seg_airline, s1.seg_origin_code, s1.seg_destination_code from seg s1
inner join seg s2 on s1.seg_origin_code = s2.seg_origin_code and s1.seg_destination_code = s2.seg_destination_code and s1.seg_airline <> s2.seg_airline

您需要distinct，因为根据定义，这会为每个匹配创建一对行。

You need the distinct, because, by definition, this creates a pair of rows for every match.

SELECT A1.SEG_AIRLINE, A1.SEG_ORIGIN_CODE, A1.SEG_DESTINATION_CODE FROM AIRLINE A1
WHERE
(SELECT COUNT(A2.SEG_ORIGIN_CODE+A2.SEG_DESTINATION_CODE) FROM AIRLINE A2
WHERE A1.SEG_ORIGIN_CODE = A2.SEG_ORIGIN_CODE AND A1.SEG_DESTINATION_CODE = A2.SEG_DESTINATION_CODE
GROUP BY A2.SEG_ORIGIN_CODE+A2.SEG_DESTINATION_CODE) > 1

这篇关于SQL查询以选择我的第一列常见的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！