MS Access 查询 - 忽略“排序依据”条款

发布于 2024-11-01 03:53:53 字数 715 浏览 1 评论 0原文

SELECT u.UserLastName, u.UserID, SUM((Format(c.CallLength, 'h') * 60 *60)) as hourSeconds, SUM((Format(c.CallLength, 'n') * 60)) as minSeconds,
                SUM((Format(c.CallLength, 's'))) as seconds, COUNT(*) as 'callCount'
                FROM Calls AS c INNER JOIN User AS u ON c.UserID = u.UserID
                WHERE c.CallDate BETWEEN format(NOW(), 'yyyy-mm-dd 00:00:00') AND format(Now(), 'yyyy-mm-dd 23:59:59') AND u.UserLastName NOT IN ('Britt','Jason','System')
                GROUP BY u.UserID, u.UserLastName
                ORDER BY 'callCount' DESC;

我一直在尝试不同的技术来使用“ORDER BY”子句对该查询进行排序。什么是不正确的？它只是运行查询，没有错误，但似乎是按 u.UserID 字段排序。无论我做什么，我都无法使用 ORDER BY 子句来对任何字段进行排序！

原文

SELECT u.UserLastName, u.UserID, SUM((Format(c.CallLength, 'h') * 60 *60)) as hourSeconds, SUM((Format(c.CallLength, 'n') * 60)) as minSeconds,
                SUM((Format(c.CallLength, 's'))) as seconds, COUNT(*) as 'callCount'
                FROM Calls AS c INNER JOIN User AS u ON c.UserID = u.UserID
                WHERE c.CallDate BETWEEN format(NOW(), 'yyyy-mm-dd 00:00:00') AND format(Now(), 'yyyy-mm-dd 23:59:59') AND u.UserLastName NOT IN ('Britt','Jason','System')
                GROUP BY u.UserID, u.UserLastName
                ORDER BY 'callCount' DESC;

I've spent forever trying different techniques to sort this query using the "ORDER BY" clause. What is incorrect? It simply runs the query with no errors but seems to sort by the u.UserID field instead. No matter what I do I cannot get the ORDER BY clause to order any field!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

素年丶 2024-11-08 03:53:53

如果您的原始查询返回了您想要的数据而没有错误，并且唯一的问题是 ORDER BY，我认为这个简单的更改是可行的方法。

SELECT
    u.UserLastName,
    u.UserID,
    SUM((Format(c.CallLength, 'h') * 60 *60)) as hourSeconds,
    SUM((Format(c.CallLength, 'n') * 60)) as minSeconds,
    SUM((Format(c.CallLength, 's'))) as seconds,
    COUNT(*) as callCount
FROM Calls AS c INNER JOIN User AS u ON c.UserID = u.UserID
WHERE
    c.CallDate BETWEEN format(NOW(), 'yyyy-mm-dd 00:00:00')
    AND format(Now(), 'yyyy-mm-dd 23:59:59')
    AND u.UserLastName NOT IN ('Britt','Jason','System')
GROUP BY u.UserID, u.UserLastName
ORDER BY 6 DESC;

当您为字段（或表达式）分配别名时，您不能在 ORDER BY 中使用该别名。但是，您可以通过其在字段列表中的顺序位置来引用它。

If your original query returned the data you want without error, and the only problem was the ORDER BY, I think this simple change is the way to go.

SELECT
    u.UserLastName,
    u.UserID,
    SUM((Format(c.CallLength, 'h') * 60 *60)) as hourSeconds,
    SUM((Format(c.CallLength, 'n') * 60)) as minSeconds,
    SUM((Format(c.CallLength, 's'))) as seconds,
    COUNT(*) as callCount
FROM Calls AS c INNER JOIN User AS u ON c.UserID = u.UserID
WHERE
    c.CallDate BETWEEN format(NOW(), 'yyyy-mm-dd 00:00:00')
    AND format(Now(), 'yyyy-mm-dd 23:59:59')
    AND u.UserLastName NOT IN ('Britt','Jason','System')
GROUP BY u.UserID, u.UserLastName
ORDER BY 6 DESC;

When you assign an alias to a field (or expression), you can't use that alias name in the ORDER BY. However you can refer to it by its ordinal position in the field list.

回复收藏 0 原文

云胡 2024-11-08 03:53:53

您不想使用字符串作为列名。

试试这个。

另外，如果我没记错的话，你不能订购和分组。则可以对结果进行排序

Select * from (
    SELECT 
        u.UserLastName, 
        u.UserID, 
        SUM((Format(c.CallLength, 'h') * 60 *60)) as hourSeconds, 
        SUM((Format(c.CallLength, 'n') * 60)) as minSeconds,
        SUM(Format(c.CallLength, 's')) as seconds, 
        COUNT(*) as callCount 
    FROM Calls AS c 
    INNER JOIN User AS u ON c.UserID = u.UserID

    WHERE c.CallDate BETWEEN format(NOW(), 'yyyy-mm-dd 00:00:00') AND 
          format(Now(), 'yyyy-mm-dd 23:59:59') AND 
          u.UserLastName NOT IN ('Britt','Jason','System')
    GROUP BY u.UserID, u.UserLastName
)
ORDER BY callCount DESC;

因此，如果您的列名称因是关键字或多个单词而无法使用，。尝试在它周围加上方括号。

[通话次数]

you dont want to use a string as a column name.

Try this.

also, if I recall correctly, you cant order and group. so a sub select groups, and you can order the results...

Select * from (
    SELECT 
        u.UserLastName, 
        u.UserID, 
        SUM((Format(c.CallLength, 'h') * 60 *60)) as hourSeconds, 
        SUM((Format(c.CallLength, 'n') * 60)) as minSeconds,
        SUM(Format(c.CallLength, 's')) as seconds, 
        COUNT(*) as callCount 
    FROM Calls AS c 
    INNER JOIN User AS u ON c.UserID = u.UserID

    WHERE c.CallDate BETWEEN format(NOW(), 'yyyy-mm-dd 00:00:00') AND 
          format(Now(), 'yyyy-mm-dd 23:59:59') AND 
          u.UserLastName NOT IN ('Britt','Jason','System')
    GROUP BY u.UserID, u.UserLastName
)
ORDER BY callCount DESC;

if Your column name cannot be used because it is a keyword or multiple words. try putting square braces around it.

[callCount]

回复收藏 0 原文

~没有更多了~