时间戳

首页 > 解决方案 > 如何从通过 SQL Developer 中的第三个“链接表”链接的 2 个表中检索数据？

sql - 如何从通过 SQL Developer 中的第三个“链接表”链接的 2 个表中检索数据？

问题描述

首先，我很抱歉，因为这个网站上有类似的问答，但是我无法得到任何解决方案。

我想检索所有菜肴及其详细信息（即菜肴名称和描述）的列表，以及他们的食物及其详细信息。排除任何 ID。

我试过（只是作为检索食物和菜肴名称的测试）：

SELECT dish_name, food_name
FROM dish_food_item_link_tbl, food_item_tbl, dish_tbl
JOIN dish_tbl ON dish_tbl.dish_id =  dish_food_item_link_tbl.dish_id
JOIN food_item_tbl ON  dish_food_item_link_tbl.food_id = food_item_tbl.food_id ;

我收到错误：ORA-00904:"DISH_FOOD_ITEM_LINK_TBL"."DISH.ID":invalid identifier

我的代码：

CREATE TABLE dish_tbl
(
dish_id VARCHAR (3),
dish_name VARCHAR (30) NOT NULL,
dish_description VARCHAR (100) NOT NULL,
CONSTRAINT dish_id_pk PRIMARY KEY (dish_id)
);

CREATE TABLE food_item_tbl
(
food_id VARCHAR (3),
food_name VARCHAR (30) NOT NULL,
food_dietry_type VARCHAR (30) NOT NULL, 
food_unit_price NUMBER (4,2) NOT NULL,
food_date_added DATE NOT NULL, 
food_description VARCHAR (50) NOT NULL,
CONSTRAINT food_id_pk PRIMARY KEY (food_id)
);

CREATE TABLE dish_food_item_link_tbl  /* One dish contains 1 to many food_item. One food_item is in 1 to many dish. */
(
dish_id VARCHAR (3),
food_id VARCHAR (3),
FOREIGN KEY (dish_id) REFERENCES dish_tbl(dish_id),
FOREIGN KEY (food_id) REFERENCES food_item_tbl(food_id),
PRIMARY KEY (dish_id, food_id)
);

我已经适当地填充了表格。例子：


INSERT INTO food_item_tbl VALUES ('F1', 'Chips', 'vegan', 3.99, TO_DATE('17-11-2019','dd-mm-yyyy'), 'Finest oven-baked chips');
INSERT INTO food_item_tbl VALUES ('F2', 'Spaghetti Bolognese', 'Not suitable for special diets', 13.99, TO_DATE('01-01-2020','dd-mm-yyyy'), 'Made from organic wheat flour and British Beef');
INSERT INTO food_item_tbl VALUES ('F3', 'Lasagne', 'Not suitable for special diets', 10.99, TO_DATE('23-02-2020','dd-mm-yyyy'), 'Finest cheese and mince beef');
INSERT INTO food_item_tbl VALUES ('F4', 'Mixed salad', 'vegan', 5.99, TO_DATE('17-04-2020','dd-mm-yyyy'), 'Mix of premium beans and veg');

INSERT INTO dish_tbl VALUES ('D1', 'Cheese Sandwich Meal', 'Cheese sandwich, chips and salad');
INSERT INTO dish_tbl VALUES ('D2', 'Bolognese Bomb', 'Spaghetti Bolognese, chips and salad');

INSERT INTO dish_food_item_link_tbl VALUES ('D1', 'F8');
INSERT INTO dish_food_item_link_tbl VALUES ('D1', 'F4');
INSERT INTO dish_food_item_link_tbl VALUES ('D1', 'F1');
INSERT INTO dish_food_item_link_tbl VALUES ('D2', 'F2');
INSERT INTO dish_food_item_link_tbl VALUES ('D2', 'F4');
INSERT INTO dish_food_item_link_tbl VALUES ('D2', 'F1');

标签： sqloracle-sqldeveloper

解决方案

对于“奶酪三明治编写一次”的问题，SQL*Plus 会为您解决 - 使用BREAK.

这就是你现在所拥有的：

SQL>   select d.dish_name, f.food_name
  2      from food_item_tbl f
  3           inner join dish_food_item_link_tbl l on l.food_id = f.food_id
  4           inner join dish_tbl d on d.dish_id = l.dish_id
  5  order by d.dish_name, f.food_name;

DISH_NAME                      FOOD_NAME
------------------------------ ------------------------------
Bolognese Bomb                 Chips
Bolognese Bomb                 Mixed salad
Bolognese Bomb                 Spaghetti Bolognese
Cheese Sandwich Meal           Chips
Cheese Sandwich Meal           Mixed salad

这就是这样BREAK做的：

SQL> break on dish_name
SQL>
SQL> /

DISH_NAME                      FOOD_NAME
------------------------------ ------------------------------
Bolognese Bomb                 Chips
                               Mixed salad
                               Spaghetti Bolognese
Cheese Sandwich Meal           Chips
                               Mixed salad

SQL>

如果要计算项目数，请使用count函数。必须将未聚合的列（dish_name在这种情况下）放入group by子句中：

SQL> select d.dish_name,
  2         count(*) number_of_food_items
  3    from food_item_tbl f
  4         inner join dish_food_item_link_tbl l on l.food_id = f.food_id
  5         inner join dish_tbl d on d.dish_id = l.dish_id
  6  group by d.dish_name
  7  order by d.dish_name;

DISH_NAME                      NUMBER_OF_FOOD_ITEMS
------------------------------ --------------------
Bolognese Bomb                                    3
Cheese Sandwich Meal                              2

SQL>

推荐阅读