如何在第二个联接表中执行具有多个条件的联接? [英] How to do a join with multiple conditions in the second joined table?

问题描述

我有2张桌子.第一个表是客户列表.

I have 2 tables. The first table is a list of customers.

第二张表是这些客户在另一个字段中拥有的设备的列表，其中包含有关该客户的一些数据(客户问题).问题在于，对于每个客户而言，可能会有多个问题.

The second table is a list of equipment that those customers own with another field with some data on that customer (customer issue). The problem is that for each customer, there may be multiple issues.

我需要在这些表上进行联接，但只返回有两个问题的客户的结果.

I need to do a join on these tables but only return results of customers having two of these issues.

问题是，如果我使用OR进行加入，那么我得到的结果将包括只有这些问题之一的客户.

The trouble is, if I do a join with OR, I get results including customers with only one of these issues.

如果我执行AND，则不会得到任何结果，因为每一行仅包含一个条件.

If I do AND, I don't get any results because each row only includes one condition.

如何在T-SQL 2008中做到这一点?

How can I do this in T-SQL 2008?

推荐答案

除非我有误解，否则我认为您想要这样的东西(如果您只对有2个特定问题的客户感兴趣):

Unless I've misunderstood, I think you want something like this (if you're only interested in customers that have 2 specific issues):

SELECT c.*
FROM Customer c
    INNER JOIN CustomerEquipment e1 ON c.CustomerId = e1.CustomerId AND e1.Issue = 'Issue 1'
    INNER JOIN CustomerEquipment e2 ON c.CustomerId = e2.CustomerId AND e2.Issue = 'Issue 2'

或者，查找具有多个问题的客户，而不论其类型如何:

Or, to find any customers that have multiple issues regardless of type:

;WITH Issues AS
(
    SELECT CustomerId, COUNT(*)
    FROM CustomerEquipment
    GROUP BY CustomerId
    HAVING COUNT(*) > 1
)

SELECT c.*
FROM Customer c
    JOIN Issues i ON c.CustomerId = i.CustomerId

这篇关于如何在第二个联接表中执行具有多个条件的联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！