我有一张这样的 table :

maiL_client   platform   rendering_engine   category

Android         mobile     Webkit           read
Android         mobile     Webkit           deleted
Apple iPhone    mobile     Webkit           skimmed
Other           Other      desktop          read
Other           Other      desktop          deleted
Android         Other      mobile           skimmed

现在我需要的是获取所有这些列的不同值。是否有可能在一个查询中得到它?如果我使用简单的 SELECT DISTINCT column_names 那么它会给出重复的值。我在 CodeIgniter 事件记录模式中使用它。

最佳答案

SELECT DISTINCT 'mail_client' AS `key`,mail_client as `value` FROM table
UNION
SELECT DISTINCT 'platform', platform FROM table
UNION
SELECT DISTINCT'rendering_engine', rendering_engine FROM table
UNION
SELECT DISTINCT'category', category FROM table

或尝试以下操作,然后拆分或分解结果,因为它会返回一个逗号分隔的列表
SELECT GROUP_CONCAT(DISTINCT Mail_Client)as Mail_client,
       GROUP_CONCAT(DISTINCT platform) as platform,
       GROUP_CONCAT(DISTINCT rendering_engine)as rendering_engine,
       GROUP_CONCAT(DISTINCT category)as category
FROM Table

sqlFiddle Demo

结果
MAIL_CLIENT                 PLATFORM      RENDERING_ENGINE      CATEGORY
-----------                 ---------     ----------------      --------
Android,Apple iPhone,Other  mobile,Other  Webkit,desktop,mobile read,deleted,skimmed

关于mysql - 4 列的 DISTINCT 值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/20560818/

10-15 21:17