MySQL操作视图数据

Question

我正在阅读一个视图，它为我提供了如下数据：

msn       | global_device_id | meter_datetime        | interval| channel_id | active_energy_pos_t1     
==================================================================================================
3798000021| 980021           | 2020-07-29 16:45:00   | 15      | 1          | 72.80300
3798000021| 980021           | 2020-07-29 17:00:00   | 15      | 1          | 72.80300
3798000021| 980021           | 2020-07-29 17:15:00   | 15      | 1          | 72.80300
3798000021| 980021           | 2020-07-29 17:30:00   | 15      | 1          | 72.80300
....

每条记录的channel_id都是1。我想操纵它。我想要对上面的数据进行精确的3 copies，1st数据的1应该是channel_id，第二个应该是channel_id，第三个应该是channel_id，如下所示

msn       | global_device_id | meter_datetime        | interval| channel_id | active_energy_pos_t1     
==================================================================================================
3798000021| 980021           | 2020-07-29 16:45:00   | 15      | 1          | 72.80300
3798000021| 980021           | 2020-07-29 17:00:00   | 15      | 1          | 72.80300
3798000021| 980021           | 2020-07-29 17:15:00   | 15      | 1          | 72.80300
3798000021| 980021           | 2020-07-29 17:30:00   | 15      | 1          | 72.80300
3798000021| 980021           | 2020-07-29 16:45:00   | 15      | 2          | 72.80300
3798000021| 980021           | 2020-07-29 17:00:00   | 15      | 2          | 72.80300
3798000021| 980021           | 2020-07-29 17:15:00   | 15      | 2          | 72.80300
3798000021| 980021           | 2020-07-29 17:30:00   | 15      | 2          | 72.80300
3798000021| 980021           | 2020-07-29 16:45:00   | 15      | 3          | 72.80300
3798000021| 980021           | 2020-07-29 17:00:00   | 15      | 3          | 72.80300
3798000021| 980021           | 2020-07-29 17:15:00   | 15      | 3          | 72.80300
3798000021| 980021           | 2020-07-29 17:30:00   | 15      | 3          | 72.80300

....

视图名称为load_profile_data。我该怎么做呢？

Answer 1

您可以将视图与固定值列表进行交叉联接：

select l.msn, l.global_device_id, l.meter_datetime, l.interval, c.channel_id, l.active_energy_pos_t1
from load_profile_data l
cross join (select 1 channel_id union all select 2 union all select 3) c

Answer 2

一种方法是使用联合：

SELECT msn, global_device_id, meter_datetime, `interval`, channel_id, active_energy_pos_t1
FROM load_profile_data
UNION ALL
SELECT msn, global_device_id, meter_datetime, `interval`, 2, active_energy_pos_t1
FROM load_profile_data
UNION ALL
SELECT msn, global_device_id, meter_datetime, `interval`, 3, active_energy_pos_t1
FROM load_profile_data;

附注：INTERVAL是MySQL中的保留关键字。我在上面的查询中用反引号进行了转义，但通常您应该避免使用保留关键字来命名数据库对象。

Answer 3

尝试联合：

select msn , global_device_id , meter_datetime . interval, channel_id ,active_energy_pos_t1 from load_profile_data

union

select msn , global_device_id , meter_datetime . interval, channel_id +1 ,active_energy_pos_t1 from load_profile_data

union

select msn , global_device_id , meter_datetime . interval, channel_id+2 ,active_energy_pos_t1 from load_profile_data