如何存储MySQL子查询的值并在outter查询的WHERE子句中使用它

I have a MySQL table called 'ad' wich stores a series of ads for a pet ecommerce website. This is the structure of said table:

我有一个名为'ad'的MySQL表,它为宠物电子商务网站存储了一系列广告。这是所述表的结构:

ad:

ID: ad unique id.

ID:广告唯一ID。

User: id of the user who published the ad

用户:发布广告的用户的ID

Animal: the type of animal

动物:动物的类型

Race: the race of the animal

种族:动物的种族

Title

Price: the price of the ad

价格:广告的价格

I need to list ads from a specific user that have a price above the average for the same animal and race combination, I've done it with this query and it works perfect:

我需要列出来自特定用户的广告,其价格高于相同动物和种族组合的平均价格,我已经使用此查询完成了它并且它完美无缺:

SELECT * FROM `ad` AS `out_ad`
WHERE `out_ad`.`user` = {iduser} AND
`out_ad`.`price` > (
     SELECT AVG(`price`) FROM `ad` AS `in_ad`
     WHERE `in_ad`.`user` <> `out_ad`.`user`
     AND `in_ad`.`animal` = `out_ad`.`animal`
     AND `in_ad`.`race` = `out_ad`.`race`
)

As I pointed out above, it works fine, but there is one problem, for every row listed in the outer query I would like to have the average price available as another column. For that I give the inner query an alias and place it in the list of columns I want to fetch:

正如我上面指出的,它工作正常,但有一个问题,对于外部查询中列出的每一行,我希望将平均价格作为另一列。为此,我给内部查询一个别名,并将其放在我想要获取的列的列表中:

SELECT *, ( SELECT AVG(`price`) FROM `ad` AS `in_ad`WHERE `in_ad`.`user` <> out_ad`.`user`
         AND `in_ad`.`animal` = `out_ad`.`animal`
         AND `in_ad`.`race` = `out_ad`.`race`
         ) AS `avg_precio`
FROM `ad` AS `out_ad`
WHERE `out_ad`.`user` = {iduser}

Then again this works fine, but it doesn't compare wich record is above average, I tried using the subquery alias in the WHERE clause of my outer query, but it gives me this MySQL error: #1054 - Unknown column 'avg_price' in 'where clause'

然后再次这样工作正常,但它没有比较记录高于平均水平,我尝试在我的外部查询的WHERE子句中使用子查询别名,但它给了我这个MySQL错误:#1054 - 未知列'avg_price'in 'where子句'

SELECT *, ( SELECT AVG(`price`) FROM `ad` AS `in_ad`WHERE `in_ad`.`user` <> out_ad`.`user`
         AND `in_ad`.`animal` = `out_ad`.`animal`
         AND `in_ad`.`race` = `out_ad`.`race`
         ) AS `avg_price`
FROM `ad` AS `out_ad`
WHERE `out_ad`.`user` = {iduser} AND `out_ad`.`price` > `out_ad`.`avg_price`

Is there anyway that I can store the inner query to return it as a column and use it in a WHERE statement at the same time?

反正我是否可以存储内部查询以将其作为列返回并同时在WHERE语句中使用它?

Thanks for reading!

谢谢阅读!

3 个解决方案

#1

I'd do something that looks monstorous but stay with me a little

我会做一些看起来很棒的东西但是和我待在一起

SELECT
    ad.*,
    ad_avg.price_average
FROM ad
INNER JOIN (
    -- Get the average price per animal
    SELECT
        ad.animal,
        ad.race,
        AVG(ad.price) AS price_average
    FROM ad
    INNER JOIN (
        -- Make sure it only gets ads that the user has selected
        SELECT DISTINCT
            animal,
            race
        FROM ad
        WHERE user = {iduser}
    ) AS ad_usr
    ON ad_usr.animal = ad.animal
    AND ad_usr.family = ad.family
    GROUP BY ad.animal, ad.race
) AS ad_avg
-- Join the averages onto ad
ON ad.animal = ad_avg.animal
AND ad.race = ad_avg.race
AND ad.price > ad_avg.price_average
WHERE ad.user = {iduser}

