我试图找到其票价具有目的地最低票价且不止一家公司使用该目的地的公司。我的命令;
SQL> select tickets.bus_id bus, tickets.fare fare, route.destination dest
from tickets left join route on route.route_id=tickets.route_id;
BUS FARE DEST
---------- ---------- -------------------------
11 21.36 Kayseri
1 23.32 Kayseri
1 63.32 Kars
5 44.31 Edirne
1 54.13 Edirne
1 33.36 Fatsa
4 33.37 Ankara
4 33.39 Kayseri
7 53.37 Istanbul
10 51.36 Izmir
7 51.36 Izmir
10 32.35 Ankara
10 42.34 Istanbul
10 52.33 Hatay
10 32.83 Bursa
2 30.00 Izmir
11 52.00 Kayseri
11 42.00 Kayseri
2 40.00 Kayseri
我想要的输出:
巴士票价目的地 ---------- ---------- ------------- 10 32.83 布尔萨 2 30.00 伊兹密尔
select bus, min(fare), dest from
(select tickets.bus_id bus, tickets.fare fare, route.destination dest
from tickets left join route on route.route_id=tickets.route_id)
group by dest having count(bus)>1;
>ERROR at line 1: ORA-00937: not a single-group group function
当我尝试分组dest
时,它给出了错误。我不明白什么是错误?有没有人想解决它?
--- 编辑表格 ---
CREATE TABLE buscompanies (
bus_id number(4) not null PRIMARY KEY,
name varchar2(25) not null);
table created.
CREATE TABLE route (
route_id number(4) not null PRIMARY KEY,
origin varchar2(25) not null,
destination varchar2(25) not null);
table created.
CREATE TABLE tickets (
tickets_id number(4) not null PRIMARY KEY,
route_id number(4) not null,
bus_id number(4) not null,
fare number(5,2) not null,
CONSTRAINT routeid_fk FOREIGN KEY (route_id) REFERENCES route(route_id),
CONSTRAINT busid_fk FOREIGN KEY (bus_id) REFERENCES buscompanies(bus_id));
SQL> select * from route;
ROUTE_ID ORIGIN DESTINATION
---------- ------------------------- -------------------------
1 Ankara Kayseri
2 Ankara Kars
3 Ankara Edirne
4 Ankara Fatsa
5 Denizli Ankara
6 Denizli Kayseri
7 Hatay Istanbul
8 Hatay Izmir
9 Izmir Ankara
10 Izmir Istanbul
11 Izmir Hatay
12 Izmir Bursa
13 Bursa Izmir
14 Bursa Kayseri
14 rows selected.
- 编辑 -
我使用了此命令,但仅显示 1 个城市
select bus, min(fare), dest from
(select tickets.bus_id bus, tickets.fare fare, route.destination dest
from tickets left join route on route.route_id=tickets.route_id)
group by bus, dest having count(dest) > 1;
BUS MIN(FARE) DEST
---------- ---------- -------------------------
11 21.36 Kayseri
也应该有其他目的地。问题是;
为每个城市找到最便宜的公司,以便超过 1 家公司前往该城市?
你想聚合一个聚合,所以最简单的方法是分组两次,我相信。您可能还想重命名我在这里使用的子查询表别名,以使它们更有意义。
我认为您必须将列总线包含在组中