如何筛选列以选择特定关键字

I have a table called Products in which I have a column called product and airport. Airports has Multiple values I need a SQL statement by which I will be able to get products names for one airport from the column airport (Which has multiple values such as Birmingham International, Luton Airport, Heathrow, Gatwick) e.g. If I want all the products for the airport Heathrow, How would I do that? would I use indexes, if yes, how? Here is the sample data:

我有一个名为Products的表,其中有一个名为product and airport的列。机场有多个值我需要一个SQL语句,通过该语句我可以从列机场获得一个机场的产品名称(其中有多个值,如伯明翰国际机场,卢顿机场,希思罗机场,盖特威克机场),例如:如果我想要机场希思罗机场的所有产品,我该怎么做?我会使用索引,如果是,怎么样?以下是示例数据:

mysql> SELECT * FROM products order by product;

| id | product                          | price  | description                    | type    | airport                                                    |
|  1 | Benson and Hedges Special filter | £28.00 | Cigarettes, Lighter and filter | Tobacco | Birmingham International, Luton Airport, Heathrow, Gatwick |
-----------------------
1 row in set (0.00 sec)

2 个解决方案

#1

create new table for Airports, f.e.

为机场创建新表,f.e。

CREATE TABLE "AIRPORT"
  (
    "AIRPORT_ID"    NUMBER ,
    "AIRPORT_NAME"   CHAR
)

and link your table with AIRPORT through link-table

并通过链接表将您的表与AIRPORT相关联

CREATE TABLE "CITS_USER"."L_YOUR_TABLE_AIRPORT"
  (
    "L_YOUR_TABLE_AIRPORT_ID" NUMBER NOT NULL ENABLE,
    "YOUR_TABLE_ID"          NUMBER NOT NULL ENABLE,
    "AIRPORT_ID"       NUMBER NOT NULL ENABLE
    CONSTRAINT "FK_L_YOUR_TABLE_AIRPORT_YOUR_TABLE" FOREIGN KEY ("YOUR_TABLE_ID") REFERENCES "YOUR_TABLE" ("YOUR_TABLE_ID") ENABLE,
    CONSTRAINT "FK_L_L_YOUR_TABLE_AIRPORT_AIRPORT" FOREIGN KEY ("AIRPORT_ID") REFERENCES "AIRPORT" ("AIRPORT_ID") ENABLE
  )

so your query will be

所以你的查询将是

select yt.product from 
your_table yt
join L_YOUR_TABLE_AIRPORT yta on yta.YOUR_TABLE_ID = yt.YOUR_TABLE_ID
join AIRPORT ap on ap.AIRPORT_ID = yta.AIRPORT_ID
where ap.NAME = 'Luton Airport'

#2

Structure change

I would suggest that you create other tables this way:

我建议你这样创建其他表:

Product (id, product, price, description)
ProductAirports (productId, airportId)
Airport (id, name)

the query

Then, if you want the products available in the air port of Birmingham International you would write

然后,如果您想要在伯明翰国际机场的航空港口提*品,您可以写信

select 
    p.id, p.product
from Product p
    inner join ProductAirports pa on pa.productId = p.id
    inner join Airport a on a.id = pa.airportId
where
    a.name = 'Birmingham International'