MySQL union - 如何在所有行中显示表 1 的内容? [英] MySQL union - how do I show table 1 content in all rows?

问题描述

在下面的 mysql 查询中，我想知道是否可以从表 aitisi 中获取行以代替表成员的空白值显示.问题是，我需要创建一个联合，但还要在所有行中显示第一个表的数据.

In the following mysql query I'd like to know if it's possible to get the rows from table aitisi to display in place of the blank values from table members. Thing is, I need to create a union but also display data of the first table in all rows.

select a1.id, a1.name, a1.surname, a1.program, a1.date
from aitisi a1 
union select a.id, m.name, m.surname, null, null from members m 
join aitisi a on (a.id = m.symbid)

我使用 null 作为虚拟字段，因此我可以执行具有相同列数的联合.但是这样我只能在表 aitisi 的行中获取日期和程序，其余的都是空白的.

I used null as dummy fields so I could perform a union with equal number of columns. But this way I'm getting date and program only in the rows from table aitisi and the rest are blank.

推荐答案

试试这个(效率不高):

Try this (not very efficient):

SELECT u.id, u.name, u.surname,
       coalesce(u.program, aa.program),
       coalesce(u.date, aa.date)
  FROM (SELECT a1.id, a1.name, a1.surname,
               a1.program, a1.date
          FROM aitisi a1 
         UNION
        SELECT a2.id, m.name, m.surname, NULL, NULL
          FROM members m
          JOIN aitisi a2 ON a2.id = m.symbid) u
  JOIN aitisi aa ON aa.id = u.id;

更多地查看您的初始查询，此变体有什么问题:

Looking more on your initial query, what would be wrong with this variant:

SELECT a1.id, a1.name, a1.surname, a1.program, a1.date
  FROM aitisi a1 
 UNION
SELECT a2.id, m.name, m.surname, a2.program, a2.date
  FROM members m 
  JOIN aitisi a2 ON a2.id = m.symbid;

这篇关于MySQL union - 如何在所有行中显示表 1 的内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！