Postgresql中的过于复杂的视图语句 [英] Over complicating view statement in postgresql

问题描述

这是我的观点，我正在尝试输出3列，分别为"flightid"，"cancelledseats"和"reservedseats".但是当我执行它时，它执行了太多的数据.我只想要一行而不是15.

This is my view, I'm trying to output 3 columns called 'flightid', 'cancelledseats' and 'reservedseats'. But when I execute it, it executes too much data. I only want one row not 15.

CREATE VIEW check_flight_status AS
SELECT
    (SELECT flightid FROM FLIGHTBOOKING WHERE flightid=111),
    (SELECT COUNT(flightID) FROM FLIGHTBOOKING
    WHERE status='c' AND flightid=111) AS cancelledseats, 
    (SELECT COUNT(flightid) FROM FLIGHTBOOKING 
    WHERE status='r' AND flightid=111) AS reservedseats
FROM flightbooking
INNER JOIN flight
ON flightbooking.flightid=flight.flightid;

(我无法在没有截图的情况下向您显示我的表格，因为随着布局的混乱，您将无法以格式文本理解它.但是，我确实尝试过了)

(I'm not able to show you my tables without taking a screenshot because you'll not be able to understand it in formatted text as the layout messes up. However, I did try)

飞行表

机票预订表

预期输出将具有3列(航班号，取消座位和预留座位)和一行，其中数据为111,1,0.

The expected output is to have 3 columns (flightid, cancelledseats and reservedseats) and one row with data 111,1,0.

EDIT

EDIT

我已经解决了我的问题！

I've just fixed my problem!

CREATE VIEW check_flight_status AS
SELECT
    (SELECT flightid FROM FLIGHTBOOKING WHERE flightid=111),
    (SELECT COUNT(flightID) FROM FLIGHTBOOKING
    WHERE status='c' AND flightid=111) AS cancelledseats, 
    (SELECT COUNT(flightid) FROM FLIGHTBOOKING 
    WHERE status='r' AND flightid=111) AS reservedseats
FROM flightbooking
INNER JOIN flight
ON flightbooking.flightid=flight.flightid
WHERE flight.flightid=111;

通过添加WHERE flight.flightid = 111;到最后，它只输出一行.但是，您认为我的观点过于复杂了吗?可以简化吗?

推荐答案

如注释中所述，查询包含不必要的复杂性元素，并且如果没有有关架构的更多信息，则很难确定问题出在哪里.以及您在这里的愿望.但是，我建议，作为第一个选项，您的额外行问题可能是尽管您在子查询中进行了过滤，但实际上您并未真正在带有WHERE子句的整个查询中过滤结果.

As has been mentioned in the comments, the query includes elements of unnecessary complexity, and without more information about your schema, it's difficult to be sure what the problem is and what you desire here. I would suggest, however, as a first stab, that your problem with extra rows may be that you're not actually filtering results in the overall query with a WHERE clause, despite the filtration you get in your subqueries.

请尝试以下操作:

CREATE VIEW check_flight_status AS
SELECT
    flightid,
    COUNT(*) FILTER (WHERE status = 'c') AS cancelledseats,
    COUNT(*) FILTER (WHERE status = 'r') AS reservedseats
FROM
    flight
    INNER JOIN flightbooking USING (flightid)
WHERE
    flightid = 111;

请注意:

1. COUNT(*)在这里很好.我假设flightid是主键，不能为NULL.
2. 便捷的USING语法可简化使用具有相同名称的字段的联接.
3. 在select子句中使用过滤器，这是无需子查询即可获取所需数据的多种方法之一.
4. COUNT个聚合应将您的行数减少为一，并且WHERE子句将计数的影响限制为特定排期.

同样，在没有模式或相关表信息的情况下，很难确定所需的查询，并且在没有更好地描述问题的情况下，很难确定所需的输出.

Again, hard to be sure of the query you need without a schema or information about the relevant tables, and it's hard to be sure what output you desire without a better description of your problem.

通常，如果您只需要一组集合中的一行，则可以将LIMIT 1添加到查询中，但是如果没有适当的结构化查询和特定意图，则不应执行此操作，并且我认为在这种情况下， LIMIT 1是合适的.

In general, if you need only one row of a set, you can add LIMIT 1 to your query, but you should not do this without a properly structured query and specific intent, and I think this is not a case where LIMIT 1 is appropriate.

这篇关于Postgresql中的过于复杂的视图语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！