撒花时刻到了...求各位牛牛解决..关于sql查询语句的问题

现在有两张表

另一张是

我现在想要的效果是..

j急求各位牛牛解决...

下面这是我之前一个表的时候做的统计..

<select id="getReportListByTime" resultType="com.tmg.entity.recruitment.pojo.ReportList" parameterType="map" >
    select 
    sum(STATUS = 'suitable') AS suiNum,
    sum(STATUS = 'unsuitable') AS unsuiNum,
    sum(DEAL_STATUS = 'Signed') AS signedNum,
	sum(DEAL_STATUS = 'Refused') AS refusNum,
	sum(DEAL_STATUS = 'Connected' OR DEAL_STATUS = 'Signed' OR DEAL_STATUS = 'Refused') AS connectNum,
	sum(DEAL_STATUS IS NOT NULL) AS reachedNum,
	CHOOSE_BY AS userName
    from RECRU_USER_REPOSITORY 
 	 <trim prefix="WHERE" prefixOverrides="AND |OR ">
 	 <if test="start!=null and !start.equals('')">
    	and CHOOSE_TIME <![CDATA[>=]]> #{start,jdbcType=VARCHAR} 
    </if>
    <if test="end!=null and !end.equals('')">
    	and CHOOSE_TIME <![CDATA[<=]]> #{end,jdbcType=VARCHAR} 
    </if>
  		and CHOOSE_BY IS NOT NULL
    </trim>
    group by CHOOSE_BY
  </select>
</mapper>

6个评论

20个牛币技术 sql 查询

所有回答列表(6)

遇见， LV36 2014年8月12日

SELECT T.LOGINNAME "loginName", SUM(CASE T.MARK WHEN '1' THEN 1 ELSE 0 END) "marked"
, SUM(CASE DEALSTALE WHEN 'suitable' THEN 1 ELSE 0 END) "suitable"
, SUM(CASE DEALSTALE WHEN 'unsuitable' THEN 1 ELSE 0 END) "unsuitable"
, SUM(CASE DEALSTALE WHEN 'Reached-out' THEN 1 ELSE 0 END) "Reached-out"
, SUM(CASE DEALSTALE WHEN 'Connected' THEN 1 ELSE 0 END) "Connected"
FROM
(SELECT LOGINNAME,DEALSTALE, '1' MARK FROM USERMARK
UNION ALL
SELECT LOGINNAME,DEALSTALE, '0' MARK FROM USERTRUSH) T GROUP BY T.LOGINNAME ORDER BY T.LOGINNAME;

评论(6) 最佳答案

limi168 LV9 2014年8月12日

这是Mysql的写法

SELECT
   a.loginName,
   sum(1) AS marked,
   sum(if(a.dealstale='suitable',1,0)) AS suitable,
   sum(if(b.dealstale='unsuitable',1,0)) AS unsuitable,
   sum(if(a.dealstale='Reached-out',1,0)) AS "Reached-out",
   sum(if(a.dealstale='connected',1,0)) AS connected
FROM
test.usermark a,
test.usertrush b
WHERE
   a.id = b.id
GROUP BY
   a.loginName

如果是oracle 就把if换成decode 就好了

如图

kiky LV10 2014年8月12日

select t1.loginname ,t2.mark ,t1.suitable,t1.unsuitable,t1."reached-out",t1.connected from
(
select
       loginname,
       sum(case when dealstale = 'suitable' then 1 else 0 end ) as suitable ,
       sum(case when dealstale = 'unsuitable' then 1 else 0 end ) as unsuitable,
       sum(case when dealstale = 'reached-out' then 1 else 0 end ) as "reached-out",
       sum(case when dealstale = 'connected' then 1 else 0 end ) as connected
from
       (select * from usermark union select * from usertrush )
group by loginname order by 1
) t1,
(select loginname ,count(*) as mark from usermark group by (loginname ) order by loginname) t2
where t1.loginname = t2.loginname

遇见， LV36 2014年8月12日

两个表用union all将结果集当成一个新表处理就好了。

记得约定 LV9 2014年8月22日

- - 默默地路过

275934449b LV4 2014年10月31日

路过

最热评论
最新问答