mysql - 调用时定义的 case 返回 "unknown column"[MySQL]

Question

我正在为产品的位置定义一个案例，然后希望稍后将该案例作为值返回。但是，我收到错误“‘字段列表’中的列位置未知。”

SELECT location, avg(pcnt), max(pcnt), min(pcnt)
  FROM (
    -- create temporary table 'a' to serve data from inner typing table created below
    SELECT a.date_key, a.num_snowflakes/k.tot_snowflakes AS pcnt
      FROM (
        SELECT 
          date_key,
          CASE
            WHEN location_type_key=2 then 'at_customer' 
            WHEN location_type_key IN (1,13) then 'on_floor' 
            WHEN location_type_key IN (6,7,11) then 'sat1'
            WHEN location_type_key IN (4,5) then 'film'
            WHEN location_type_key IN (8,9) then 'repair'
            WHEN location_type_key IN (12) then 'inspection'
            WHEN location_type_key IN (2) then 'outside' 
            ELSE NULL END AS location,
          COUNT(distinct garment_key) AS num_snowflakes, 
          COUNT(distinct product_key) AS num_skus

          FROM database
          -- excluding vendor return and retired
            WHERE location_type_key NOT IN (3,10)
            -- between 6/15/13 and 8/15/13
              AND date_key >= 20130615 AND date_key <= 20130815
          -- aggregates by day and location case    
          GROUP BY date_key, location
        ) AS a
        JOIN 
        -- create temporary table 'k' to serve 'tot_snowflakes' to table 'a' above
          (SELECT b.date_key, sum(b.num_snowflakes) AS tot_snowflakes 
              FROM (
                SELECT 
                  date_key, 
                  CASE   
                    WHEN location_type_key=2 then 'at_customer' 
                    WHEN location_type_key IN (1,13) then 'on_floor' 
                    WHEN location_type_key IN (6,7,11) then 'sat1'
                    WHEN location_type_key IN (4,5) then 'film'
                    WHEN location_type_key IN (8,9) then 'repair'
                    WHEN location_type_key IN (12) then 'inspection'
                    WHEN location_type_key IN (2) then 'outside' 
                    ELSE NULL END AS location,
                  COUNT(distinct garment_key) AS num_snowflakes, 
                  COUNT(distinct product_key) AS num_skus

                  FROM database
                    WHERE location_type_key NOT IN (3,10)
                    -- between 6/15/13 and 8/15/13
                      AND date_key >= 20130615 AND date_key <= 20130815
                  -- aggregates by day and location case    
                  GROUP BY date_key, location
                ) AS b
            ) AS k
          ON a.date_key=k.date_key
      GROUP BY a.date_key, a.location
    ) AS z

我试图确保我总是调用正确的表，所以我不确定错误来自哪里。大家有什么想法吗？

谢谢!

Answer 1

最外层查询从表 z 中选择。

表 z 派生自 SELECT FROM 表 a JOIN 表 k。

表 a 源自 SELECT FROM 表“database”。

表 k 源自 SELECT FROM 派生表 b。

表 b 派生自 SELECT FROM 基表“database”。

这里的简短答案是，您查询的第一行实际上是在要求:

SELECT z.location, avg(z.pcnt), max(z.pcnt), min(z.pcnt)

派生表 z 只有两列，“date_key”和“pcnt”。

(SELECT a.date_key, a.num_snowflakes/k.tot_snowflakes AS pcnt
...
) AS z

为了使查询正常工作，表 z 需要一个位置，该位置在每个嵌套选择中都必须是明确的，直到您可以跟踪它到您打算实际从中读取它的基表。

看起来查询所做的工作超出了您想要实现的结果所需的工作量，但我一直盯着它直到我的眼睛失去了聚焦能力，所以我会坚持下去目前的答案是:每个需要将其“位置”列向上渗透到最外层选择的派生表都需要显式选择它。