需要帮助来解决此查询 [英] Need help in resolving this query

问题描述

大家好



我有以下数据

Hi all

I have data like below

Itemcode   itemname   price   dept
1          aa         100     ABC
2          bb         200     DEF
3          cc         300     ABC
4          dd         400     CCA
5          ee         500     DEF
6          ff         600     CCA
7          gg         99      CCA
8          hh         80      ABC
9          ii         114     DEF
10         jj         677     DEF
11         kk         432     CCA
12         ll         223     ABC

i想要每个部门的前三项。



请告诉我怎么做。



谢谢

i want top 3 items from each dept.

Please tell me how to do this.

Thank you

推荐答案

试试这个：

Try this:

--top 3 by itemname
SELECT *
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY dept ORDER BY itemname) AS RowNo, Itemcode, itemname, price, dept
    FROM TableName
) AS T
WHERE RowNo<4

--top 3 by price
SELECT *
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY dept ORDER BY price) AS RowNo, Itemcode, itemname, price, dept
    FROM TableName
) AS T
WHERE RowNo<4

如需了解更多信息，请参阅： ROW_NUMBER（） [< a href =http://msdn.microsoft.com/en-us/library/ms186734.aspxtarget =_ blanktitle =新窗口> ^ ]

你可以试试这个： -



SELECT DISTINCT itemname，price，dept

FROM table_name

ORDER BY itemname，price，dept;

You can try this:-

SELECT DISTINCT itemname,price,dept
FROM table_name
ORDER BY itemname,price,dept;

这篇关于需要帮助来解决此查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！