Distinct后，分页

[zlw0905]

查询数据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)

还请童鞋们帮助我一下

[--小F--]

你这个不是可以吗？

[--小F--]

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) as t

[还在加载中灬]

你参考一下

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

[Tiger_Zhao]

你这个查询有点莫名其妙，难道 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
       )

[中国风]

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
        ) as 表名

楼主的语句少了别名表 as 表名

[zlw0905]

引用 2 楼 fredrickhu 的回复:

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) as t

问题很明显啊，那不是dev_id无效啊 ，子表当中为了用distinct不能取dev_id

[zlw0905]

引用 3 楼 ky_min 的回复:

你参考一下

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

谢谢，在你的基础上给子表加了一个别名能用

[zlw0905]

引用 6 楼 roy_88 的回复:

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
        ) as 表名

楼主的语句少了别名表 as 表名

thank you