logo标签列表

MySQL子查询中的未知列

mysqlsqljoinsubquery
10

我试图获取一个项目的平均值，因此我正在使用子查询。

更新：我最初应该更清楚，但我只想要最后5个项目的平均值

首先我从以下内容开始：

SELECT 
y.id
FROM (
    SELECT *
        FROM (
                SELECT *
                FROM products
                WHERE itemid=1
        ) x  
    ORDER BY id DESC
    LIMIT 15 
) y;

这个程序可以运行，但它只显示 ID，相当无用。

然后我添加了以下内容:

SELECT
y.id,
(SELECT AVG(deposit) FROM (SELECT deposit FROM products WHERE id < y.id ORDER BY id DESC LIMIT 5)z) AVGDEPOSIT
FROM (
    SELECT *
        FROM (
                SELECT *
                FROM products
                WHERE itemid=1
        ) x  
    ORDER BY id DESC
    LIMIT 15 
) y;

当我这样做时，我遇到了错误'where clause'中的未知列'y.id'，在进一步阅读后，我认为这是因为查询下降到下一个级别时需要连接？

所以我尝试了下面的操作**移除了不必要的子查询

SELECT
y.id,
(SELECT AVG(deposit) FROM (
    SELECT deposit 
    FROM products
    INNER JOIN y as yy ON products.id = yy.id       
    WHERE id < yy.id 
    ORDER BY id DESC 
    LIMIT 5)z
    ) AVGDEPOSIT
FROM (
    SELECT *
    FROM products
    WHERE itemid=1
    ORDER BY id DESC
    LIMIT 15 
) y;

但是我得到了表'test.y'不存在的错误信息。我是否在正确的轨道上？我需要做什么更改才能得到我想要的结果呢？

可以在这里的 sqlfiddle 找到示例。

CREATE TABLE products
    (`id` int, `itemid` int, `deposit` int);

    INSERT INTO products
    (`id`, `itemid`, `deposit`)
VALUES
(1, 1, 50),
(2, 1, 75),
(3, 1, 90),
(4, 1, 80),
(5, 1, 100),
(6, 1, 75),
(7, 1, 75),
(8, 1, 90),
(9, 1, 90),
(10, 1, 100);

给定这个例子中的数据，我的预期结果如下，每个ID旁边都有一列，其中包含前5次存款的平均值。

id | AVGDEPOSIT
10 | 86 (deposit value of (id9+id8+id7+id6+id5)/5) to get the AVG
 9 | 84
 8 | 84
 7 | 84
 6 | 79
 5 | 73.75
你能否发布你的表结构？ - Sajuna Fernando
@SajunaFernando，我已经编辑了问题并添加了sqlfiddle。 - ak85
请问您能否在问题中添加一下，您希望在fiddle中插入的数据得到什么样的预期结果？ - mavrosxristoforos
请用英语准确描述您正在尝试做什么。 - Bohemian
8个回答
6

我不是MySQL专家（在MS SQL中可能更容易），你的问题对我来说有点不清楚，但看起来像是你试图得到前五项的平均值。

如果你的Id没有间隙，这很容易：

select
    p.id,
    (
        select avg(t.deposit)
        from products as t
        where t.itemid = 1 and t.id >= p.id - 5 and t.id < p.id
    ) as avgdeposit
from products as p
where p.itemid = 1
order by p.id desc
limit 15

如果不是这样，那么我尝试过像这样查询
select
    p.id,
    (
        select avg(t.deposit)
        from (
            select tt.deposit
            from products as tt
            where tt.itemid = 1 and tt.id < p.id
            order by tt.id desc
            limit 5
        ) as t
    ) as avgdeposit
from products as p
where p.itemid = 1
order by p.id desc
limit 15

但是我遇到了异常在where子句中，未知的列'p.id'。看起来MySQL不能处理两层嵌套的子查询。 但是你可以通过使用offset来获取前五个元素，例如：

