使用union进行多表查询

最新推荐文章于 2024-08-06 17:41:54 发布

原创最新推荐文章于 2024-08-06 17:41:54 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

java 同时被 3 个专栏收录

29 篇文章

订阅专栏

数据库

5 篇文章

订阅专栏

mybatis

2 篇文章

订阅专栏

本文介绍了一个SQL存储过程的创建，用于统计不同业务排队信息中未处理的数量，并探讨了如何通过MyBatis将存储过程的执行结果以Map集合的形式返回。文章详细展示了两种存储过程的实现方式，一种返回list集合，另一种返回Map集合。

我在这边写了个存储过程，

--procedure_pdxx存储过程用于查询还未处理的排队信息，统计各个业务未处理的数量
IF EXISTS(SELECT * FROM sysobjects WHERE OBJECT_ID('procedure_pdxx') = ID AND type = 'P')
    DROP PROCEDURE procedure_pdxx
GO
PRINT 'CREATE PROCEDURE procedure_pdxx ......'
GO

create procedure procedure_pdxx
as
begin
	select COUNT(ZYBH) as count,'txxxbCount' as name from TXXXB where TXKSSJ is null and TXJSSJ is null and DATEDIFF(dd,getDate(),DJRQ)=0
	union
	select COUNT(ZYBH) as 'count','tjcsbCount' as name from TJCSDJB where CSSJ is null and HSSJ is null and DATEDIFF(dd,getDate(),DJRQ)=0
	union
	select COUNT(ZYBH) as 'count','lshjCount' as name from LSHJXXB where HJKSSJ is null and HJJSSJ is null and DATEDIFF(dd,getDate(),DJRQ)=0
	union
	select COUNT(ZYBH) as 'count','jshjCount' as name from JSHJXXB where HJKSSJ is null and HJJSSJ is null and DATEDIFF(dd,getDate(),DJRQ)=0;
end

exec procedure_pdxx;

exec procedure_pdxx得到的结果是

我是用mybatis将这个结果返回出去，这个返回的结果是list集合了

<!--查询排队信息正在等候的人数-->
  <select id="selectPdxxCount" resultType="List">
  	exec procedure_pdxx
  </select>

现在我思考一个问题，怎么样返回一个map集合呢

我还是写一个存储过程，如下：

--procedure_pdxx存储过程用于查询还未处理的排队信息，统计各个业务未处理的数量
IF EXISTS(SELECT * FROM sysobjects WHERE OBJECT_ID('procedure_pdxx') = ID AND type = 'P')
    DROP PROCEDURE procedure_pdxx
GO
PRINT 'CREATE PROCEDURE procedure_pdxx ......'
GO

create procedure procedure_pdxx
as
begin
	declare @txxxbCount int,@tjcsbCount int,@lshjCount int,@jshjCount int;
	
	select @txxxbCount = count(ZYBH) from TXXXB where TXKSSJ is null and TXJSSJ is null and DATEDIFF(dd,getDate(),DJRQ)=0
	
	select @tjcsbCount = count(ZYBH) from TJCSDJB where CSSJ is null and HSSJ is null and DATEDIFF(dd,getDate(),DJRQ)=0
	
	select @lshjCount = count(ZYBH) from LSHJXXB where HJKSSJ is null and HJJSSJ is null and DATEDIFF(dd,getDate(),DJRQ)=0
	
	select @jshjCount = COUNT(ZYBH) from JSHJXXB where HJKSSJ is null and HJJSSJ is null and DATEDIFF(dd,getDate(),DJRQ)=0;

	select @txxxbCount as txxxbCount,@tjcsbCount as tjcsbCount,@lshjCount as lshjCount,@jshjCount as jshjCount;
end

exec procedure_pdxx;

exec procedure_pdxx的结果是：

txxxbCount	tjcsbCount	lshjCount	jshjCount
0	0	0	0

然后mybatis中mapper的写法如下：

<!--查询排队信息正在等候的人数-->
  <select id="selectPdxxCount" resultType="Map">
  	exec procedure_pdxx
  </select>

Map<String, Object> selectPdxxCount();