SQL 具有不在 SELECT 中的列

发布于 2024-11-24 14:57:31 字数 823 浏览 0 评论 0原文

我有一个包含 3 列的表：

userid   mac_address   count

一个用户的条目可能如下所示：

57193   001122334455   42
57193   000C6ED211E6   15
57193   FFFFFFFFFFFF   2

我想创建一个视图，仅显示被视为该用户“常用”的那些 MAC。例如，我想过滤掉与该用户最常用的 MAC 地址相比使用量小于 10% 的 MAC。此外，我想要每个用户 1 行。这可以通过 GROUP BY、HAVING 和 GROUP BY 轻松实现。 GROUP_CONCAT：

SELECT userid, GROUP_CONCAT(mac_address SEPARATOR ',') AS macs, count
FROM mactable
GROUP BY userid
HAVING count*10 >= MAX(count)

事实上，结果如下：

57193   001122334455,000C6ED211E6   42

但是我真的不希望在我看来计数列。但是，如果我将其从 SELECT 语句中取出，则会出现以下错误：

#1054 - Unknown column 'count' in 'having clause'

有没有什么方法可以执行此操作，而不会被迫在我的视图中包含令人讨厌的计数列？我知道我可能可以使用内部查询来做到这一点，但出于性能原因我想避免这样做。

非常感谢您的帮助！

原文

I have a table with 3 columns:

userid   mac_address   count

The entries for one user could look like this:

57193   001122334455   42
57193   000C6ED211E6   15
57193   FFFFFFFFFFFF   2

I want to create a view that displays only those MAC's that are considered "commonly used" for this user. For example, I want to filter out the MAC's that are used <10% compared to the most used MAC-address for that user. Furthermore I want 1 row per user. This could easily be achieved with a GROUP BY, HAVING & GROUP_CONCAT:

SELECT userid, GROUP_CONCAT(mac_address SEPARATOR ',') AS macs, count
FROM mactable
GROUP BY userid
HAVING count*10 >= MAX(count)

And indeed, the result is as follows:

57193   001122334455,000C6ED211E6   42

However I really don't want the count-column in my view. But if I take it out of the SELECT statement, I get the following error:

#1054 - Unknown column 'count' in 'having clause'

Is there any way I can perform this operation without being forced to have a nasty count-column in my view? I know I can probably do it using inner queries, but I would like to avoid doing that for performance reasons.

Your help is very much appreciated!

分享到QQ

分享到微博

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

发布评论

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

简单爱 2024-12-01 14:57:31

由于 HAVING 显式引用选择列表中的列名称，因此不可能是您想要的。
但是，您可以将您的选择用作仅返回您想要的行的选择的子选择。

SELECT a.userid, a.macs
FROM
(
    SELECT userid, GROUP_CONCAT(mac_address SEPARATOR ',') AS macs, count
    FROM mactable
    GROUP BY userid
    HAVING count*10 >= MAX(count)
) as a

更新：
由于 MySQL 的限制，这是不可能的，尽管它可以在 Oracle 等其他 DBMS 中工作。
一种解决方案是为子查询创建一个视图。另一个解决方案似乎更清晰：

CREATE VIEW YOUR_VIEW (userid, macs) AS
SELECT userid, GROUP_CONCAT(mac_address SEPARATOR ',') AS macs, count
FROM mactable
GROUP BY userid
HAVING count*10 >= MAX(count)

这会将视图声明为仅返回列 userid 和 macs，尽管底层的 SELECT 语句返回的列比这两个列多.
虽然我不确定非DBMS MySQL是否支持这个...

As HAVING explicitly refers to the column names in the select list, it is not possible what you want.
However, you can use your select as a subselect to a select that returns only the rows you want to have.

SELECT a.userid, a.macs
FROM
(
    SELECT userid, GROUP_CONCAT(mac_address SEPARATOR ',') AS macs, count
    FROM mactable
    GROUP BY userid
    HAVING count*10 >= MAX(count)
) as a

UPDATE:
Because of a limitation of MySQL this is not possible, although it works in other DBMS like Oracle.
One solution would be to create a view for the subquery. Another solution seems cleaner:

CREATE VIEW YOUR_VIEW (userid, macs) AS
SELECT userid, GROUP_CONCAT(mac_address SEPARATOR ',') AS macs, count
FROM mactable
GROUP BY userid
HAVING count*10 >= MAX(count)

This will declare the view as returning only the columns userid and macs although the underlying SELECT statement returns more columns than those two.
Although I am not sure, whether the non-DBMS MySQL supports this or not...

回复收藏 0 原文

~没有更多了~