请问这查询出来的两张表使用了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
虽然回答的都不对，原因是SELECT @i:= -1这里，i 前面已经递增，后面再使用肯定会出错。所以后面的变量i要更换
只要保证 前面查的字段 和后面相对应就好了 ！ 字段名 要求一样 和 字段个数也要一样的
SELECT
_assetFlow.order_no AS orderNo,
_assetFlow.order_type AS orderType,
_assetFlow.operate_type AS operateType,
_assetFlow.money_operate AS moneyOperate,
_assetFlow.money_unit AS moneyUnit,
@orderState := 2 AS orderState,
_assetFlow.flow_remark AS flowRemark,
_assetFlow.create_person_id AS createPersonId,
_assetFlow.create_date AS createDate,
_assetFlow.is_del AS isDel,
_assetFlow.pay_type AS payType
FROM
y_asset_flow _assetFlow
WHERE
_assetFlow.is_del = 0 and _assetFlow.create_person_id != 0
UNION ALL
SELECT
_memberOrder.order_no AS orderNo,
@orderType := 5,
@operateType := 0,
_memberOrder.order_money AS moneyOperate,
_memberOrder.order_money_unit AS moneyUnit,
_memberOrder.order_state AS orderState,
_memberOrder.flow_remark AS flowRemark,
_memberOrder.create_person_id AS createPersonId,
_memberOrder.create_date AS createDate,
_memberOrder.is_del AS isDel,
_memberOrder.pay_type AS payType
FROM
m_member_order _memberOrder
WHERE
_memberOrder.is_del = 0
AND _memberOrder.order_state = 0
这是我写的一个例子