数据库多表连接查询（Select Left join Group by ）

最新推荐文章于 2024-07-05 09:20:50 发布

唐林

最新推荐文章于 2024-07-05 09:20:50 发布

阅读量7.1k

点赞数 1

分类专栏： SQL 文章标签： SQL

本文链接：https://blog.csdn.net/wumuzhizi/article/details/80175995

版权

SQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SELECT
	*
FROM
	(
		SELECT DISTINCT
			ccrh.BLNO,
			ccrh.clientseqno,
			ccrh. ID AS customsid,
			ccrh.TRANSPORTTOOLNAME,
			ccrh.VOYAGENO,
			counttb.boxcount AS boxcount,
			countarrive.arriveboxcount as carrivstate,
			countarrive.CONTASUM as contasum,
			ccrh.STATUS,
			ccrh.ediStatus,
			ccrh.cdydResult,
			ccrh.CREATEDATE,
			ccrh.SYSCOMPANYID
		FROM
			C_CUSTOMS_RECORD_HEAD ccrh
		LEFT JOIN (
			SELECT
				billno,
				customsid,
				COUNT (DISTINCT CONTAID) AS boxcount
			FROM
				C_RPC_MANIFEST_BAK
			WHERE
				delflag NOT LIKE '%1'
			AND delflag NOT LIKE '1%'
			GROUP BY
				billno,
				customsid
		) counttb ON counttb.customsid = ccrh.id
		LEFT JOIN (
			SELECT
				billno,
				customsid,
				CONTASUM,
				COUNT (DISTINCT CONTANO) AS arriveboxcount
			FROM
				C_RPC_MANIFEST_ARRIVE
			WHERE
				contaarravlmark = '20'
			AND contaarravlmark = '20'
			GROUP BY
				billno,
				CONTASUM,
				customsid


		) countarrive ON countarrive.customsid = ccrh.id
		WHERE
			(
				ccrh.ediStatus != 'R'
				OR ccrh.ediStatus IS NULL
			)
		AND ccrh.status != '0'
		AND ccrh.blno IS NOT NULL
		AND ccrh.clientseqno IS NOT NULL
	)
where blno = 'OOLU2598215680'
ORDER BY
	CREATEDATE DESC