先上SQL:
with temp(area_code, is_province, target_value) AS
( --筛选出省内地市和省外省的目标数据(is_province 区分省内外)
SELECT area_code, (CASE area_type WHEN '03' THEN 1 ELSE 0 END ), target_value
FROM bit_warn_target
WHERE warn_code = 'WARN_SELL' AND data_year = '2019' AND toba_code = '0535' AND area_code <> '420000'
),
temp2(area_code, is_province, target_value, snw_terget_value) AS
( --省市目标及省内外目标(省内目标为省内地市目标总和,省外目标为省外省目标总和)
SELECT area_code, is_province, target_value,
SUM(target_value) OVER(PARTITION BY is_province) AS snw_terget_value
FROM temp
)
SELECT * FROM temp2 ORDER BY is_province@
结果为:这种结果是对的,省内外目标数据就两种情况
但是问题就是来了 SELECT * FROM temp2 ORDER BY is_province@换成
SELECT * FROM temp2 ORDER BY is_province DESC@
结果就变成了
结果完全不一样了。ORDER 只是对结果集进行排序呀,为什么会影响结果集呢。。。如果ORDER BY是放在OVER里面影响了结果集我还能理解。但是放在最后面影响了结果集完全不能理解。请求各位大佬帮我解释一下。