oracle 当另一个字段已在 GROUP BY 中时，WHERE 大于计数的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20087551/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
An SQL query with a WHERE greater than a count, when another field is already in GROUP BY
提问by Michael Sherman
(I gladly welcome better title suggestions)
（我很高兴欢迎更好的标题建议）
I'm trying to write an SQL query in Oracle that only outputs entries WHERE one field has a count above a certain value.
我正在尝试在 Oracle 中编写一个 SQL 查询，该查询仅输出一个字段的计数高于某个值的条目。
Specifically, I have tables AIRPORTS, MARKETS, and STATES.
具体来说，我有表 AIRPORTS、MARKETS 和 STATES。
The AIRPORTS table is information about airports with fields ID (primary key), NAME, ABBR (abbreviation), MARKET (foreign key from MARKETS table), STATE (foreign key from STATE table), and CITY. Like so:
AIRPORTS 表是关于具有字段 ID（主键）、NAME、ABBR（缩写）、MARKET（来自 MARKETS 表的外键）、STATE（来自 STATE 表的外键）和 CITY 的机场的信息。像这样：
14122 PITTSBURGH INTERNATIONAL PIT 30198 42 PITTSBURGH, PA
14150 PELLSTON REGIONAL AIRPORT PLN 34150 26 PELLSTON, MI
14193 PENSACOLA GULF COAST REGIONAL PNS 33728 12 PENSACOLA, FL
MARKETS is information about different markets airports can be in. It contains an ID (primary key) and NAME field. Like so:
MARKETS 是关于机场可以进入的不同市场的信息。它包含一个 ID（主键）和 NAME 字段。像这样：
30576 Baglung, Nepal
30577 Binghamton, NY
30578 Bruggen, Germany
30579 Bergen, Norway
STATES contains information about states in the USA, using the government's FIPS codes. It contains fields FIPS (primary key), NAME, and ABBR (abbreviation). Like so:
STATES 包含有关美国各州的信息，使用政府的 FIPS 代码。它包含字段 FIPS（主键）、NAME 和 ABBR（缩写）。像这样：
1 ALABAMA AL
2 ALASKA AK
4 ARIZONA AZ
5 ARKANSAS AR
I'm trying to write an SQL query that outputs the AIRPORTS.NAME, MARKETS.NAME, and STATES.ABBR fields for all airports in a market that has airports in more than one state, and I'd like to do it without creating a view. I've gotten as far as a query that shows me all the MARKETS.ID with more than 2 airports:
我正在尝试编写一个 SQL 查询，该查询输出市场中所有机场的 AIRPORTS.NAME、MARKETS.NAME 和 STATES.ABBR 字段，该市场的机场处于多个状态，我想在不创建的情况下执行此操作一个看法。我已经得到了一个查询，该查询显示了超过 2 个机场的所有 MARKETS.ID：
SELECT *
FROM(
SELECT markets.id as "market", count(markets.name) as "airports"
FROM markets
INNER JOIN airports
ON airports.market = markets.id
GROUP BY markets.id)
WHERE "airports" > 2
But I'm not exactly sure where to go from here. And I'm sure there's a better way to do this.
但我不完全确定从这里去哪里。我相信有更好的方法来做到这一点。
Thank you!
谢谢！
回答by Linger
SELECT m.id AS market, COUNT(*) AS airports
FROM markets AS m
INNER JOIN airports AS a
ON a.market = m.id
GROUP BY m.id
HAVING COUNT(*) > 2
回答by Jeffrey Kemp
SELECT airport, market, state
FROM (
SELECT airports.name AS airport
,markets.name AS market
,states.abbr AS state
,count(DISTINCT airports.state) OVER (PARTITION BY airports.market)
AS states_per_market
FROM airports
JOIN markets
ON airports.market = markets.id
JOIN states
ON airports.state = states.fips
) WHERE states_per_market > 1;
