sql-server - TSQL:选择共享单个列值的行集

Question

我正在运行 MSSQL Server 2016 Express。我想选择所有具有特定 articlegroup_id 值的行，并选择具有匹配 articleid 值的行。

因此，使用下面的数据，假设我想获取值为 684 的 articlegroup_id 的结果集。只有 articleid 的 3107196 和 3100000 与此匹配。所以结果集是:

3100000 129
3100000 144
3100000 684
3107196 129
3107196 144
3107196 684

同样，如果我在 articlegroup_id 上运行相同的查询，我想得到值为 112:

2106543 110
2106543 112
2106999 110
2106999 112

DDL

CREATE TABLE [dbo].[products_category_mapping](
    [articleid] [int] NOT NULL,
    [articlegroup_id] [int] NOT NULL
) ON [PRIMARY]

INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (3100000,129)
INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (3100000,144)
INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (3100000,684)

INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (3107196,129)
INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (3107196,144)
INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (3107196,684)

INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (3107100,129)
INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (3107100,144)

INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (2106543,110)
INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (2106543,112)

INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (2107000,110)

INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (2106999,110)
INSERT INTO products_category_mapping(articleid,articlegroup_id) VALUES (2106999,112)

Answer 1

另一种方法是使用 JOIN

Select A.*
 From  [products_category_mapping] A
 Join (
        Select articleid from [products_category_mapping] where articlegroup_id=684
      ) B on A.articleid=B.articleid