select
    p.id,
    (
        select avg(t.deposit)
        from products as t
        where t.itemid = 1 and t.id > coalesce(p.prev_id, -1) and t.id < p.id
    ) as avgdeposit
from 
(
    select
        p.id,
        (
            select tt.id
            from products as tt
            where tt.itemid = 1 and tt.id <= p.id
            order by tt.id desc
            limit 1 offset 6
        ) as prev_id
    from products as p
    where p.itemid = 1
    order by p.id desc
    limit 15
) as p

SQL Fiddle示例

这带回了我想要的结果，谢谢。我将继续进行一些测试，并让您知道是否有需要更改的内容，但目前看起来很棒！ - ak85
5
这是我的解决方案。它很容易理解如何工作，但同时它不能够被优化得更好，因为我使用了一些字符串函数，并且它远离标准SQL。如果你只需要返回少量记录，它仍然可以正常工作。
对于每个ID，这个查询将返回一个按升序排列的以逗号分隔的前一个ID列表：
SELECT p1.id, p1.itemid, GROUP_CONCAT(p2.id ORDER BY p2.id DESC) previous_ids
FROM
  products p1 LEFT JOIN products p2
  ON p1.itemid=p2.itemid AND p1.id>p2.id
GROUP BY
  p1.id, p1.itemid
ORDER BY
  p1.itemid ASC, p1.id DESC

它将返回类似于下面的内容：

| ID | ITEMID |      PREVIOUS_IDS |
|----|--------|-------------------|
| 10 |      1 | 9,8,7,6,5,4,3,2,1 |
|  9 |      1 |   8,7,6,5,4,3,2,1 |
|  8 |      1 |     7,6,5,4,3,2,1 |
|  7 |      1 |       6,5,4,3,2,1 |
|  6 |      1 |         5,4,3,2,1 |
|  5 |      1 |           4,3,2,1 |
|  4 |      1 |             3,2,1 |
|  3 |      1 |               2,1 |
|  2 |      1 |                 1 |
|  1 |      1 |            (null) |

然后我们可以将此查询的结果与产品表本身连接，并在连接条件中使用FIND_IN_SET（src，csvalues），该条件返回逗号分隔值内src字符串的位置：
ON FIND_IN_SET(id, previous_ids) BETWEEN 1 AND 5

最终的查询看起来像这样：

SELECT
  list_previous.id,
  AVG(products.deposit)
FROM (
  SELECT p1.id, p1.itemid, GROUP_CONCAT(p2.id ORDER BY p2.id DESC) previous_ids
  FROM
    products p1 INNER JOIN products p2
    ON p1.itemid=p2.itemid AND p1.id>p2.id
  GROUP BY
    p1.id, p1.itemid
  ) list_previous LEFT JOIN products
  ON list_previous.itemid=products.itemid
     AND FIND_IN_SET(products.id, previous_ids) BETWEEN 1 AND 5
GROUP BY
  list_previous.id
ORDER BY
  id DESC

请查看这里的示例：http://sqlfiddle.com/#!2/c13bc/81。我不建议在大型表中使用此技巧，但对于小数据集来说是可以的。
3

这可能不是最简单的解决方案，但它能够完成任务并且是一种有趣的变化，我认为也是透明的。我模拟从Oracle中知道的分析函数。

由于我们不假设id连续，因此行计数通过每行增加@rn来模拟。接下来，将包括rownum的产品表与自身连接，并仅使用第2-6行构建平均值。

select p2id, avg(deposit), group_concat(p1id order by p1id desc), group_concat(deposit order by p1id desc)
  from ( select p2.id p2id, p1.rn p1rn, p1.deposit, p2.rn p2rn, p1.id p1id
           from   (select p.*,@rn1:=@rn1+1 as rn from products p,(select @rn1 := 0) r) p1
                , (select p.*,@rn2:=@rn2+1 as rn from products p,(select @rn2 := 0) r) p2 ) r
  where p2rn-p1rn between 1 and 5
  group by p2id
  order by p2id desc
  ;

