SQL:如何根据不同的条件从一列中提取数据作为不同的列？

Question

我有很多接入点，每个AP有两个射频卡，每个射频卡有一个频道号码。

AP_idx | RF_idx | Channel |
   0   |    0   |    7    |
   0   |    1   |    136  |
   1   |    0   |    11   |
   1   |    1   |    128  |
   2   |    0   |    4    |
   2   |    1   |    149  |

现在我需要AP_idx和(RF0通道，RF1通道)之间的映射，参见下面的示例

AP_idx | ChannelA | ChannelB |
   0   |    7     |    136   |
   1   |    11    |    128   |
   2   |    4     |    149   |

我想要channelA频道，RF_idx == 0频道，RF_idx == 1频道

如何设计SQL语句？

Answer 1

如果我正确理解，你想要“枢轴”数据。在SQLite中，通过使用group by实现这一目的的一种方法

select AP_idx,
       max(case when RF_idx = 0 then Channel end) as ChannelA,
       max(case when RF_idx = 1 then Channel end) as ChannelB
from table t
group by AP_idx;

另一种方法是使用join

select ta.AP_idx, ta.channel as ChannelA, tb.channel as ChannelB
from table ta join
     table tb
     on ta.AP_idx = tb.AP_idx and
        ta.RF_idx = 0 and
        tb.RF_idx = 1;

使用正确的索引，这可能具有更好的性能。另一方面，如果缺少一些通道值，则聚合方法更安全。

Answer 2

select AP_idx, ChannelA, ChannelB
from (select AP_idx, Channel AS ChannelA WHERE RF_idx = 0) AS T1
inner join
(select AP_idx, Channel AS ChannelB WHERE RF_idx = 1) AS T2
using (AP_idx)

Answer 3

SQL：

select a.AP_idx, a.Channel, b.Channel
  from (select AP_idx, RF_idx, Channel from t where RF_idx = 0) as a,
       (select AP_idx, RF_idx, Channel from t where RF_idx = 1) as b
 where a.AP_idx = b.AP_idx;

结果：

0|7|136
1|11|128
2|4|149