gpt4 book ai didi

java - MySQL 查询 "select top 5"查询

转载 作者:太空宇宙 更新时间:2023-11-03 12:34:49 27 4
gpt4 key购买 nike

我有一个查询要“转换”为 mysql。这是查询:

select top 5 * 
from
(select id, firstName, lastName, sum(fileSize) as TotalBytes, sum(fileSize)/count(b.*) as Average
from roster_cs368 a
join htmp_cs3868 b on b.id = a.id
)
union
(
select id, firstName, lastName, sum(fileSize) as TotalBytes, sum(fileSize)/count(b.*) as Average
from roster_cs368 a
join atmp_cs3868 b on b.id = a.id
)
order by TotalBytes desc

我希望有人能告诉我“mysql: 做这件事的方法。我真的很感激。这是我正在做的 Java 程序的一部分,到目前为止还不太熟悉 sql 查询。

更新:

mysql> select * from roster_cs368
-> ;
+--------+-----------+-----------+
| id | firstName | lastName |
+--------+-----------+-----------+
| apn7cf | Allen | Newton |
| atggg3 | andrew | goebel |
| aysfgd | Alfred | Santos |
| cdq6c | chris | declama |

其中“id”是主键

mysql> select * from htmp_cs368;
+------------+----------+------------+----------+----------+-------+------+-------+----------------------+
| filePerms | numLinks | id | idGroup | fileSize | month | day | time | fileName |
+------------+----------+------------+----------+----------+-------+------+-------+----------------------+
| drwx------ | 2 | schulte | faculty | 289 | Nov | 7 | 2011 | Java |
| -rw-r--r-- | 1 | schulte | faculty | 136 | Apr | 29 | 2012 | LD |
| drwxr-xr-x | 3 | schulte | faculty | 177 | Mar | 20 | 2012 | Upgrade |

这里没有主键,最后一张表:

mysql> select * from atmp_cs368;
+------------+----------+--------------+----------+----------+-------+------+-------+-----------------------------+
| filePerms | numLinks | id | idGroup | fileSize | month | day | time | fileName |
+------------+----------+--------------+----------+----------+-------+------+-------+-----------------------------+
| drwxr-xr-x | 2 | remierm | 203 | 245 | Sep | 17 | 14:40 | 148360_sun_studio_12 |
| drwx---rwx | 31 | antognolij | sasl | 2315 | Oct | 24 | 12:28 | 275 |
| -rwx------ | 1 | kyzvdb | student | 36 | Sep | 19 | 13:05 | 275hh |
| drwx---rwx | 26 | antognolij | sasl | 1683 | Nov | 12 | 14:00 | 401 |

这里也没有主键。

我要回答的问题是:

produce a list of the five members of roster_cs368
and their ids who use the most space (number of bytes)
in htmp_cs368 and atmp_cs368 in descending order--
greediest first.

希望这些信息对您有所帮助。请注意,随后显示的表格中包含更多信息。这里只是一个片段。

最佳答案

根据 SELECT Syntax 记录:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.

因此:

(
SELECT id,
firstName,
lastName,
SUM(fileSize) AS TotalBytes,
SUM(fileSize)/COUNT(*) AS Average
FROM roster_cs368 AS a
JOIN htmp_cs3868 AS b USING (id)
) UNION (
SELECT id,
firstName,
lastName,
SUM(fileSize) AS TotalBytes,
SUM(fileSize)/COUNT(*) AS Average
FROM roster_cs368 AS a
JOIN atmp_cs3868 AS b USING (id)
)
ORDER BY TotalBytes DESC
LIMIT 5

请注意 idfirstNamelastNamehidden columns ,除非每条记录都相同,否则其值是不确定的。

关于java - MySQL 查询 "select top 5"查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13671367/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com