SQL 如果列 A 为空，则使用列 B 的值计算列 C [英] SQL if column A is null, then use column B's value to compute column C

问题描述

使用具有以下设计的 MS Access 数据库:

Using an MS Access database with the following design:

Tables
Equipment, Employees, CreditCard

Fields
Equipment: ID, PrimaryEmployee, SecondaryEmployee
Employees: ID, CreditCard
CreditCard: ID, Number, Pin

因此，一件设备可以有两个不同的员工分配给它.每个员工都可以拥有一张信用卡，也可以根本没有.

So, a piece of equipment can have two different employees assigned to it. Each employee can have a CreditCard assigned to them, or not at all.

基于一件设备的ID:

• 如果该设备设置了 SecondaryEmployeeID，并且对应的 Employee 有一个 CreditCard 集，然后返回的值那张信用卡.
• Else 如果设备设置了 PrimaryEmployeeID，并且对应的 Employee 有一个 CreditCard 集，然后返回的值那张信用卡.

我还必须在同一个查询中从员工表中提取员工姓名，因此，我之前使用了 LEFT JOIN，据我所知，这似乎无法做到这一点.

I also have to pull the employee's name from the Employees table in the same query, and thus, I was using a LEFT JOIN before, which seemed to make it impossible to do this in my knowledge.

已编辑

我当前的查询尝试:

SELECT
    Equipment.ID,
    Equipment.PrimaryEmployee,
    Equipment.SecondaryEmployee,
    Employees.CreditCard,
    CreditCard.Pin
FROM
    (Equipment
        LEFT JOIN Employees ON Equipment.PrimaryEmployee = Employees.ID
    )
LEFT JOIN CreditCard ON Employees.CreditCard = CreditCard.ID
WHERE (((Equipment.EquipmentType)=1))

我从查询中删除了一些不相关的字段，希望能提高可读性，并简化我遇到问题的区域.

I removed some unrelated fields from the query to hopefully improve readability, and simplify the area I'm having troubles.

目前正在生成如下结果:

This is currently generating a result like:

ID    PrimaryEmployee    SecondaryEmployee    CreditCard    Pin
--------------------------------------------------------------------
1     John Doe           Jack Smith           1234567890    1234
2     Bubba Smith        Ryan Howard          2345678901    2345

问题是它只匹配 PrimaryEmployee.我需要它首先检查 SecondaryEmployee，如果没有匹配项或信用卡集，则在 PrimaryEmployee 上进行匹配.如果都不匹配，则返回 Null 或-".

The problem is that it only matches on the PrimaryEmployee. I need it to check the SecondaryEmployee first, and if that doesn't have a match or credit card set, then match on the PrimaryEmployee. If neither match, then return Null or '-'.

如果仍需要其他信息，请告诉我.

Let me know if additional information is still required.

推荐答案

在不破坏 SQL 的情况下，最简单的方法是创建第二个查询，该查询使用第一个查询的结果并拉入 Employee 列您的第一个结果 IS NULL.

The easiest way to do this, without mucking up the SQL, is to create a second query that uses the results from your first query and pulls in the Employee column where your first result IS NULL.

获得两个查询后，您距离编写第三个查询仅一步之遥，该查询将两个查询的结果连接到一个输出中.

Once you've got your two queries, you're just a stone's throw from writing a third query that joins the results from both queries into a single output.

这篇关于SQL 如果列 A 为空，则使用列 B 的值计算列 C的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！