结果：

+------+--------------+---------------------------------------+------------------------------------------+
| p2id | avg(deposit) | group_concat(p1id order by p1id desc) | group_concat(deposit order by p1id desc) |
+------+--------------+---------------------------------------+------------------------------------------+
|   10 |      86.0000 | 9,8,7,6,5                             | 90,90,75,75,100                          |
|    9 |      84.0000 | 8,7,6,5,4                             | 90,75,75,100,80                          |
|    8 |      84.0000 | 7,6,5,4,3                             | 75,75,100,80,90                          |
|    7 |      84.0000 | 6,5,4,3,2                             | 75,100,80,90,75                          |
|    6 |      79.0000 | 5,4,3,2,1                             | 100,80,90,75,50                          |
|    5 |      73.7500 | 4,3,2,1                               | 80,90,75,50                              |
|    4 |      71.6667 | 3,2,1                                 | 90,75,50                                 |
|    3 |      62.5000 | 2,1                                   | 75,50                                    |
|    2 |      50.0000 | 1                                     | 50                                       |
+------+--------------+---------------------------------------+------------------------------------------+

SQL Fiddle演示：http://sqlfiddle.com/#!2/c13bc/129

我想感谢这个回答，它教会了我如何在MySQL中模拟分析函数：MySQL get row position in ORDER BY

2

看起来你只是想要：

SELECT
  id,
  (SELECT AVG(deposit)
   FROM (
        SELECT deposit
        FROM products
        ORDER BY id DESC
        LIMIT 5) last5
   ) avgdeposit
FROM products

内部查询获取了添加到产品中的最后5行，包装该查询的查询获取它们存款的平均值。
我提供了更多信息，你的答案方向正确。不过我希望那个平均值是最后5个结果的平均值。 - ak85
谢谢，我正在寻找平均值随着每个项目的变化而改变，就像目前为止最佳答案中所做的那样。 - ak85
您想要根据前五个产品（基于id）的平均存款，其中itemid为1？虽然您的示例数据没有显示它，但itemid可能不是1吗？如果它不是1，那么它是否算作“最新的5个”，但不计入总数，或者我应该找到itemid为1的5行，无论有多远？ - Bohemian
是的，对于混淆感到抱歉。其中 itemid = 当前 id 的 itemid（在我的示例中，这总是 1，但最好不要硬编码为 1）。 - ak85
你应该在其他行之间添加更多的行，因为目前你可以省略itemid条件仍然得到正确的答案。如果不同itemid的行交错出现在一系列连续的id中，这尤其重要。请调整你的示例数据和sqlfiddle以使其更具挑战性。 - Bohemian
0

我会简化你的查询，这样我就可以解释它了。

SELECT
y.id,
(
    SELECT AVG(deposit) FROM 
    (
        SELECT deposit 
        FROM products
        LIMIT 5
    ) z
) AVGDEPOSIT
FROM 
(
    SELECT *
    FROM 
    (
        SELECT *
        FROM products
    ) x  
    LIMIT 15 
) y;

我猜你只需要在那里插入一些AS关键字。我相信其他人会想出更优雅的解决方案，但现在你可以试试。

SELECT
y.id,
(
    SELECT AVG(deposit) FROM 
    (
        SELECT deposit 
        FROM products
        LIMIT 5
    ) z
) AS AVGDEPOSIT
FROM 
(
    SELECT *
    FROM 
    (
        SELECT *
        FROM products
    ) AS x
    LIMIT 15 
) y;
嵌套 SELECT * FROM products 的目的是什么？ - zerkms
我只是简化了语法，以使“AS”关键字更容易看到。他@ak85可能希望保留原始的选择语句。 - bean5
0
这是在MySQL中实现它的一种方式：
SELECT p.id
     , ( SELECT AVG(deposit)
           FROM ( SELECT @rownum:=@rownum+1 rn, deposit, id
                    FROM ( SELECT @rownum:=0 ) r
                       , products
                   ORDER BY id ) t
          WHERE rn BETWEEN p.rn-5 AND p.rn-1 ) avgdeposit
  FROM ( SELECT @rownum1:=@rownum1+1 rn, id
           FROM ( SELECT @rownum1:=0 ) r
              , products
          ORDER BY id ) p
 WHERE p.rn >= 5
 ORDER BY p.rn DESC;

