从mysql到ms sql 2000，按、按、按顺序进行模拟

I have a query in MySQL:

我在MySQL中有一个查询:

select slscod,slsname,brc
from td_casa
group by slscod
order by slsname
limit 0,100

This query retrieves the top 100 unique sales codes with slsname and brc, ordered by slsname ascending.

该查询使用slsname和brc检索前100个唯一的销售代码，按slsname升序排序。

How to change this query in MSSQL 2000?

如何在MSSQL 2000中更改此查询?

3 个解决方案

#1

The basic syntax is:

基本语法:

select top 100 slscod,slsname,brc 
from td_casa 
group by slscod 
order by slsname

#2

SELECT TOP 100 slscod, slsname, brc
FROM td_casa
GROUP BY slscod, slsname, brc
ORDER BY slsname

Note: regarding your comment about Column 'xyz' is invalid in the select ... error I noticed that you were selecting 3 columns but specified 1 column in the GROUP BY clause. This is a MySQL specific behavior as described here:

注意:关于你对“xyz”一栏的评论，在select中无效。我注意到您选择了3列，但在GROUP BY子句中指定了1列。这是这里描述的特定于MySQL的行为:

MySQL extends the use of GROUP BY to permit selecting fields that are not mentioned in the GROUP BY clause. If you are not getting the results that you expect from your query, please read the description of GROUP BY found in Section 11.15, "Functions and Modifiers for Use with GROUP BY Clauses".

MySQL扩展了GROUP的使用，允许选择GROUP BY子句中没有提到的字段。如果您没有从查询中获得预期的结果，请阅读第11.15节“用于GROUP BY子句的函数和修饰符”中对组的描述。

#3

because in MSSQL 2000 does not support the ROW_NUMBER() and LIMIT ... OFFSET .. finally, i found this query :

因为在MSSQL 2000中不支持ROW_NUMBER()并限制…抵消。最后，我找到了这个查询:

SELECT slscod, MIN(slsname) slsname, MIN(brc) brc FROM (
    SELECT top 30 slscod, MIN(slsname) slsname, MIN(brc) brc FROM
    (
        SELECT TOP (1*30) slscod, MIN(slsname) slsname, MIN(brc) brc
        FROM td_casa group by slscod
        ORDER BY slsname ASC
    ) AS t1 group by slscod
     ORDER BY slsname DESC ) AS t2 group by slscod ORDER BY MIN(slsname) ASC

This is the same if in mysql :

在mysql中也是一样的:

select slscod,slsname,brc from td_casa group by slscod order by slsname limit 0,30

if you change TOP (2*30) this is the same limit 30,30. TOP (3*30) this is the same limit 60,30 and so on.

如果你改变顶部(2*30)这是相同的极限。上面(3*30)这是相同的极限，60 30，等等。

desperate need of effort. thanks all.let's cheers

迫切需要的努力。谢谢所有。让我们干杯

#1