在同一个mysql查询中多次使用一个子查询结果

How can we use result set of one subquery multiple times in the same query

我们如何在同一查询中多次使用一个子查询的结果集

SELECT
(
    SELECT
        COUNT(joa.id)
    FROM
        game_applied joa
    INNER JOIN (
        SELECT
            jrmm.id
        FROM
            game_refer_to_member jrmm
        JOIN game_refer jrr ON jrr.id = jrmm.rid
        AND jrr.referby_user_id = 2551
        AND jrmm. STATUS = '1'
    ) be ON joa.referred_by = be.id
) AS applicationcount,
(
    SELECT
        COUNT(joa.id)
    FROM
        game_applied joa
    INNER JOIN (
        SELECT
            jrmm.id
        FROM
            game_refer_to_member jrmm
        JOIN game_refer jrr ON jrr.id = jrmm.rid
        AND jrr.referby_user_id = 2551
        AND jrmm. STATUS = '1'
    ) bf ON joa.referred_by = bf.id
    AND joa.admin_review = '3'
    AND joa.rejection_reason = 'Admin rejected your game'
) AS admin_reject,
(
    SELECT
        COUNT(joa.id)
    FROM
        game_applied joa
    INNER JOIN (
        SELECT
            jrmm.id
        FROM
            game_refer_to_member jrmm
        JOIN game_refer jrr ON jrr.id = jrmm.rid
        AND jrr.referby_user_id = 2551
        AND jrmm. STATUS = '1'
    ) bg ON joa.referred_by = bg.id
    AND joa. STATUS = '5'
    AND joa.admin_review = '2'
) AS employer_reject,
(
    SELECT
        COUNT(joa.id)
    FROM
        game_applied joa
    INNER JOIN (
        SELECT
            jrmm.id
        FROM
            game_refer_to_member jrmm
        JOIN game_refer jrr ON jrr.id = jrmm.rid
        AND jrr.referby_user_id = 2551
        AND jrmm. STATUS = '1'
    ) bd ON joa.referred_by = bd.id
    AND joa.admin_review = '1'
) AS admin_review,
(
    SELECT
        COUNT(joa.id)
    FROM
        game_applied joa
    INNER JOIN (
        SELECT
            jrmm.id
        FROM
            game_refer_to_member jrmm
        JOIN game_refer jrr ON jrr.id = jrmm.rid
        AND jrr.referby_user_id = 2551
        AND jrmm. STATUS = '1'
    ) bc ON joa.referred_by = bc.id
    AND joa.admin_review = '5'
) AS accountmanager_review,
(
    SELECT
        COUNT(joa.id)
    FROM
        game_applied joa
    INNER JOIN (
        SELECT
            jrmm.id
        FROM
            game_refer_to_member jrmm
        JOIN game_refer jrr ON jrr.id = jrmm.rid
        AND jrr.referby_user_id = 2551
        AND jrmm. STATUS = '1'
    ) ba ON joa.referred_by = ba.id
    AND joa.admin_review = '6'
) AS rp_review,
(
    SELECT
        COUNT(joa.id)
    FROM
        game_applied joa
    INNER JOIN (
        SELECT
            jrmm.id
        FROM
            game_refer_to_member jrmm
        JOIN game_refer jrr ON jrr.id = jrmm.rid
        AND jrr.referby_user_id = 2551
        AND jrmm. STATUS = 1
    ) bh ON joa.referred_by = bh.id
    AND joa.admin_review = '2'
    AND (
        joa. STATUS = '' || joa. STATUS = 1 || joa. STATUS = 2 || joa.    STATUS = 3 || joa. STATUS = 4
    )
) AS other_status 
  FROM
game_applied ja
JOIN user_user u ON u.id = ja.applied_recruiter_id
INNER JOIN (
SELECT
    jrmm.id
FROM
    game_refer_to_member jrmm
    JOIN game_refer jrr ON jrr.id = jrmm.rid
AND jrr.referby_user_id = 2551
AND jrmm. STATUS = '1'
) bn ON ja.referred_by = bn.id
GROUP BY
applicationcount

How can we use result set of one subquery multiple times in the same query

我们如何在同一查询中多次使用一个子查询的结果集

the sub query used mulitple times in this query to a single use

子查询在此查询中多次使用一次

(
SELECT
    jrmm.id
FROM
    game_refer_to_member jrmm
JOIN game_refer jrr ON jrr.id = jrmm.rid
AND jrr.referby_user_id = 2551
AND jrmm. STATUS = '1'
  ) bn ON ja.referred_by = bn.id

2 个解决方案

#1

OK... I'll try to figure out what the query does first. I'll replace all instances of:

好的......我会试着先弄清楚查询的作用。我将替换所有的实例：

SELECT
    COUNT(joa.id)
FROM
    game_applied joa
