查找成员与广告系列重叠的所有实例

如此多的交叉联接问题是由每个广告系列有一个列引起的，而使用一个广告系列列来标识每个广告系列会更有效。最近我看到这样的描述：你目前的表布局对人类有好处，但对数据库不利。

因此，当前的表格布局可以称为“透视”，我们需要做的是“取消透视”它，因此我们只有 2 列。一旦我们这样做了，回答谁在活动 X 和 Y 中的问题就会变得简单得多。例如：

CREATE TABLE mytable (
    Patient_AGN INT,    MCP INT,    P5E INT,    CMD INT,    KRG INT,
    EMP INT,    CAR INT,    SEG INT,    CON INT,    EMB INT,    HP INT
);

INSERT INTO mytable (Patient_AGN, MCP, P5E, CMD, KRG, EMP, CAR, SEG, CON, EMB, HP)
VALUES 
    (1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    (2, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0),
    (3, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0),
    (4, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0);

以下查询将数据“取消透视”为 2 列。我选择将其作为视图，但实际上，您最好将数据存储到此 2 列样式中。

CREATE VIEW myview AS
SELECT Patient_AGN, 'MCP' AS campaign FROM mytable WHERE MCP = 1
UNION ALL
SELECT Patient_AGN, 'P5E' AS campaign FROM mytable WHERE P5E = 1
UNION ALL
SELECT Patient_AGN, 'CMD' AS campaign FROM mytable WHERE CMD = 1
UNION ALL
SELECT Patient_AGN, 'KRG' AS campaign FROM mytable WHERE KRG = 1
UNION ALL
SELECT Patient_AGN, 'EMP' AS campaign FROM mytable WHERE EMP = 1
UNION ALL
SELECT Patient_AGN, 'CAR' AS campaign FROM mytable WHERE CAR = 1
UNION ALL
SELECT Patient_AGN, 'SEG' AS campaign FROM mytable WHERE SEG = 1
UNION ALL
SELECT Patient_AGN, 'CON' AS campaign FROM mytable WHERE CON = 1
UNION ALL
SELECT Patient_AGN, 'EMB' AS campaign FROM mytable WHERE EMB = 1
UNION ALL
SELECT Patient_AGN, 'HP' AS campaign FROM mytable WHERE HP = 1;


select 
       Patient_AGN
     , campaign
from myview
order by
       Patient_AGN
     , campaign

所以现在数据看起来像这样：

然后，我们可以使用相当简单的查询来查找参与 2 个特定活动的人（例如）：

select Patient_AGN
from myview
where campaign IN('CON','MCP')
group by Patient_AGN
having count(distinct campaign) = 2

小提琴