read the f*cking source code

最新推荐文章于 2019-07-15 08:07:44 发布

槛外之人

最新推荐文章于 2019-07-15 08:07:44 发布

阅读量670

点赞数

CC 4.0 BY-SA版权

分类专栏： MSSQL

本文链接：https://blog.youkuaiyun.com/lzhp84/article/details/51837272

MSSQL 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一个复杂的 SQL 查询案例，通过使用子查询、联合查询、窗口函数等高级特性来筛选和展示特定供应商的审计日期数据，并排除了指定日期之前已存在的供应商记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
	*
FROM
	(
		SELECT
			ID = row_number () OVER (
				partition BY SUPPLIERNAME
				ORDER BY
					AUDITDATE
			),
			t.*
		FROM
			(
				SELECT
					gys.SUPPLIERNAME,dd.poid,
					dd.AUDITDATE,
					ht.salesman
				FROM
					IV_T_SUPPLIER gys,
					sm_t_spc ht,
					SM_T_SP_DETAIL mx,
					SM_T_POC dd
				WHERE
					gys.SUPPLIERID = ht.supplierid
				AND ht.soid = mx.soid
				AND mx.POID = dd.POID
				AND dd.AUDITDATE > ''
				UNION
					SELECT
						gys.SUPPLIERNAME,dd.poid,
						dd.AUDITDATE,
						ht.salesman
					FROM
						PDERP.dbo.IV_T_SUPPLIER gys,
						PDERP.dbo.sm_t_spc ht,
						PDERP.dbo.SM_T_SP_DETAIL mx,
						PDERP.dbo.SM_T_POC dd
					WHERE
						gys.SUPPLIERID = ht.supplierid
					AND ht.soid = mx.soid
					AND mx.POID = dd.POID
					AND dd.AUDITDATE > ''
			) t
		WHERE
			t.auditdate > '2016-03-15'
		AND t.auditdate <= '2016-06-15'
		AND t.SUPPLIERNAME NOT IN (
			SELECT
				tt.SUPPLIERNAME
			FROM
				(
					SELECT
						gys.SUPPLIERNAME,
						dd.AUDITDATE
					FROM
						IV_T_SUPPLIER gys,
						sm_t_spc ht,
						SM_T_SP_DETAIL mx,
						SM_T_POC dd
					WHERE
						gys.SUPPLIERID = ht.supplierid
					AND ht.soid = mx.soid
					AND mx.POID = dd.POID
					UNION
						SELECT
							gys.SUPPLIERNAME,
							dd.AUDITDATE
						FROM
							PDERP.dbo.IV_T_SUPPLIER gys,
							PDERP.dbo.sm_t_spc ht,
							PDERP.dbo.SM_T_SP_DETAIL mx,
							PDERP.dbo.SM_T_POC dd
						WHERE
							gys.SUPPLIERID = ht.supplierid
						AND ht.soid = mx.soid
						AND mx.POID = dd.POID
				) tt
			WHERE
				tt.auditdate <= '2016-03-15'
		)
	) t2
WHERE
	t2.ID = 1
ORDER BY
	suppliername