postgresql选择与分组

lndjwyie 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(283)

我是sql新手，玩pokemon数据库学习。开始理解mysql、sqlite和postgresql的一些区别。
sqllite允许我使用以下查询来获取只有一种类型的pokemon（它们只有一行使用select语句作为类型\ u names.name将生成两行--每种类型一行--如果它们有两种类型）：

-- displays all pokemon of a single type (no dual-types)
SELECT pokemon_species.id,
       pokemon_species.identifier,
       type_names.name
FROM pokemon_species
    JOIN pokemon_types ON pokemon_species.id = pokemon_types.pokemon_id
    JOIN type_names ON pokemon_types.type_id = type_names.type_id
GROUP BY 1
    HAVING COUNT(*) = 1
ORDER BY pokemon_species.id;

然而，postgresql将给出“[42803]错误：列”type\u names.name“必须出现在group by子句中，或用于聚合函数位置：70”错误。
我发现以下查询适用于postgresql：

-- displays all pokemon of a single type (no dual-types)
WITH species AS (
    SELECT pokemon_species.id,
           pokemon_species.identifier
    FROM pokemon_species
             JOIN pokemon_types ON pokemon_species.id = pokemon_types.pokemon_id
             JOIN type_names ON pokemon_types.type_id = type_names.type_id
    GROUP BY 1, 2
             HAVING COUNT(*) = 1
    ORDER BY pokemon_species.id
)

SELECT species.*, type_names.name  
    FROM species
             JOIN pokemon_types ON species.id = pokemon_types.pokemon_id
             JOIN type_names ON pokemon_types.type_id = type_names.type_id;

两次连接三个表似乎是多余的，我想知道——这个查询怎么写得更好？
结果示例：

id | identifier | type_name
-- | ---------- | ---------
4  | charmander | Fire
5  | charmeleon | Fire
7  | squirtle   | Water
8  | wartortle  | Water
9  | blastoise  | Water
10 | caterpie   | Bug
11 | metapod    | Bug

sql postgresql group-by select

来源：https://stackoverflow.com/questions/63046331/postgresql-selecting-and-grouping

3条答案

按热度按时间

brccelvz1#

在每个组中只查找一个匹配行，因此可以使用聚合函数：

SELECT ps.id, ps.identifier, MAX(tn.name) as name
FROM pokemon_species ps JOIN
     pokemon_types pt
     ON ps.id = pt.pokemon_id JOIN
     type_names tn
     ON pt.type_id = tn.type_id
GROUP BY 1
HAVING COUNT(*) = 1
ORDER BY ps.id;

想必， pokemon_species.id 声明为主键。所以你可以离开 ps.identifier 在外面 GROUP BY .

赞(0）回复(0）举报 2021-07-24

gwbalxhn2#

你可以过滤单一类型的pokemon id。

SELECT pokemon_species.id,
       pokemon_species.identifier,
       type_names.name
FROM pokemon_species
    JOIN pokemon_types ON pokemon_species.id = pokemon_types.pokemon_id
    JOIN type_names ON pokemon_types.type_id = type_names.type_id
WHERE pokemon_species.id IN (
   SELECT pokemon_id
   FROM pokemon_types
   GROUP BY pokemon_id 
   HAVING COUNT(*) = 1
)

或者可以使用窗口函数对不同的类型进行计数，然后进行筛选

WITH CTE AS 
(SELECT pokemon_species.id,
       pokemon_species.identifier,
       type_names.name as type_name,
       COUNT(distinct type_names.name) OVER (PARTITION BY pokemon_species.id) as type_count
FROM pokemon_species
    JOIN pokemon_types ON pokemon_species.id = pokemon_types.pokemon_id
    JOIN type_names ON pokemon_types.type_id = type_names.type_id
)
SELECT id,
       identifier,
       type_name
FROM CTE
WHERE type_count=1

赞(0）回复(0）举报 2021-07-24

kmynzznz3#

聚合后加入：

SELECT ps.id,
       ps.identifier,
       tn.name
FROM (
  SELECT id,
         identifier,
  FROM pokemon_species 
  GROUP BY ps.id
  HAVING COUNT(*) = 1
) ps 
  JOIN pokemon_types pt ON ps.id = pt.pokemon_id
  JOIN type_names tn ON pt.type_id = tn.type_id
ORDER BY pokemon_species.id;

赞(0）回复(0）举报 2021-07-24

我来回答

postgresql选择与分组

3条答案

相关问题

热门标签

最新问答