时间戳

首页 > 解决方案 > SQLite - 使用复杂关节表过滤查询

sql - SQLite - 使用复杂关节表过滤查询

问题描述

大家下午好，

我在一个使用SQLite3数据库的项目上工作，它是用Doctrine（PHP中的 ORM ）生成的。

地铁站表包含巴黎的所有车站：

CREATE TABLE underground_station (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    long_name VARCHAR(255) NOT NULL 
);

行表包含巴黎的所有行：

CREATE TABLE line ( 
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    commercial_name VARCHAR(255) NOT NULL );

此表根据服务站关联地铁线路：

CREATE TABLE line_association ( 
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    underground_station_id INT NOT NULL, 
    line_id INT NOT NULL, 
    is_terminus BOOL NOT NULL, 
    CONSTRAINT fk_association_underground_station FOREIGN KEY (underground_station_id) REFERENCES underground_station(id), 
    CONSTRAINT fk_association_line FOREIGN KEY (line_id) REFERENCES line(id) );

我有一个查询要返回地铁站名称、那里服务的线路以及它是否是终点站：

SELECT u.long_name, group_concat(l.commercial_name) as "lines", la.is_terminus
FROM underground_station u
    JOIN line_association la on u.id = la.underground_station_id
    JOIN line l on la.line_id = l.id
GROUP BY u.id;

查询结果：

+-------------------------+------------------+-------------+
|long_name                | lines            | is_terminus |
+-------------------------+------------------+-------------+
|CHARLES DE GAULLE ETOILE | M6,M2,M1         | 0           |
+-------------------------+------------------+-------------+
|CHATEAU DE VINCENNES     | M1               | 1           |
+-------------------------+------------------+-------------+
|CONCORDE                 | M12,M1,M8        | 0           |
+-------------------------+------------------+-------------+
|FRANKLIN-D.ROOSEVELT     | M9,M             | 0           |
+-------------------------+------------------+-------------+
|LA DEFENSE-GRANDE ARCHE  | M1               | 1           |
+-------------------------+------------------+-------------+
|NATION                   | M2,M9,M6,M1      | 0           |
+-------------------------+------------------+-------------+
|CHATELET                 | M14,M1,M7,M11,M4 | 0           |
+-------------------------+------------------+-------------+

此查询运行良好。我的问题是，当我选择像“M1”这样的特定地下线路时，如何返回仅包含地铁站及其服务的相同数据？

我发现了这种可能性，但我有错误的数据，因为即使分组下的站有 2 个或更多连接，“连接”返回总是返回“1”：

SELECT underground_station.long_name,
   (SELECT count(line_id)
    FROM line_association
    GROUP BY underground_station_id
    HAVING count(line_id)) AS "connections",
    is_terminus
FROM underground_station
JOIN line_association la on underground_station.id = la.underground_station_id
JOIN line l on la.line_id = l.id
WHERE l.commercial_name = 'M1';

查询结果：

+-------------------------+-------------+-------------+
|long_name                | connections | is_terminus |
+-------------------------+-------------+-------------+
|CHARLES DE GAULLE ETOILE | 1           | 0           |
+-------------------------+-------------+-------------+
|CHATEAU DE VINCENNES     | 1           | 1           |
+-------------------------+-------------+-------------+
|CONCORDE                 | 1           | 0           |
+-------------------------+-------------+-------------+
|FRANKLIN-D.ROOSEVELT     | 1           | 0           |
+-------------------------+-------------+-------------+
|LA DEFENSE-GRANDE ARCHE  | 1           | 1           |
+-------------------------+-------------+-------------+
|NATION                   | 1           | 0           |
+-------------------------+-------------+-------------+
|CHATELET                 | 1           | 0           |
+-------------------------+-------------+-------------+

我曾尝试使用“LIKE”条件，但当我尝试仅找到 M1 地铁站及其连接时，结果包含 M14、M13、M12、M11 线路。

我也尝试过“instr(lines, 'M1')”，但它只返回链接到“M1”地下线的数据。

当我按地下线路过滤时，您知道如何获得正确的值吗？

标签： sqlstringsqliteinner-joinaggregate-functions

解决方案

我想你想要一个HAVING子句：

SELECT u.long_name, group_concat(l.commercial_name) as "lines", la.is_terminus
FROM underground_station u
    JOIN line_association la on u.id = la.underground_station_id
    JOIN line l on la.line_id = l.id
GROUP BY u.id
HAVING MAX(l.commercial_name = 'M1') = 1

推荐阅读