So, starting at the inside with [QRY1], select the animal/race combinations that the user has ads in so as to limit the ad types that are averaged for performance reasons

因此,从[QRY1]内部开始,选择用户拥有广告的动物/种族组合,以限制因性能原因而平均的广告类型

        SELECT DISTINCT
            animal,
            race
        FROM ad
        WHERE user = {iduser}

Now a level up, [QRY2], this uses the animals and races from [QRY1] and determines the overall average prices for them.

现在升级,[QRY2],它使用来自[QRY1]的动物和种族,并确定它们的总体平均价格。

    SELECT
        ad.animal,
        ad.race,
        AVG(ad.price) AS price_average
    FROM ad
    INNER JOIN (
        [QRY1]
    ) AS ad_usr
    ON ad_usr.animal = ad.animal
    AND ad_usr.family = ad.family
    GROUP BY ad.animal, ad.race

And now back around to the top level query, this joins the above averages table onto the ad table selecting all ads by the desired user, success!

现在回到*查询,这会将上面的平均值表加入到广告表中,选择所需的用户所有广告,成功!

SELECT
    ad.*,
    ad_avg.price_average
FROM ad
INNER JOIN (
    [QRY2]
) AS ad_avg
-- Join the averages onto ad
ON ad.animal = ad_avg.animal
AND ad.race = ad_avg.race
AND ad.price > ad_avg.price_average
WHERE ad.user = {iduser}

And just because I'm in that type of mood

只是因为我处于那种情绪中

I haven't tried the below query but in theory it should do much the same however requires userid to be specified only once, and joins the innermost [QRY1] select onto a tuple in the [QRY2] WHERE statement. It may be worth giving it a try in case it is more efficient, hard to tell without testing against your dataset

我没有尝试过以下的查询,但理论上它应该做很多相同但是只需要指定userid一次,并将最里面的[QRY1] select连接到[QRY2] WHERE语句中的元组。如果没有针对您的数据集进行测试,它可能值得尝试,以防它更有效,很难说

SELECT
    ad.*,
    ad_avg.price_average
FROM ad
INNER JOIN (
    SELECT
        ad.animal,
        ad.race,
        AVG(ad.price) AS price_average
    FROM ad
    WHERE (ad.animal,ad.race) IN (
        SELECT DISTINCT
            ad.animal,
            ad.race
        FROM ad,(SELECT @iduser := {iduser}) v
        WHERE ad.user = @iduser
    )
    GROUP BY ad.animal,ad.race
) AS ad_avg
ON (ad.animal,ad.race) = (ad_avg.animal,ad_avg.race)
AND ad.price > ad_avg.price_average
WHERE ad.user = @iduser

#2

You can also wrap the query into an external one and move the problematic condition out there:

您还可以将查询包装到外部查询中并将有问题的条件移出:

SELECT *
FROM
  ( SELECT *, ( SELECT AVG(`precio`) 
                FROM `ad` AS `in_ad`
                WHERE `in_ad`.`user` <> out_ad`.`user`
                  AND `in_ad`.`animal` <> `out_ad`.`animal`
                  AND `in_ad`.`race` <> `out_ad`.`race`
             ) AS `avg_price`
    FROM `ad` AS `out_ad`
    WHERE `out_ad`.`user` = {iduser} 
  ) AS as
WHERE  price > avg_price

#3

First of all, from the logic point of view, I would suggest to remove in_ad`.`user` <> `out_ad`.`user condition when calculating AVG(price).

首先,从逻辑的角度来看,我建议在计算AVG(价格)时删除in_ad``user` <>`out_ad` .user条件。

If one user's price is higher than average price (calculated including this user), then of cause her price is higher than average price when calculated without this user. Vice Versa.

如果一个用户的价格高于平均价格(包括此用户计算),那么因为在没有该用户的情况下计算她的价格高于平均价格。反之亦然。

After you remove this condition, you can even create a table by:

删除此条件后,您甚至可以通过以下方式创建表:

create table avgprice as
  select animal, race, AVG(price) from ad group by animal, race

then your can can easily join this avgprice table for your query.

那么你的罐头可以很容易地加入这个avgprice表来查询。