INNER JOIN (
    SELECT
        jrmm.id
    FROM
        game_refer_to_member jrmm
    JOIN game_refer jrr ON jrr.id = jrmm.rid
    AND jrr.referby_user_id = 2551
    AND jrmm. STATUS = '1'

with the phrase:

短语：

( SELECT count(*) FROM joa JOIN (subselect))

...for clarity.

......为了清楚起见。

I also removed the GROUP BY, since it is useless and/or misguided, unless you can explain why it is there.

我也删除了GROUP BY，因为它是无用的和/或误导的，除非你能解释它为什么存在。

I assume ja.applied_recruiter_id is a foreign key, which means...

我假设ja.applied_recruiter_id是一个外键，这意味着......

JOIN user_user u ON u.id = ja.applied_recruiter_id

...always returns one row. Since no columns from user_user are actually selected, this join can be removed. Now, this part:

...总是返回一行。由于实际上没有选择user_user中的列，因此可以删除此连接。现在，这部分：

    INNER JOIN (
        SELECT
            jrmm.id
        FROM
            game_refer_to_member jrmm
            JOIN game_refer jrr ON jrr.id = jrmm.rid
        AND jrr.referby_user_id = 2551
        AND jrmm. STATUS = '1'
    ) bn ON ja.referred_by = bn.id

...it is unclear what this does. Since the subselect is the same as the one in the previous queries, it is unlikely that it would filter lines returned by the whole query. I'd say its only effect is to uselessly duplicate lines, which explains why there was a GROUP BY... So, off it goes.

......目前还不清楚这是做什么的。由于子选择与先前查询中的子选择相同，因此它不太可能过滤整个查询返回的行。我会说它唯一的影响是无用的复制线，这就解释了为什么有一个GROUP BY ...所以，关闭它。

We get:

我们得到：

SELECT
( SELECT count(*) FROM joa JOIN (subselect)) be ON joa.referred_by = be.id ) AS applicationcount,
( SELECT count(*) FROM joa JOIN (subselect)) bf ON joa.referred_by = bf.id
    AND joa.admin_review = '3'
    AND joa.rejection_reason = 'Admin rejected your game'
) AS admin_reject,
( SELECT count(*) FROM joa JOIN (subselect)) bg ON joa.referred_by = bg.id
    AND joa. STATUS = '5'
    AND joa.admin_review = '2'
) AS employer_reject,
( SELECT count(*) FROM joa JOIN (subselect)) bd ON joa.referred_by = bd.id
    AND joa.admin_review = '1'
) AS admin_review,
( SELECT count(*) FROM joa JOIN (subselect)) bc ON joa.referred_by = bc.id
    AND joa.admin_review = '5'
) AS accountmanager_review,
( SELECT count(*) FROM joa JOIN (subselect)) ba ON joa.referred_by = ba.id
    AND joa.admin_review = '6'
) AS rp_review,
( SELECT count(*) FROM joa JOIN (subselect)) bh ON joa.referred_by = bh.id
    AND joa.admin_review = '2'
    AND (joa. STATUS = '' || joa. STATUS = 1 || joa. STATUS = 2 || joa.    STATUS = 3 || joa. STATUS = 4)
) AS other_status 
  FROM
game_applied ja

...And, using the same logic as Sarhash, we simplify this into:

...并且，使用与Sarhash相同的逻辑，我们将其简化为：

SELECT COUNT(joa.id) AS applicationcount, 
     SUM(joa.admin_review = '3' AND joa.rejection_reason = 'Admin Rejected your resume') AS admin_reject, 
     SUM(joa.STATUS = '5' AND joa.admin_review = '2') AS employer_reject, 
     SUM(joa.admin_review = '1') AS admin_review, 
     SUM(joa.admin_review = '5') AS accountmanager_review, 
     SUM(joa.admin_review = '6') AS rp_review, 
     SUM(joa.admin_review = '2' AND joa.STATUS != '5') AS other_status, 
FROM game_refer_to_member jrmm 
INNER JOIN game_refer jrr ON jrr.id = jrmm.rid 
INNER JOIN game_applied joa ON jrmm.id  = joa.referred_by  
WHERE jrmm.STATUS = '1' AND jrr.referby_user_id = 2551

(which is the same, minus the useless join to USER and a cleanup in the WHERE, thank you Sarhash, you get all the credit).

（这是相同的，减去对USER的无用连接和WHERE中的清理，谢谢你Sarhash，你得到所有的功劳）。

#2

Try this:

尝试这个：

SELECT COUNT(joa.id) AS applicationcount, 
         SUM(joa.admin_review = '3' AND joa.rejection_reason = 'Admin Rejected your resume') AS admin_reject, 
         SUM(joa.STATUS = '5' AND joa.admin_review = '2') AS employer_reject, 
         SUM(joa.admin_review = '1') AS admin_review, 
         SUM(joa.admin_review = '5') AS accountmanager_review, 
         SUM(joa.admin_review = '6') AS rp_review, 
         SUM(joa.admin_review = '2' AND joa.STATUS != '5') AS other_status, 
FROM game_refer_to_member jrmm 
INNER JOIN game_refer jrr ON jrr.id = jrmm.rid AND jrr.referby_user_id = 2551 
INNER JOIN game_applied joa ON jrmm.id  = joa.referred_by  
INNER JOIN user_user u ON u.id = joa.applied_recruiter_id
WHERE jrmm.STATUS = '1'