php - mysql 查询 - 不要在多个 (5) 列中显示重复项？

Question

关于此的讨论有很多，但我似乎无法找到可行的解决方案。这是我的查询:

 $sql = "SELECT DISTINCT `People`,`People2`,`People3`,`People4`,`People5`
 FROM `album1` 
 WHERE `People` != '' ORDER BY `People`";

我遇到了多个问题。首先，重复项仍在显示。另外，我不知道如何在多个列上执行 WHERE...

例如，表格看起来像这样。

 People | People2  | People3 | People4 | People5  
--------+----------+---------+---------+--------
 Alex   |   Frank  |         |         | John      
 Alex   |          | John    | Frank   |

它们主要用于“标记”照片。由于可以在 5 个人员输入字段中的任何一个中输入一个人的姓名，因此姓名会到处都是。

我所做的只是尝试让名称出现一次。 People != '' 不显示空单元格。

有什么想法吗？如果我需要包含更多信息，请告诉我。

Answer 1

您应该对此进行反规范化，这样您就只有一个 People 列。您可能应该将其称为 Person 以强调其独特性。

但是，如果由于某种原因您不能在您的数据模型中这样做，那么您可以这样做。它创建了一个虚拟的单个 Person 列。

SELECT DISTINCT People as Person, Place, Year, Filename, Orientation
FROM (
  SELECT People AS People, Place, Year, Filename, Orientation
    FROM ALBUM1
   WHERE People IS NOT NULL and People <> ''
  UNION ALL
  SELECT People1 AS People, Place, Year, Filename, Orientation
    FROM ALBUM1
   WHERE People1 IS NOT NULL and People1 <> ''
  UNION ALL
  SELECT People2 AS People, Place, Year, Filename, Orientation
    FROM ALBUM1
   WHERE People2 IS NOT NULL and People2 <> ''
 /* etc */
)a
ORDER BY People, Place, Year, Filename, Orientation