如果值存在，则从其他表中选择 [英] Select from other table if value exist

问题描述

我为此创建了一个小提琴，在这个链接:http://www.sqlfiddle.com/#!2/7e007

I created a fiddle for this, at this link: http://www.sqlfiddle.com/#!2/7e007

我找不到 SQL compact/CE，所以它在 MySQL 中.

I could'nt find SQL compact / CE so it's in MySQL.

表格看起来像这样

Records                      Clients
ID | NAME    | AGE           ID | NAME    
------------------           ----------------
1  | John    | 20            1  | John
2  | Steven  | 30            2  | Daniel
3  | Abraham | 30            3  |
4  | Donald  | 25            5  | Lisa 
6  |         | 35            6  | Michael
7  |         | 42            7  |

我想从两个表中进行选择，如果 id 在两个表中并且都有名称，我希望将Clients"中的名称作为默认名称.如果 Records 中的名称为空，则使用客户名称(如果有)并且 Clients.Name 为空；使用记录名称.

I would like to select from both tables, and if the id is in both tables and both have names I would like the the name from "Clients" as the default. If the name in Records is blank, use the client name (if any) and if the Clients.Name is blank; use the records.Name.

从上面的表格中，我想这样:

From tables above, i would like this:

ID | NAME    | AGE 
------------------
1  | John    | 20
2  | Daniel  | 30
3  | Abraham | 30
4  | Donald  | 25
5  | Lisa    |
6  | Michael | 35
7  |         | 42

如何在 SQL Compact 中执行此操作?

How do i do this in SQL Compact?

多亏了下面的好答案，我设法提出了这个几乎有效的查询:

Thanks to great answers below i've managed to come up with this query, which ALMOST works:

SELECT t.id, t.name, t.age FROM 
(
    (
        SELECT r.id,
        CASE WHEN r.name = NULL THEN c.name ELSE r.name END  as name,
        r.age 
        FROM Records r 
        LEFT JOIN Clients c ON c.id = r.id
    )
    UNION 
    (
        SELECT c.id, c.name, null as age FROM Clients c where c.id NOT IN (select id from Records)
    )
) as t ORDER BY t.id 

这给了我这个输出:

ID | NAME    | AGE 
------------------
1  | John    | 20
2  | Daniel  | 30
3  | Abraham | 30
4  | Donald  | 25
5  | Lisa    |
6  |         | 35
7  |         | 42

在这种情况下缺少迈克尔"(应该在 #6 上).为什么?！

"Michael" (should be on #6) is missing in this case. Why?!

推荐答案

select r.id,
     IF(c.name != '',c.name,r.name) as name,
     r.age 
 FROM Records r 
 LEFT JOIN Clients c ON c.id = r.id 
 GROUP BY c.id

使用上面的查询.

已

  SELECT t.id, t.name, t.age FROM 
(
    (
        SELECT r.id,
        CASE WHEN c.name <> '' THEN c.name ELSE r.name END  as name,
        r.age 
        FROM Records r 
        LEFT JOIN Clients c ON c.id = r.id
    )
    UNION 
    (
        SELECT c.id, c.name, null as age FROM Clients c where c.id NOT IN (select id from Records)
    )
) as t ORDER BY t.id 

使用此查询.

这篇关于如果值存在，则从其他表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！