一个联合查询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
可是我要将这个语句建立视图,视图又不允许使用子查询。求大神点解。
语句如下:
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
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 试试就知道了
(select id from user1 order by id desc limit 0,1) union all (select id from user2 order by id desc limit 0,1)