SQL TOP 10 排名查询 需要帮助
我有下面的代码,我试图找出上个月使用的前 10 个服务器,但遇到问题,SQL 不太好。如果可能的话,需要一些帮助或建议。
我在前 10 个应用程序上实现了此功能,但不知道如何在前 10 个服务器上实现此功能。
SELECT TOP 10 dbo_LU_SERVERNAME.SERVERNAME, Count(*) AS SessionNumbers
FROM dbo_LU_SERVERNAME INNER JOIN dbo_SDB_SESSION ON dbo_LU_SERVERNAME.PK_SERVERNAMEID = dbo_SDB_SESSION.FK_SERVERNAMEID
WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-30,Now())))
GROUP BY dbo_LU_SERVERNAME.SERVERNAME
ORDER BY Count(*) DESC;
UNION ALL SELECT "Other" AS SERVERNAME, Count(*) AS SessionNumbers
FROM (dbo_LU_SERVERNAME
INNER JOIN dbo_SDB_SESSION
ON dbo_LU_SERVER.PK_SERVERID = dbo_SDB_SESSION.FK_SERVERID)
LEFT JOIN (SELECT TOP 10 dbo_LU_SERVERNAME.SERVERNAME, Count(*) AS SessionNos
FROM dbo_LU_SERVERNAME
INNER JOIN dbo_SDB_SESSION
ON dbo_LU_SERVER.PK_SERVERID = dbo_SDB_SESSION.FK_SERVERID
WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-31,Now())))
GROUP BY dbo_LU_SERVERNAME.SERVERNAME
ORDER BY Count(*) DESC) AS s ON dbo_LU_SERVERNAME.SERVERNAME = s.SERVERNAME
WHERE s.SERVERNAME Is Null
GROUP BY "Other";
这是适用于排名前 10 位的应用程序的 SQL。
SELECT TOP 10 dbo_LU_APPNAME.APPNAME, Count(*) AS SessionNos
FROM dbo_LU_APPNAME INNER JOIN dbo_SDB_SESSION ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID
WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-30,Now())))
GROUP BY dbo_LU_APPNAME.APPNAME
ORDER BY Count(*) DESC;
UNION ALL SELECT "Other" AS APPNAME, Count(*) AS SessionNos
FROM (dbo_LU_APPNAME
INNER JOIN dbo_SDB_SESSION
ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID)
LEFT JOIN (SELECT TOP 10 dbo_LU_APPNAME.APPNAME, Count(*) AS SessionNos
FROM dbo_LU_APPNAME
INNER JOIN dbo_SDB_SESSION
ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID
WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-31,Now())))
GROUP BY dbo_LU_APPNAME.APPNAME
ORDER BY Count(*) DESC) AS s ON dbo_LU_APPNAME.APPNAME = s.APPNAME
WHERE s.APPNAME Is Null
GROUP BY "Other";
请注意,这些表格的关联如下: dbo_LU_SERVER ---> FK_SERVERNAMEID、PK_SERVERID dbo_LU_SERVERNAME ---> PK_SERVERNAMEID dbo.SDB.SESSION ---> FK_SERVERID
我不确定我做错了什么。
请帮忙。
谢谢
I have the code below and I am trying to find out the top 10 servers used in the last month, but having issues, not very good with SQL. Need some help or advice if possible.
I got this working on top 10 Apps but cannot figure out how to make this happen for top 10 servers.
SELECT TOP 10 dbo_LU_SERVERNAME.SERVERNAME, Count(*) AS SessionNumbers
FROM dbo_LU_SERVERNAME INNER JOIN dbo_SDB_SESSION ON dbo_LU_SERVERNAME.PK_SERVERNAMEID = dbo_SDB_SESSION.FK_SERVERNAMEID
WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-30,Now())))
GROUP BY dbo_LU_SERVERNAME.SERVERNAME
ORDER BY Count(*) DESC;
UNION ALL SELECT "Other" AS SERVERNAME, Count(*) AS SessionNumbers
FROM (dbo_LU_SERVERNAME
INNER JOIN dbo_SDB_SESSION
ON dbo_LU_SERVER.PK_SERVERID = dbo_SDB_SESSION.FK_SERVERID)
LEFT JOIN (SELECT TOP 10 dbo_LU_SERVERNAME.SERVERNAME, Count(*) AS SessionNos
FROM dbo_LU_SERVERNAME
INNER JOIN dbo_SDB_SESSION
ON dbo_LU_SERVER.PK_SERVERID = dbo_SDB_SESSION.FK_SERVERID
WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-31,Now())))
GROUP BY dbo_LU_SERVERNAME.SERVERNAME
ORDER BY Count(*) DESC) AS s ON dbo_LU_SERVERNAME.SERVERNAME = s.SERVERNAME
WHERE s.SERVERNAME Is Null
GROUP BY "Other";
This is the SQL that is working for top 10 APPS.
SELECT TOP 10 dbo_LU_APPNAME.APPNAME, Count(*) AS SessionNos
FROM dbo_LU_APPNAME INNER JOIN dbo_SDB_SESSION ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID
WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-30,Now())))
GROUP BY dbo_LU_APPNAME.APPNAME
ORDER BY Count(*) DESC;
UNION ALL SELECT "Other" AS APPNAME, Count(*) AS SessionNos
FROM (dbo_LU_APPNAME
INNER JOIN dbo_SDB_SESSION
ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID)
LEFT JOIN (SELECT TOP 10 dbo_LU_APPNAME.APPNAME, Count(*) AS SessionNos
FROM dbo_LU_APPNAME
INNER JOIN dbo_SDB_SESSION
ON dbo_LU_APPNAME.PK_APPNAMEID = dbo_SDB_SESSION.FK_APPNAMEID
WHERE (((dbo_SDB_SESSION.SESSIONSTART) Between Now() And DateAdd("d",-31,Now())))
GROUP BY dbo_LU_APPNAME.APPNAME
ORDER BY Count(*) DESC) AS s ON dbo_LU_APPNAME.APPNAME = s.APPNAME
WHERE s.APPNAME Is Null
GROUP BY "Other";
Please note that the tables are related as:
dbo_LU_SERVER ---> FK_SERVERNAMEID, PK_SERVERID
dbo_LU_SERVERNAME ---> PK_SERVERNAMEID
dbo.SDB.SESSION ---> FK_SERVERID
I am not sure what I am doing wrong.
Please Help.
Thank you
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
我设法回答了我的问题。
感谢您的评论,我之前确实问过另一个问题,几乎与此相同,但另一个问题是关于两个相关实体,这是三个相关实体。
再次感谢
干杯:)
I managed to answer my question.
Thanks for your comments, I did ask another question some time before almost the same as this but the other one is regarding two related entities, this is three related entities.
Thanks again
Cheers :)