以这样一种方式修改sql查询，它将选择不同的记录 [英] modify sql query in such a way that it will select distinct records

问题描述

我正在使用以下sql查询..

i am using the following sql query..

string query = "select * from (select ROW_NUMBER() OVER (ORDER BY style_no) rn ,* from add_product) a where rn BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1";

它会给出类似这样的记录...

it is giving records something like this...

Style_no               Text             Age
JC001                  Text1            15
JC001                  Text2            18
JC001                  Text5            19
JC002                  Text1            80
JC002                  Text8            45
JC002                  Text9            60

但我希望得到如下结果：

But i want results something like this :

JC001                   Text1              15
JC002                   Text1              80

必须仅选择具有相同style_no列的每一行一旦。我希望你明白我在说什么。



i希望以这样的方式修改上面的查询，它应该像它一样工作..只有改变我想要的是它应该只选择一次，其中style_no超过一次。

Each row with same style_no column must be selected only once. i hope you understand what i am saying.

i want to modify above query in such a way that it should work like as it is.. only change i want is that it should select the records only once where style_no is more then once.

推荐答案

SELECT * FROM  (
    SELECT ROW_NUMBER() OVER (ORDER BY style_no) rn, style_no, text, age FROM 
    (
-- take everything and rank it by style_no
        SELECT ROW_NUMBER() OVER (ORDER BY style_no) rank, style_no, text, age
        FROM add_product
    ) ranking
    WHERE rank = 1 -- take only one of those (you didn't specify any ordering)
) paging
where rn BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1;

或者您可以使用SELECT TOP 1 * FROM ....但我认为以上更清晰。

Or you could play with SELECT TOP 1 * FROM ....but I think the above is cleaner.

这篇关于以这样一种方式修改sql查询，它将选择不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！