SQL:如何在多列上选择一个表的公共行 [英] SQL: how to select common lines of one table on several column

问题描述

我有一张桌子:

create table a (page int, pro int)
go
insert into a select 1, 2
insert into a select 4, 2
insert into a select 5, 2
insert into a select 9, 2
insert into a select 1, 3
insert into a select 2, 3
insert into a select 3, 3
insert into a select 4, 3
insert into a select 9, 3
insert into a select 1, 4
insert into a select 9, 4
insert into a select 12, 4
insert into a select 1, 5
insert into a select 9, 5
insert into a select 12, 5
insert into a select 13, 5
insert into a select 14, 5
insert into a select 15, 5
go

(这里是这个表的SQLfiddle以及我开始写的查询)

(here is the SQLfiddle of this table and queries I began to write )

1. 我希望从该表中提取每个pro"列的公共列页面".这是我们期望的:

1. I'm looking to extract the common column "page" for each column "pro" from this table. here is what we expect :

 1
 9

我尝试使用:

SELECT DISTINCT a.page
FROM a
WHERE a.page IN (
  SELECT b.page FROM a as b
  WHERE b.pro <> a.pro
) 

但是这个查询返回每个页面"，这些页面"至少有一个共同的值，这不是我们需要的.见下文:

but this query returns every "page" that have at least one common values which is not what we need to have. see below :

 1
 4
 9
12

相反的查询又名不同的值至少有一个但不是所有时间

The opposite query aka different value at least one but not all time

我希望提取链接到一个或多个pro"的页面"，但对所有这些页面"都不通用(这与上一个查询完全相反)

I'm looking to extract the "page" linked to one or more "pro" but without being common to all of them (it's the exact opposite of the previous query)

这是我们期望的:

  2
  3
  4
  5
 12
 13
 14
 15

我无法找到这两个查询的解决方案:'(有人可以帮我解决这些问题吗?

I can't manage to find a solution to those 2 queries :'( Could anyone help me on those ones?

最好的问候

SQLfiddle url

edit: the SQLfiddle url

推荐答案

只是有点逆向思维 - 按 page 分组并为每个值计算不同的 pro 值.返回与不同 pro 值的总数相匹配的行

Just a bit of reversed thinking - group by page and count distinct pro values for each. Return rows that matches the total of distinct pro values

SELECT [page]
FROM a
GROUP BY [page]
HAVING COUNT(DISTINCT pro) = (SELECT COUNT(DISTINCT pro) FROM a)

SQLFiddle

对于第二个问题，只需将 = 替换为 '<'在最后一行 -> SQLFiddle

for the second problem, just replace = with '<' in the final line -> SQLFiddle

这篇关于SQL:如何在多列上选择一个表的公共行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！