SQL从2个具有相同列名的表中进行选择仅在不为null时返回列 [英] SQL Select from 2 tables with same column names only return column when not null

问题描述

我想知道有人可以帮我这个忙吗?

I wonder if someone can give me a hand with this...

我需要查询两个表，其中一个表包含默认数据，第二个表包含任何替代数据，例如...

I need to query two tables where one table contains default data and the second table contains any override data, such as...

id = 5  
title = 'This is the default title'  
text = 'Hi, default text here...'  

表2

id = 1  
relation_id = 5
title = 'This is an override title'  
text = NULL

我需要返回一组完整的行，因此如果table2文本为空，那么我的结果集将包含table1文本.同样，如果我的table2标题不为空，那么我的结果标题将是table2标题的值，因此将覆盖默认的table1文本值.

I need to return a full set of rows so if table2 text is empty then my result set would contain the table1 text. Likewise if my table2 title does is not empty then my result title would be the value of the table2 title thus overriding the default table1 text value.

根据上面的给定表结构

id = 5
title = 'This is an override title'
text = 'Hi, default text here...'

我尝试过仅使用标准联接从两个表中获取所有数据，然后使用PHP整理数据，但我真的很想在SQL中使用它.

I have tried just using a standard join to get all data from both tables and then arranging the data with PHP but I really would like to do it in the SQL if possible.

我正在运行的查询的大致示例是...

SELECT vt.id, 
  vt.title as vt_title,
  vt.text AS vt_text,
  vt.relation_id,
  t.id, t.title,
  t.text 
  FROM table1 vt 
  LEFT JOIN table2 t ON vt.relation_id = $id 
  AND vt.relation_id = t.id",

我的表最多可以有6个6列，这些列具有相同的列名/覆盖数据. 我想尽可能保留默认字段名称不变，并避免在返回集中分配新名称，例如

My tables could have up to 6 six columns with the same column names / overriding data. I'd like to keep the default field names intact where possible and avoid assigning new names in the return set, for example

id = 1
title = 'default title'
override_title = 'this is the override title'
text = 'Hi, default text here...'

推荐答案

SELECT  a.ID,
        COALESCE(b.Title, a.Title) Title,
        COALESCE(b.Text, a.Text) Text
FROM    Table1 a
        LEFT JOIN Table2 b
            ON a.ID = b.relation_ID

• SQLFiddle演示
• SQLFiddle Demo

输出

╔════╦═══════════════════════════╦═══════════════════════╗
║ ID ║           TITLE           ║         TEXT          ║
╠════╬═══════════════════════════╬═══════════════════════╣
║  5 ║ This is an override title ║ Hi. default text here ║
╚════╩═══════════════════════════╩═══════════════════════╝

这篇关于SQL从2个具有相同列名的表中进行选择仅在不为null时返回列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！