一个联合查询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
解决方案 »
- 关于PostgreSql远程数据传输问题
- 取不到值,请大家帮忙修改这条mysql语句。
- 关于mssql 与 mysql的转换
- 虚拟主机上,一台机器可以有几百个mysql数据库,是每个数据库都启动单独的服务,还是只启动一个服务呀?
- MYSQL数据丢失,查看MYD文件,内容还在,MYISAMCHK检查提示Data records:0 Deleted blocks:4218,能否恢复?
- 请教两个MYSQL的问题
- 请问mysql怎么用dos命令打开服务和关闭服务
- 我想用php+mysql做一个在线做作业批改作业的系统
- 有那位大虾知道, 如何将Linux上的MySQL的一个库移植到NT上?
- mysql密码问题
- 求一个SQL
- 求问一个mySQL的SQL查询的优化方法
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)