mysql复杂的建立视图问题

[jc_liumangtu]

一个联合查询union 是从很多张表取时间最近的一条记录 ，由于union 并不支持每个记录都写 order by limit 所以我改了个写法用子查询的方式通过了
可是我要将这个语句建立视图，视图又不允许使用子查询。求大神点解。
语句如下：

  
CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `ed`@`%` 
    SQL SECURITY DEFINER
VIEW `view_IndexKPI` AS
select * from 
    (select 
        'BMI' AS `IndexCode`,
        '体质指数' AS `IndexName`,
        `iot_fatdatav1`.`BMI` AS `IndexValue`,
        '24.9' AS `LimitedUp`,
        '18.5' AS `LimitedDown`
    from
        `iot_fatdatav1`
    order by `iot_fatdatav1`.`COLLECTDATE` desc
    limit 1) a1
union all
select * from 
(select 
        'BLOODSUGAR' AS `IndexCode`,
        '血糖' AS `IndexName`,
        `iot_bgdatav1`.`BLOODSUGAR` AS `IndexValue`,
        '6.1' AS `LimitedUp`,
        '3.9' AS `LimitedDown`
    from
        `iot_bgdatav1`
    order by `iot_bgdatav1`.`COLLECTDATE` desc
    limit 1
) a2
union all
select * from 
(select 
        'BMR' AS `IndexCode`,
        '基础代谢率' AS `IndexName`,
        `iot_fatdatav1`.`BMR` AS `IndexValue`,
        '0' AS `LimitedUp`,
        '0' AS `LimitedDown`
    from
        `iot_fatdatav1`
    order by `iot_fatdatav1`.`COLLECTDATE` desc
    limit 1
) a3
union all
select * from 
(select 
        'DIASTOLIC' AS `IndexCode`,
        '舒张压' AS `IndexName`,
        `iot_bloodpressurev1`.`DIASTOLICPRESSURE` AS `IndexValue`,
        '80' AS `LimitedUp`,
        '0' AS `LimitedDown`
    from
        `iot_bloodpressurev1`
    order by `iot_bloodpressurev1`.`COLLECTDATE` desc
    limit 1
) a4
union all
select * from 
(select 
        'FATCONTENT' AS `IndexCode`,
        '脂肪比例' AS `IndexName`,
        `iot_fatdatav1`.`FATCONTENT` AS `IndexValue`,
        '20' AS `LimitedUp`,
        '10' AS `LimitedDown`
    from
        `iot_fatdatav1`
    order by `iot_fatdatav1`.`COLLECTDATE` desc
    limit 1
) a5
union all
select * from 
(select 
        'OXYGEN' AS `IndexCode`,
        '血氧饱和度' AS `IndexName`,
        `iot_spo2datav1`.`OXYGEN` AS `IndexValue`,
        '98' AS `LimitedUp`,
        '98' AS `LimitedDown`
    from
        `iot_spo2datav1`
    order by `iot_spo2datav1`.`COLLECTDATE` desc
    limit 1
) a6
union all
select * from 
(select 
        'ECG' AS `IndexCode`,
        '心电图' AS `IndexName`,
        `iot_ecgpicturev1`.`DATAID` AS `IndexValue`,
        '0' AS `LimitedUp`,
        '0' AS `LimitedDown`
    from
        `iot_ecgpicturev1`
    order by `iot_ecgpicturev1`.`COLLECTDATE` desc
    limit 1
) a7
union all
select * from 
(select 
        'PULSE' AS `IndexCode`,
        '脉率' AS `IndexName`,
        `iot_bloodpressurev1`.`PULSE` AS `IndexValue`,
        '100' AS `LimitedUp`,
        '60' AS `LimitedDown`
    from
        `iot_bloodpressurev1`
    order by `iot_bloodpressurev1`.`COLLECTDATE` desc
    limit 1
) a8
union all
select * from 
(select 
        'SYSTOLI' AS `IndexCode`,
        '收缩压' AS `IndexName`,
        `iot_bloodpressurev1`.`SYSTOLICPRESSURE` AS `IndexValue`,
        '120' AS `LimitedUp`,
        '0' AS `LimitedDown`
    from
        `iot_bloodpressurev1`
    order by `iot_bloodpressurev1`.`COLLECTDATE` desc
    limit 1
) a9
union all
select * from 
(select 
        'WEIGHT' AS `IndexCode`,
        '体重' AS `IndexName`,
        `iot_weightdatav1`.`WEIGHT` AS `IndexValue`,
        '0' AS `LimitedUp`,
        '0' AS `LimitedDown`
    from
        `iot_weightdatav1`
    order by `iot_weightdatav1`.`COLLECTDATE` desc
    limit 1
) a10
 

[jc_liumangtu]

引用 1 楼 benluobobo 的回复:

由于union 并不支持每个记录都写 order by limit 这个应该支持吧 create view test as select id from user1 limit 0,1 union all (select id from user2 limit 0,1) 以上语句在5.6上测试可以

LIMIT 可以用，你加上order by 试试就知道了

[benluobo]

加上括号不就可以了？ 你是要取每个表的最近再合并 参考如下： create view test1 as (select id from user1 order by id desc limit 0,1) union all (select id from user2 order by id desc limit 0,1)

[benluobo]

由于union 并不支持每个记录都写 order by limit 这个应该支持吧 create view test as select id from user1 limit 0,1 union all (select id from user2 limit 0,1) 以上语句在5.6上测试可以