我想将两列合并为一个新列，但前提是其中一列不是'；t为空，然后按新列排序.(MySQL） - I want to combine two columns into a new column, but only if one isn't blank, and then sort by that new column. (MySQL)

I want to combine two columns into a new column, but only if one isn't blank, and then sort by that new column. (MySQL)

假设我有两列，city和sitename。

City        Sitename
Boston      Hynes Convention Center 
Baltimore   The Sheraton 
NYC         Times Square
            Applebees 
Tuoson       
Beijing     
            My Backyard

我特意将任意一列中的一些字段留空，原因如下。当我使用PHP输出这些信息时，我希望它以{city}-{sitename}的形式出现，但前提是它们都不是空的。如果其中任何一个是空的，那么我只想让它显示{city}或{state}，无论是哪一个。然后，一旦我有了这个由组合(或不组合(值组成的新列，我就想按这个新列A-Z对我的数据进行排序。所以最后它应该显示这样的内容。。。

City         Sitename                  Combined
             Applebees                 Applebees
Baltimore    The Sheraton              Baltimore - The Sheraton
Beijing                                Beijing
Boston       Hynes Convention Center   Baltimore - Hynes Convention Center
             My Backyard               My Backyard
NYC          Times Square              NYC - Times Square
Tuoson                                 Tuoson

我以前组合和重命名过列，也做过排序，但尝试添加条件和事例让我挠头。

哦，这不是表更改，这是我想要检索数据的方式。所以这个声明应该是。。。

"SELECT*FROM table…">

这是未经测试的，我很抱歉。

Select Trim(BOTH ' - ' From Concat(Coalesce(city, ''), ' - ', Coalesce(sitename, ''))) As citysite
From my_table
Order By citysite

我相信MySQL可以让您通过别名列进行排序。如果不是这样，那么只需将Trim((内容复制到Order By中即可。

SELECT 
   COALESCE(City,'') as City, 
   COALESCE(Sitename,'') AS Sitename, 
   CASE 
     WHEN COALESCE(City,'')='' THEN Sitename
     WHEN COALESCE(City,'')<>'' AND COALESCE(Sitename,'')='' THEN City
     ELSE City + ' - ' + Sitename 
   END AS Combined
FROM table
ORDER BY 
 CASE 
  WHEN COALESCE(City,'')='' THEN Sitename
  WHEN COALESCE(City,'')<>'' AND COALESCE(Sitename,'')='' THEN City
  ELSE City + ' - ' + Sitename 
 END

它应该是非常直接的。

SELECT case when city is null then
           sitename
       else
           city || ' - ' || sitename
       end 
FROM table

select  City + ' ' + Sitename   
where City ....
  and Sitename ...
order by City + ' ' + Sitename