oracle SQL 从一张联表表中全选
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19577349/
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
SQL select all from one table of joint tables
提问by Martin Melka
I am a SQL beginner and I need to figure out this query: I have three tables joined together from which I am counting certain value, like this:
我是一名 SQL 初学者，我需要弄清楚这个查询：我将三个表连接在一起，从中计算某些值，如下所示：
SELECT SEATS_MAX-COUNT(BOOKING_ID)
FROM FLIGHTS
INNER JOIN PLANES ON FLIGHTS.PLANE_ID=PLANES.PLANE_ID
LEFT JOIN BOOKINGS ON FLIGHTS.FLIGHT_ID=BOOKINGS.FLIGHT_ID
GROUP BY SEATS_MAX;
This returns number of free seats in a flight. But I would like to get all the columns from FLIGHTS (as in SELECT * FROM FLIGHTS;) plus the count number. i.e. something like
这将返回航班中的空闲座位数。但我想从 FLIGHTS 中获取所有列（如SELECT * FROM FLIGHTS;）加上计数。即类似的东西
SELECT FLIGHTS.*, SEATS_MAX-COUNT(BOOKING_ID)
FROM FLIGHTS
INNER JOIN PLANES ON FLIGHTS.PLANE_ID=PLANES.PLANE_ID
LEFT JOIN BOOKINGS ON FLIGHTS.FLIGHT_ID=BOOKINGS.FLIGHT_ID
GROUP BY SEATS_MAX;
but this doesn't work (invalid user.table.column, table.column or column specification). Is there a way to achieve this? I'm using Oracle db.
但这不起作用（无效的 user.table.column、table.column 或列规范）。有没有办法实现这一目标？我正在使用 Oracle 数据库。
Thanks
谢谢
回答by mucio
In the group by you need to have all the column not aggregated.
在 group by 中，您需要将所有列都未聚合。
So you query has to become:
所以你的查询必须变成：
SELECT FLIGHTS.*,
SEATS_MAX-COUNT(BOOKING_ID)
FROM FLIGHTS
INNER JOIN PLANES
ON FLIGHTS.PLANE_ID = PLANES.PLANE_ID
LEFT JOIN BOOKINGS
ON FLIGHTS.FLIGHT_ID = BOOKINGS.FLIGHT_ID
GROUP BY FLIGHTS.Column1,
...
FLIGHTS.ColumN,
SEATS_MAX;
Edit:To list all columns of you table you can use the following query
编辑：要列出表的所有列，您可以使用以下查询
SELECT 'FLIGHTS.' || column_name
FROM user_tab_columns
WHERE table_name = 'FLIGHTS'
ORDER BY column_id;
This should make your life a bit easier, then copy and paste
这应该会让你的生活更轻松，然后复制和粘贴
回答by danny
This looks a common mistake to sql beginners. please note that when you use group by clause on some columns (in your case SEATS_MAX), most databases don't allow you to add other columns in your select clause except aggregation on SEATS_MAX such as count(SEATS_MAX), max(SEATS_MAX) and so on.
对于 sql 初学者来说，这看起来是一个常见的错误。请注意，当您在某些列（在您的情况下为 SEATS_MAX）上使用 group by 子句时，大多数数据库不允许您在 select 子句中添加其他列，除了 SEATS_MAX 上的聚合，例如 count(SEATS_MAX)、max(SEATS_MAX) 和很快。
This is because your data has been grouped by SEATS_MAX so the result set is based on each group. to a group of data that has a same SEATS_MAX value, the values of Flight table's columns (FLIGHTS.*) are not necessarily the same. Therefore, databases don't know which values to return.
这是因为您的数据已按 SEATS_MAX 分组，因此结果集基于每个组。对于具有相同 SEATS_MAX 值的一组数据，Flight 表的列 (FLIGHTS.*) 的值不一定相同。因此，数据库不知道要返回哪些值。
回答by Lawrence Lopez
This should work:
这应该有效：
SELECT flights.*, seats_max-booked_seats
FROM flights, (SELECT COUNT(*) booked_seats, flight_id
FROM bookings GROUP BY flight_id) book
WHERE flights.flight_id = book.flight_id
