选择至少有1个列值与特定行匹配的行

Question

我一直在尝试编写一个配置单元查询，该查询允许我选择至少有1个与单行（即）匹配的特定列值的所有行

---

City     |   Mon  |  Tue  |  Wed  |  Thu  |  Fri  |  Sat  |  Sun
-----------------------------------------------------------------
Houston  |  Rain  |  Fog  |  Sun  |  Fog  |  Rain |  Sun  |  Sun
Denver   |  Sun   |  Sun  |  Sun  |  Sun  |  Fog  |  Sun  |  Sun
Seattle  |  Rain  |  Sun  |  Sun  |  Rain |  Rain |  Sun  |  Rain
Boston   |  Snow  |  Sun  |  Sun  |  Snow |  Snow |  Fog  |  Rain
Dallas   |  Sun   |  Sun  |  Sun  |  Sun  |  Sun  |  Rain |  Sun
StLouis  |  Rain  |  Sun  |  Fog  |  Rain |  Sun  |  Sun  |  Sun

现在我想选择所有与"西雅图"同一天有"雨"的城市结果是，

City     |   Mon  |  Tue  |  Wed  |  Thu  |  Fri  |  Sat  |  Sun
-----------------------------------------------------------------
Houston  |  Rain  |  Fog  |  Sun  |  Fog  |  Rain |  Sun  |  Sun
Seattle  |  Rain  |  Sun  |  Sun  |  Rain |  Rain |  Sun  |  Rain
Boston   |  Snow  |  Sun  |  Sun  |  Snow |  Snow |  Fog  |  Rain
StLouis  |  Rain  |  Sun  |  Fog  |  Rain |  Sun  |  Sun  |  Sun

感谢您的帮助。感谢

Answer 1

如果将Seattle行连接到每一行，则可以逐列进行比较，以确定是否应在结果中保留每一行：

SELECT
    t.*
FROM
    table t
INNER JOIN
    (SELECT * FROM table WHERE City = 'Seattle') s
WHERE
    (s.Mon = 'Rain' AND t.Mon = 'Rain') OR
    (s.Tue = 'Rain' AND t.Tue = 'Rain') OR
    (s.Wed = 'Rain' AND t.Wed = 'Rain') OR
    (s.Thu = 'Rain' AND t.Thu = 'Rain') OR
    (s.Fri = 'Rain' AND t.Fri = 'Rain') OR
    (s.Sat = 'Rain' AND t.Sat = 'Rain') OR
    (s.Sun = 'Rain' AND t.Sun = 'Rain')
;