SQL 列合并和聚合函数

Question

我有一个简单的表，有两列（两列感兴趣，加上一个ID）...我有一个ajax搜索，它只是寻找关键字...然后将其发送到我真正的搜索...ajax搜索不关心它们是什么，但它们需要不同...

我怎样才能将两列合并在一起：

City, Country

Krakow, Poland
Warsaw, Poland
Austin, USA
New York, USA
Prague, Czech Republic

这样我就可以

Keyword, Sideinfo

Krakow, Poland
Warsaw, Poland
Austin, USA
Prague, Czech Republic
USA, Country (only once)
Poland, Country
Czech Republic, Country

尝试做一个 UNION 但我不确定如何做一个 WHERE LIKE 'keyword%'

希望这是有道理的......

Answer 1

尝试这个。

SELECT Keyword, SideInfo
FROM
(
    SELECT
        DISTINT City as Keyword, Country as SideInfo
    FROM Table

    UNION
    SELECT 
        DISTINCT Country, 'Country'
    FROM Table
) AS InnerQuery
Where Keyword LIKE '%blah%'

Answer 2

这应该可以做到：

select distinct country Keyword,'Country' SideInfo from Cities
union all
select City Keyword,Country SideInfo from Cities