我可以让SQL查询在每一行上做两个匹配吗？

I have an unusual SQL table (not mine) which has the following fields (among others): last_name, primary_name, secondary_name, denoting married couples. The last name is assumed to be shared (not very modern, I know), and if it's not a couple, then either the primary_name or secondary_name may be NULL. (The table also has several duplicates.)

我有一个不寻常的SQL表(不是我的),其中包含以下字段(其中包括):last_name,primary_name,secondary_name,表示已婚夫妇。假设姓氏是共享的(不是很现代,我知道),如果它不是一对,那么primary_name或secondary_name可能是NULL。 (该表也有几个重复。)

What I want to do is get a list of all names ("first last") in the database, alphabetized in the usual manner. Right now I'm doing two passes through the database using PHP and PDO:

我想要做的是获取数据库中所有名称(“最后一个”)的列表,以通常的方式按字母顺序排列。现在我正在使用PHP和PDO对数据库进行两次传递:

$qstr = "SELECT DISTINCT primary_name, last_name 
            FROM members 
            WHERE primary_name IS NOT null
            ORDER BY last_name, primary_name";
$sth = $dbh->prepare($qstr);
$sth->execute();
// output the results

$qstr = "SELECT DISTINCT secondary_name, last_name 
            FROM members 
            WHERE secondary_name IS NOT null
            ORDER BY last_name, secondary_name";
$sth = $dbh->prepare($qstr);
$sth->execute();
// output the new results

But the end result isn't alphabetized because the second pass starts over again.

但最终结果不是按字母顺序排列的,因为第二遍再次开始。

How can I get all the names at once, alphabetized completely? Is there a way to do this in SQL, or do I need to build two arrays and re-alphabetize them in PHP afterwards?

如何一次性获取所有名称,完全按字母顺序排列?有没有办法在SQL中执行此操作,或者我是否需要构建两个数组并在之后使用PHP重新按字母顺序排列它们?

EDIT The database looks something like this:

编辑数据库看起来像这样:

last_name  primary_name   secondary_name
----------------------------------------
Abrams     Joe            Susan
Miller     Sam            Abby

The desired output would be something like this:

期望的输出将是这样的:

["Joe Abrams","Susan Abrams","Abby Miller","Sam Miller"]

Instead, if the first pass gets all the husbands and the second pass all the wives, I'm getting something like this:

相反,如果第一遍得到所有的丈夫,第二遍通过所有的妻子,我得到这样的东西:

["Joe Abrams","Sam Miller","Susan Abrams","Abby Miller"]

3 个解决方案

#1

An alternative is to use UNION...

另一种方法是使用UNION ......

SELECT
  *
FROM
(
  SELECT primary_name AS pri_sec_name, last_name 
    FROM members 
   WHERE primary_name IS NOT null

  UNION

  SELECT secondary_name AS pri_sec_name, last_name 
    FROM members 
   WHERE secondary_name IS NOT null
)
  AS data
ORDER BY
  last_name, pri_sec_name

NOTE: UNION (as opposed to UNION ALL) will de-duplicate the results.

注意:UNION(与UNION ALL相反)将重复删除结果。

Another is to do a join on a mapping table.

另一种方法是在映射表上进行连接。

SELECT
  members.last_name,
  CASE WHEN map.mode = 1 THEN members.primary_name ELSE members.secondary_name END AS pri_sec_name
FROM
  members
INNER JOIN
  (SELECT 1 as mode UNION ALL SELECT 2 as mode) AS map
    ON (map.mode = 1 AND members.primary_name   IS NOT NULL)
    OR (map.mode = 2 AND members.secondary_name IS NOT NULL)
ORDER BY
  1,
  2

#2

If I understand correctly, I think you are looking for something like this:

如果我理解正确,我认为你正在寻找这样的东西:

select distinct coalesce(primary_name, secondary_name) as pri_sec_name,
    last_name
from members
where coalesce(primary_name, secondary_name) is not null
order by last_name,
    coalesce(primary_name, secondary_name)

Update

It sounds like in some cases you have one row for a last_name, where both primary_name and secondary_name are populated. The query below should give you the output you want (sorry, no COALESCE this time):

听起来在某些情况下,last_name有一行,其中primary_name和secondary_name都已填充。下面的查询应该为您提供所需的输出(抱歉,这次没有COALESCE):

select last_name, pri_sec_name
from (
    select primary_name as pri_sec_name, last_name from members where primary_name is not null
    union all
    select secondary_name as pri_sec_name, last_name from members where secondary_name is not null
) a 
order by last_name, pri_sec_name

#3

I think you need:

我认为你需要:

SELECT primary_name AS name
     , last_name 
  FROM members 
  WHERE primary_name IS NOT NULL
UNION 
SELECT secondary_name
     , last_name 
  FROM members 
  WHERE secondary_name IS NOT NULL
ORDER BY last_name, name

Another rewrite is using UNION ALL:

另一个重写是使用UNION ALL:

SELECT COALESCE(primary_name, secondary_name) AS name
     , last_name 
  FROM members 
UNION ALL
SELECT secondary_name
     , last_name 
  FROM members 
  WHERE primary_name IS NOT NULL
    AND secondary_name IS NOT NULL
ORDER BY last_name, name

The 2nd version may be faster but it may show duplicate results. If more example, you have these rows, one Joe Jackson married to Susan and one married to Lea:

第二个版本可能会更快,但它可能会显示重复的结果。如果更多的例子,你有这些行,一个Joe Jackson与Susan结婚,一个与Lea结婚:

last_name  primary_name   secondary_name
----------------------------------------
Jackson    Joe            Susan
Jackson    Joe            Lea

The first query qould show:

第一个查询可以显示:

name   last_name
-----------------
Joe    Jackson 
Lea    Jackson 
Susan  Jackson

while the second would have "duplicates":

而第二个会有“重复”:

name   last_name
-----------------
Joe    Jackson 
Joe    Jackson 
Lea    Jackson 
Susan  Jackson

Which is more proper, depends on your specs.

哪个更合适,取决于您的规格。

#1