查询数据SQL:
select * from ep_user where ep_code='USER_EP_051' and ep_val is not null
效果：
去除重复数据SQL:
select distinct(ep_val),ep_code from ep_user where ep_code='USER_EP_051'
and ep_val is not null
效果：
我想用Row_Number()来分页，可是在distinct的时候，不可以取dev_id
select ROW_NUMBER() over(order by dev_id) as num,ep_code,ep_val
from (select distinct(ep_val),ep_code from ep_user where ep_code='USER_EP_051' and ep_val is not null)
还请童鞋们帮助我一下
------解决思路----------------------
你参考一下
select ROW_NUMBER() over(order by dev_id) as num,ep_code,ep_val
from (SELECT ROW_NUMBER()OVER(PARTITION BY ep_val,ep_code order by dev_id)RN,dev_id,ep_val,ep_code from ep_user where ep_code='USER_EP_051' and ep_val is not null)
WHERE RN=1
------解决思路----------------------
SELECT ROW_NUMBER() OVER ( ORDER BY dev_id ) AS num
,ep_code
,ep_val
FROM ( SELECT ep_val
,ep_code
,[dev_id] = MAX([dev_id])
FROM ep_user
WHERE ep_code = 'USER_EP_051'
AND ep_val IS NOT NULL
GROUP BY ep_val
,ep_code
)
用GROUP BY
------解决思路----------------------
你这个查询有点莫名其妙，难道 ep_val 没有自己的字典，这样的排序没有确定性。
select ROW_NUMBER() over(order by dev_id) as num,
ep_code,
ep_val
from (
select ep_val,
ep_code,
min(dev_id) dev_id
from ep_user
where ep_code='USER_EP_051'
and ep_val is not null
group by ep_val, ep_code
)