欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

同一个表,用sql查询今天和昨天的差值,然后排序

程序员文章站 2022-05-09 17:20:23
...

同一个表,用sql查询今天和昨天的差值,然后排序

SELECT tagId2, yesterday, cha, absSum
FROM (
	SELECT tt.tagId2, tt.yesterday, tt.absSum, tt.cha, rownum AS rn
	FROM (
		SELECT tagId2, yesterday, before_yesterday - yesterday AS cha
			, abs(before_yesterday - yesterday) AS absSum
		FROM (
			SELECT x.tagId1 AS tagId2, SUM(CASE 
					WHEN x.VN = 20180529 THEN x.totalMarkNum1
					ELSE 0
				END) AS yesterday, SUM(CASE 
					WHEN x.VN = 20180528 THEN x.totalMarkNum1
					ELSE 0
				END) AS before_yesterday
			FROM (
				SELECT t.tag_id AS tagId1, t.vn, SUM(t.key_count) AS totalMarkNum1
				FROM bdatag_hist_tag_result_sum t
				WHERE t.org_id LIKE '3%'
					AND t.vn BETWEEN '20180528' AND '20180529'
				GROUP BY t.tag_id, t.vn
			) x
			GROUP BY x.tagId1
		)
		ORDER BY absSum DESC
	) tt
)
WHERE rn <= 10