如何在查询中多次使用子查询的结果

发布于 2024-11-02 08:36:39 字数 917 浏览 3 评论 0原文

MySQL 查询需要在不同位置获得子查询的结果,如下所示:

SELECT COUNT(*),(SELECT hash FROM sets WHERE ID=1) 
     FROM sets 
     WHERE hash=(SELECT hash FROM sets WHERE ID=1) 
           and XD=2;

有没有办法避免子查询的双重执行(SELECT hash FROM set WHERE ID=1)? 子查询的结果始终返回有效的哈希值。 重要的是主查询的结果还包括 HASH。

首先,我尝试了这样的 JOIN

SELECT COUNT(*), m.hash FROM sets s INNER JOIN sets AS m
     WHERE s.hash=m.hash AND id=1 AND xd=2;

如果 XD=2 与行不匹配,结果是:

+----------+------+
| count(*) | HASH |
+----------+------+
|        0 | NULL | 
+----------+------+

而不是类似(我需要的):

+----------+------+
| count(*) | HASH |
+----------+------+
|        0 | 8115e| 
+----------+------+

有什么想法吗?请告诉我!预先感谢您的帮助。

//编辑: 最后,该查询只需计算表中具有相同哈希值的所有条目,例如 ID=1 且 XD=2 的条目。如果没有行匹配(如果 XD 设置为其他数字,则会发生这种情况),则返回 0 并简单地哈希值。

A MySQL query needs the results of a subquery in different places, like this:

SELECT COUNT(*),(SELECT hash FROM sets WHERE ID=1) 
     FROM sets 
     WHERE hash=(SELECT hash FROM sets WHERE ID=1) 
           and XD=2;

Is there a way to avoid the double execution of the subquery (SELECT hash FROM sets WHERE ID=1)?
The result of the subquery always returns an valid hash value.
It is important that the result of the main query also includes the HASH.

First I tried a JOIN like this:

SELECT COUNT(*), m.hash FROM sets s INNER JOIN sets AS m
     WHERE s.hash=m.hash AND id=1 AND xd=2;

If XD=2 doesn't match a row, the result is:

+----------+------+
| count(*) | HASH |
+----------+------+
|        0 | NULL | 
+----------+------+

Instead of something like (what I need):

+----------+------+
| count(*) | HASH |
+----------+------+
|        0 | 8115e| 
+----------+------+

Any ideas? Please let me know! Thank you in advance for any help.

//Edit:
finally that query only has to count all the entries in an table which has the same hash value like the entry with ID=1 and where XD=2. If no rows matches that (this case happend if XD is set to an other number), so return 0 and simply hash value.

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

扫码二维码加入Web技术交流群

发布评论

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

评论(4

冷血 2024-11-09 08:36:39
SELECT  SUM(xd = 2), hash
FROM    sets
WHERE   id = 1

如果 idPRIMARY KEY (我认为这是因为您正在对其使用单记录查询),那么您只需删除 SUM< /code>:

SELECT  xd = 2 AS cnt, hash
FROM    sets
WHERE   id = 1

更新:

抱歉,您的任务有误。

试试这个:

SELECT  si.hash, COUNT(so.hash)
FROM    sets si
LEFT JOIN
        sets so
ON      so.hash = si.hash
        AND so.xd = 2
WHERE   si.id = 1
SELECT  SUM(xd = 2), hash
FROM    sets
WHERE   id = 1

If id is a PRIMARY KEY (which I assume it is since your are using a single-record query against it), then you can just drop the SUM:

SELECT  xd = 2 AS cnt, hash
FROM    sets
WHERE   id = 1

Update:

Sorry, got your task wrong.

Try this:

SELECT  si.hash, COUNT(so.hash)
FROM    sets si
LEFT JOIN
        sets so
ON      so.hash = si.hash
        AND so.xd = 2
WHERE   si.id = 1
喵星人汪星人 2024-11-09 08:36:39

我通常嵌套如下语句

SELECT  Count(ResultA.Hash2) AS Hash2Count,
        ResultA.Hash1
FROM    (SELECT S.Hash AS Hash2,
                (SELECT s2.hash 
                 FROM   sets AS s2 
                 WHERE  s2.ID = 1) AS Hash1
            FROM sets AS S
            WHERE S.XD = 2) AS ResultA
WHERE ResultA.Hash2 = ResultA.Hash1
GROUP BY ResultA.Hash1

(这是手写的,未经测试,但你应该明白这一点)
Hash1 是您的子查询,一旦嵌套,您可以通过其别名在外部查询中引用它。它使查询变得更大一些,但我不认为这是一个大问题。

I normally nest the statements like the following

SELECT  Count(ResultA.Hash2) AS Hash2Count,
        ResultA.Hash1
FROM    (SELECT S.Hash AS Hash2,
                (SELECT s2.hash 
                 FROM   sets AS s2 
                 WHERE  s2.ID = 1) AS Hash1
            FROM sets AS S
            WHERE S.XD = 2) AS ResultA
WHERE ResultA.Hash2 = ResultA.Hash1
GROUP BY ResultA.Hash1

(this one is hand typed and not tested but you should get the point)
Hash1 is your subquery, once its nested, you can reference it by its alias in the outer query. It makes the query a little larger but I don't see that as a biggy.

不弃不离 2024-11-09 08:36:39

如果我正确理解您想要获取的内容,查询应如下所示:

select count(case xd when 2 then 1 else null end case), hash from sets where id = 1 group by hash

If I understand correctly what you are trying to get, query should look like this:

select count(case xd when 2 then 1 else null end case), hash from sets where id = 1 group by hash
云巢 2024-11-09 08:36:39

我同意其他答案,即 GROUP BY 可能更好,但要回答所提出的问题,以下是如何消除重复:

SELECT COUNT(*), h.hash
     FROM sets, (SELECT hash FROM sets WHERE ID=1) h
     WHERE sets.hash=h.hash
           and sets.ID=1 and sets.XD=2;

I agree with the other answers, that the GROUP BY may be better, but to answer the question as posed, here's how to eliminate the repetition:

SELECT COUNT(*), h.hash
     FROM sets, (SELECT hash FROM sets WHERE ID=1) h
     WHERE sets.hash=h.hash
           and sets.ID=1 and sets.XD=2;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文