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

Answer 2

首先，" distinct“只适用于整个结果行，所以当您选择3列时，它们将只是distinct组合，例如结果行"A，B，C，and "A，A，B”是distinct，但另一个"A，B，C“将被忽略。

所以，假设你想要生成你的相册中所有人的列表(如果我错了，请纠正我)，你需要把所有的列塞在一起才能做出不同的工作，这让你想到了UNION：

select people from album where...
UNION DISTINCT 
select people2 from album where...
UNION DISTINCT 
select people3 from album where...

(您实际上不需要在联合子句中声明DISTINCT，因为这是默认行为)

这将所有的"people“列连接到一个单独的列表，DISTINCT可以对其进行操作。

顺便说一句，其他评论者和答案都是对的，你应该对此进行规范化。

Answer 3

试着这样做(它将删除空白列)，但是，我非常同意Jon C，这是一个针对不正确数据库设计的变通……

select id,max(people) as People,max(people2) as Pep2,
          max(people3) as Pep3,max(people4) as Pep4,max(people5) as Pep5
from
(
select id,people,'' as People2,'' as People3,'' as People4,'' as People5
from dbo.album
where people <> ''
union
select id,'',people2,'','',''
from dbo.album
where people2 <> ''
union
select id,'','',people3,'',''
from dbo.album
where people3 <> ''
union
select id,'','','',people4,''
from dbo.album
where people4 <> ''
union
select id,'','','','',people5
from dbo.album
where people5 <> ''
) xx
group by id