sql如何将一次查询的结果与另一次查询的结果合并并排序

查询1:
Select null as name,Checkinout.* from Checkinout where (Checkinout.userid=1000000000000 and (Checkinout.CheckTime >='2008-01-01 00:00:00') and (Checkinout.CheckTime <='2020-01-01 23:59:59')) Order By Checkinout.CheckTime DESC Limit '0','6'

查询2:
Select UserInfo.name,Checkinout.* from UserInfo left join Checkinout on Checkinout.userid=UserInfo.userid where ((Checkinout.CheckTime >='2008-01-01 00:00:00') and (Checkinout.CheckTime <='2020-01-01 23:59:59')) Order By Checkinout.CheckTime DESC Limit '0','6'

将这两次查询的结果合并后按照Checkinout.CheckTime排序

9个回答

Select null as name,Checkinout.* from Checkinout where (Checkinout.userid=1000000000000 and (Checkinout.CheckTime >='2008-01-01 00:00:00') and (Checkinout.CheckTime <='2020-01-01 23:59:59')) Order By Checkinout.CheckTime DESC Limit '0','6'
UNION
Select UserInfo.name,Checkinout.* from UserInfo left join Checkinout on Checkinout.userid=UserInfo.userid where ((Checkinout.CheckTime >='2008-01-01 00:00:00') and (Checkinout.CheckTime <='2020-01-01 23:59:59')) Order By Checkinout.CheckTime DESC Limit '0','6'
ORDER BY CheckTime;

qq_32332777
vamViolet 并集
大约 2 年之前 回复

()查询1 union 查询2) order by...

用union all 链接两个查询表 在用order by 排序
注意 链接的两个表字段名称要一模一样

union之后 用到group by 就可以

select * from (Select null as name,Checkinout.* from Checkinout where (Checkinout.userid=1000000000000 and (Checkinout.CheckTime >='2008-01-01 00:00:00') and (Checkinout.CheckTime <='2020-01-01 23:59:59')) Order By Checkinout.CheckTime DESC Limit '0','6'
UNION
Select UserInfo.name,Checkinout.* from UserInfo left join Checkinout on Checkinout.userid=UserInfo.userid where ((Checkinout.CheckTime >='2008-01-01 00:00:00') and (Checkinout.CheckTime <='2020-01-01 23:59:59')) Order By Checkinout.CheckTime DESC Limit '0','6' )
ORDER BY CheckTime;

sql 查询语句 ,用UNION或者UNION ALL进行查询结果集的合并,2个查询表必须结果列的数量、类型、别名一致 ;
你在合并的时候,如果过滤重复的值用UNION。如果允许重复的值,用 UNION ALL;
再order by就可以了

用union连接两个表,但是后面要有条件,不能直接加

select a.*,b.* from (select * from tbla) as a,(select * from tblb) as b order by a.xxx,b.yyy
说明: select * from tbla 、select * from tblb等,表示某类查询结果

weixin_43142925
ZHZHK001 我这样不知道哪里错了
大约一年之前 回复
weixin_43142925
ZHZHK001 SELECT A.*,B.* FROM (SELECT SUM(SALES_AMOUNT) AS AA FROM TB_ORDER TR WHERE TR.USER_ID = 2 AND TR.SALES_TIME >= TO_DATE( TO_CHAR( (SELECT MAX(W.WKH_MTIME) FROM TB_WKH W WHERE W.USER_ID = 2 AND W.WKH_DIR = 0 GROUP BY W.USER_ID ) , 'YYYY-MM-DD' ) , 'YYYY-MM-DD' ) ) AS A, (SELECT MAX(W.WKH_MTIME) AS BB FROM TB_WKH W WHERE W.USER_ID = 2 AND W.WKH_DIR = 0 GROUP BY W.USER_ID) AS B ORDER BY A.AA,B.BB
大约一年之前 回复

select * from (select * from tbla union select * from tblb ) order by xxx
说明: 先合并结果,然后在排序显示

weixin_43142925
ZHZHK001 你这个ORA-01790: 表达式必须具有与对应表达式相同的数据类型 01790. 00000 - "expression must have same datatype as corresponding expression" *Cause:
大约一年之前 回复

union on就可以了,保证查询的字段名保持一致,字段数量不同的时候用“”也是可以的

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问