有没有一种方法可以将一行“拆分”为多行？
我的问题是我有一个边缘表，其中patternID是边缘的ID，而sourceStationID和targetStationID是边缘连接的ID：
模式：
patternID | sourceStationID | targetStationID
1|1|2
2|1|6
3|1|3
4|1|4
5|4|6
6|5|6
我还有可在其中转移的集线器表：
集线器：
hubID
4
5
我需要完全通过一个集线器连接站点1-> 6的那些数据模式ID。因此，查询结果应为：
4
5
我这样做是通过将模式表与集线器表连接在一起，然后再次将模式表与模式表连接在一起，因此得到：
patternID | sourceStationID | targetStationID | patternID | sourceStationID | targetStationID
4 | 1 | 4 | 5 | 4 | 6
如何将这一行分为两行？
编辑：
这是我到目前为止使用的代码：
select t2a.patternID from
(
select * from `patterns`
join `hubs` on `targetStationID` = `hubID`
where `sourceStationID` = 1
) as t1a
join
(
select * from `patterns`
join `hubs` on `sourceStationID` = `hubID`
where `targetStationID` = 6
) as t2a
on t1a.hubID = t2a.hubID
union
select t1b.patternID from
(
select * from `patterns`
join `hubs` on `targetStationID` = `hubID`
where `sourceStationID` = 1
) as t1b
join
(
select * from `patterns`
join `hubs` on `sourceStationID` = `hubID`
where `targetStationID` = 6
) as t2b
on t1b.hubID = t2b.hubID;
它正在工作，但我两次使用相同的选择。
最佳答案
更新我的答案以希望更接近您想要的内容。据我了解您的问题，这似乎可以满足您的要求。通过在CTE中完成其联接，可以避免该视图：
C:\Users\DDevienne>sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table edges (id integer primary key autoincrement, beg int, end int);
sqlite> insert into edges (beg, end) values (1, 2), (1, 6), (1, 3), (1, 4), (4, 6), (5, 6);
sqlite> create view edges2 as
...> select start.id as beg_id, start.beg as beg, start.end as hub, finish.id as end_id, finish.end as end
...> from edges start, edges finish
...> where start.end = finish.beg;
sqlite> .headers on
sqlite> select * from edges2;
beg_id|beg|hub|end_id|end
4|1|4|5|6
sqlite> create table hubs (id integer primary key);
sqlite> insert into hubs values (4), (5);
sqlite> with hub_edges(beg_id, beg, hub, end_id, end) as (
...> select edges2.* from edges2, hubs where edges2.hub = hubs.id
...> )
...> select beg_id as id, beg, hub as end from hub_edges
...> union all
...> select end_id as id, hub as beg, end from hub_edges;
id|beg|end
4|1|4
5|4|6
sqlite>
警告：这需要最新的SQLite（3.8.4.3），并且不适用于3.8.3.x
关于mysql - SQLite“将”行拆分为多行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23222315/