很遗憾，MySQL不支持WITH子句或窗口函数。如果两者都有的话，可以大大简化查询如下：
WITH tbl AS (
    SELECT id, deposit, ROW_NUMBER() OVER(ORDER BY id) rn
      FROM products
)
SELECT id
     , ( SELECT AVG(deposit)
           FROM tbl
          WHERE rn BETWEEN t.rn-5 AND t.rn-1 ) 
  FROM tbl t
 WHERE rn >= 5
 ORDER BY rn DESC;

后一条查询在Postgres中运行良好。

0

这里有两种可能的解决方案

首先使用用户变量添加序列号。这样做两次，将第二组连接到第一组，其中序列号在id-1和id-5之间。然后只需使用AVG即可。没有相关子查询。

SELECT Sub3.id, Sub3.itemid, Sub3.deposit, AVG(Sub4.deposit)
FROM
(
    SELECT Sub1.id, Sub1.itemid, Sub1.deposit, @Seq:=@Seq+1 AS Sequence
    FROM
    (
        SELECT id, itemid, deposit
        FROM products
        ORDER BY id DESC
    ) Sub1
    CROSS JOIN
    (
        SELECT @Seq:=0
    ) Sub2
) Sub3
LEFT OUTER JOIN
(
    SELECT Sub1.id, Sub1.itemid, Sub1.deposit, @Seq1:=@Seq1+1 AS Sequence
    FROM
    (
        SELECT id, itemid, deposit
        FROM products
        ORDER BY id DESC
    ) Sub1
    CROSS JOIN
    (
        SELECT @Seq1:=0
    ) Sub2
) Sub4
ON Sub4.Sequence BETWEEN Sub3.Sequence + 1 AND Sub3.Sequence + 5
GROUP BY Sub3.id, Sub3.itemid, Sub3.deposit
ORDER BY Sub3.id DESC

第二种方法比较粗糙，使用了相关子查询（随着数据量的增加，性能可能会变差）。它执行普通的选择操作，但对于最后一列，它有一个子查询，引用了主选择中的ID。

SELECT id, itemid, deposit, (SELECT AVG(P2.deposit) FROM products P2 WHERE P2.id BETWEEN P1.id - 5 AND p1.id - 1 ORDER BY id DESC LIMIT 5)
FROM products P1
ORDER BY id DESC
0

这是你想要的吗？

SELECT m.id
     , AVG(d.deposit) 
  FROM products m
     , products d
 WHERE d.id < m.id
   AND d.id >= m.id - 5
 GROUP BY m.id
 ORDER BY m.id DESC
;

但事情并不会那么简单。首先，表格不能只包含一个itemid（因此您的WHERE子句）；其次，id不能在itemid内是连续的/没有间隔的。第三，您可能想要生成跨越itemid而不是一次一个itemid的内容。所以这就是它。

SELECT itemid
     , m_id as id
     , AVG(d.deposit) as deposit
  FROM (
        SELECT itemid
             , m_id
             , d_id
             , d.deposit
             , @seq := (CASE WHEN m_id = d_id THEN 0 ELSE @seq + 1 END) seq
          FROM (
                SELECT m.itemid
                     , m.id m_id
                     , d.id d_id
                     , d.deposit
                  FROM products m
                     , products d
                 WHERE m.itemid = d.itemid
                   AND d.id <= m.id
                 ORDER BY m.id DESC
                     , d.id DESC) d
             , (SELECT @seq := 0) s
        ) d
 WHERE seq BETWEEN 1 AND 5
 GROUP BY itemid
     , m_id
 ORDER BY itemid 
     , m_id DESC
;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接