请问这查询出来的两张表使用了union all 后数据为什么不正常,可是如果我把这两张表查询出来的数据插入到两张真实表中,然后使用union all
查询出来的数据是正确的,是不是在查询这两张表中已经使用了union all ,然后再次使用有问题,有人晓得吗
SELECT device_name,device_ip ,module_name, area_name,0 unusual_num,usual_num FROM(
SELECT device_name,device_ip ,module_name, area_name,usual_num FROM (
SELECT device_name,device_ip ,module_name, area_name,COUNT(id) usual_num FROM(
SELECT device_name,device_ip ,module_name, area_name,id,time FROM (SELECT DATE_ADD('2017-07-04',INTERVAL @i:=@i+1 DAY) as time
FROM ( select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1
) as tmp,(SELECT @i:= -1) t) a
left JOIN
(SELECT DATE_FORMAT(p.first_find_time,'%Y-%m-%d') first_time ,d.entip device_ip,p.id,d.entname device_name, s.entname module_name, ar.entname area_name,DATE_FORMAT(p.recent_find_time,'%Y-%m-%d') recent_time
FROM process_device_log p,device_info d ,area_info ar,soc_module_info s
WHERE d.module_id = s.entid AND d.area_id = ar.entid AND p.device_id = d.entid AND (p.pro_status=1 or p.pro_status=2) AND p.first_find_time <= '2017-07-11 23:59:59' AND p.recent_find_time>='2017-07-04 00:00:00') b
on recent_time>=a.time AND first_time <= a.time
union all
SELECT d.entname device_name, d.entip device_ip, s.entname module_name, ar.entname area_name,p.id,DATE_FORMAT(p.first_find_time,'%Y-%m-%d') time FROM
process_device_log p,device_info d ,area_info ar,soc_module_info s WHERE d.module_id = s.entid AND d.area_id = ar.entid AND p.device_id = d.entid AND p.first_find_time >= '2017-07-04 00:00:00'
AND p.first_find_time<= '2017-07-11 23:59:59' AND ISNULL(p.recent_find_time) AND (p.pro_status=1 or p.pro_status=2) ) c group by device_ip
) d
WHERE usual_num >0) g
union all 就是就是就是就是这里的union all 前面一个表,后面一个表都能查询出数据,可是一拼起来数据就错了
SELECT device_name,device_ip ,module_name, area_name,unusual_num,0 usual_num FROM(
SELECT device_name,device_ip ,module_name, area_name,unusual_num FROM (
SELECT device_name,device_ip ,module_name, area_name,COUNT(id) unusual_num FROM(
SELECT device_name,device_ip ,module_name, area_name,id,time FROM (SELECT DATE_ADD('2017-07-04',INTERVAL @i:=@i+1 DAY) as time
FROM ( select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1
) as tmp,(SELECT @i:= -1) t) a
left JOIN
(SELECT DATE_FORMAT(p.first_find_time,'%Y-%m-%d') first_time ,d.entip device_ip,p.id,d.entname device_name, s.entname module_name, ar.entname area_name,DATE_FORMAT(p.recent_find_time,'%Y-%m-%d') recent_time
FROM process_device_log p,device_info d ,area_info ar,soc_module_info s
WHERE d.module_id = s.entid AND d.area_id = ar.entid AND p.device_id = d.entid AND p.pro_status=3 AND p.first_find_time <= '2017-07-11 23:59:59' AND p.recent_find_time>='2017-07-04 00:00:00') b
on recent_time>=a.time AND first_time <= a.time
union all
SELECT d.entname device_name, d.entip device_ip, s.entname module_name, ar.entname area_name,p.id,DATE_FORMAT(p.first_find_time,'%Y-%m-%d') time FROM
process_device_log p,device_info d ,area_info ar,soc_module_info s WHERE d.module_id = s.entid AND d.area_id = ar.entid AND p.device_id = d.entid AND p.first_find_time >= '2017-07-04 00:00:00'
AND p.first_find_time<= '2017-07-11 23:59:59' AND ISNULL(p.recent_find_time) AND p.pro_status=3 )c group by device_ip
) d
WHERE unusual_num >0) h

请问这查询出来的两张表使用了union all 后数据为什么不正常,
- 写回答
- 好问题 0 提建议
- 关注问题
- 邀请回答
-
4条回答 默认 最新
- 寒江勿落雪 2017-07-11 02:52关注
虽然回答的都不对,原因是SELECT @i:= -1这里,i 前面已经递增,后面再使用肯定会出错。所以后面的变量i要更换
本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 1无用