SQL列合并和聚合函数

时间：2022-04-05 22:38:33

I have a simple table with two columns (well two columns of interest, plus just an ID)... I have an ajax search which is just looking for keywords... which are then sent to my real search... the ajax search doesn't care what they are, but they need to be distinct...

我有一个简单的表,有两列(两列感兴趣,只有一个ID)...我有一个ajax搜索,只是寻找关键字...然后发送到我的真实搜索... ajax搜索并不关心它们是什么,但它们需要区别对待......

How can I merge the two columns together:

如何将两列合并在一起:

City, Country

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

So that I would get

所以我会得到

Keyword, Sideinfo

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

I tried doing a UNION but I'm not sure how i'd do a WHERE LIKE 'keyword%'

我尝试做一个UNION,但我不知道我怎么做一个WHERE LIKE'关键字%'

Hope that makes sense...

希望有道理......

2 个解决方案

#1

Try this.

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%'

#2

This should do it:

这应该这样做:

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

#1

Try this.

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%'

#2

This should do it:

这应该这样做:

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

相关文章