[SQL]自定义排序依据 [英] [SQL] Custom Order By

问题描述

我有以下SQL查询:

I have the following SQL Query:

SELECT        COUNT(*) AS Expr1
FROM            _Events AS t1 INNER JOIN
                         _EventToClient AS t2 ON t1.EventID = t2.EventID INNER JOIN
                         _Clients AS t3 ON t2.ClientID = t3.ClientID
WHERE        (t3.ClientConnected = 1) AND (t1.EventID = @some_id)

我想从表_Events中选择顶部，同时根据此排序键以降序方式进行排序.我该怎么办?

I would like to SELECT the top from the table _Events, while sorted in a descending manner according to this sorting key. What should I do?

SELECT TOP (@MaxAmount) ... FROM _Events WHERE ... ORDER BY ...

有没有比我更好的方法来做我需要的事情，问题是我无法选择非GROUPED BY数据...

Is there a better way to do what I need than, the problem is that I can''t select non GROUPED BY data...

SELECT        t1.EventID, t1., ..., COUNT(*) AS Key
FROM            _Events AS t1 INNER JOIN
                         _EventToClient AS t2 ON t1.EventID = t2.EventID INNER JOIN
                         _Clients AS t3 ON t2.ClientID = t3.ClientID
WHERE        (t3.ClientConnected = 1)
GROUP BY t1.EventID
ORDER BY Key DESC

推荐答案

创建视图视图名称

create view viewname

as
SELECT        t1.EventID, t1., ..., COUNT(*) AS Key
FROM            _Events AS t1 INNER JOIN
                         _EventToClient AS t2 ON t1.EventID = t2.EventID INNER JOIN
                         _Clients AS t3 ON t2.ClientID = t3.ClientID
WHERE        (t3.ClientConnected = 1)
GROUP BY t1.EventID

然后通过
调用tha view

then call tha view by

select * from viewname order by desc

通过使用veiw，可以解决您的问题.

by using veiw, you can solve your problem..

未经测试.这可能会有所帮助:

Not tested. This might help:

SELECT _Events.*, cte.key
FROM _Events
INNER JOIN (
    SELECT EventID, MAX(Amount) AS MAmount, COUNT(*) AS key
    FROM _Events
    GROUP BY EventID
) AS cte
ON _Events.EventID = cte.EventID

这篇关于[SQL]自定义排序依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！