4.解决多表连接中（左连和等值）因添加新的报表导致查询结果有问题的方法

最新推荐文章于 2021-02-02 01:08:45 发布

江山w美人

最新推荐文章于 2021-02-02 01:08:45 发布

阅读量457

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/liujian928730/article/details/50468955

版权

MySQL 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

需求及解决办法如下：

SELECT
		a.id AS id,
		c.sdate AS sdate,
		a. NAME AS NAME,
		ifnull(sum(c.xzuser),0) AS totalxz,
		ifnull(sum(c.tcount),0) AS totaljs,
		ifnull(sum(c.jsuser),0) AS
		totaluseable,
		ifnull(sum(c.duliuser),0) AS totaldl,
		ifnull(sum(c.imeiuser),0) AS totalimei,
		ifnull(sum(c.noimsiuser),0) AS totalnoimsi
		FROM
		tb_customercorp a
		INNER JOIN
		tb_customerapp
		b ON a.id = b.channelid
		INNER JOIN(
		SELECT
		usc.channelid AS
		appid,
		usc.sdate AS sdate,
		v_uj.tcount AS tcount,
		tuc.usercount AS jsuser,
		tuic.usercount AS duliuser,
		usc.usercount AS xzuser,
		(select uic.usercount from tb_userstat_imei_channelid as uic where uic.channelid = usc.channelid and uic.sdate = usc.sdate) as imeiuser,
		(select uiic.usercount from tb_userstat_imeiimsi_channelid as uiic where uiic.channelid = usc.channelid and uiic.sdate = usc.sdate) as noimsiuser
		FROM
		tb_userstat_channelid
		AS usc
		LEFT JOIN
		tb_userjs_channelid
		as tuc
		ON usc.channelid = tuc.channelid
		AND
		usc.sdate = tuc.sdate
		LEFT JOIN 
		tb_userimsi_channelid
		as tuic
		ON tuc.sdate = tuic.sdate
		AND
		tuc.channelid = tuic.channelid
		left join 
		(
		SELECT
		`uj`.`appid` AS `appid`,
		`uj`.`sdate` AS
		`sdate`,
		sum(`uj`.`usercount`)AS `tcount`
		FROM
		`tb_userlog_jsok` `uj`
		WHERE
		uj.grade > 0
		GROUP BY
		`uj`.`appid`,
		`uj`.`sdate`
		having
		uj.sdate BETWEEN '20160101' and '20160103'
					
	
		)AS v_uj on v_uj.appid = tuc.channelid and v_uj.sdate = tuc.sdate 
		where
					usc.sdate BETWEEN '20160101' and '20160103'
			
		)c ON
		b.id = c.appid
		
		GROUP BY a.id,c.sdate
		ORDER BY c.sdate DESC