如何在计算中从其他表访问列？

Sorry for the title. Not really sure how to word it. Edits appreciated.

对不起,标题。不太确定如何说出来。编辑赞赏。

I have a table, bin_content, which has a column, LotId that I'm trying to do a calculation on. The calculation is a bit complex, but I've boiled it down to the part that is failing in the sql below:

我有一个表bin_content,它有一个列,我正在尝试对其进行计算。计算有点复杂,但我把它归结为下面的sql中失败的部分:

SELECT bc.LotId, bc.ModelId, x.datecode FROM (
    SELECT
        LEFT(bc.LotId,4) as datecode
) x, bin_content bc WHERE bc.Quantity <> 0;

When I run this, I'm getting:

当我运行这个时,我得到:

SQL Error (1109): Unknown table 'bc' in field list.

SQL错误(1109):字段列表中的未知表'bc'。

The error is referring to the reference on the third line of code:

错误是指第三行代码的引用:

        LEFT(bc.LotId,4) as datecode

I'm wanting to access the value of LotId in the current record. What's the right way to do this?

我想要在当前记录中访问LotId的值。这样做的正确方法是什么?

EDIT:

Here's the actual calculation:

这是实际的计算:

SELECT bc.LotId, bc.ModelId, x.monthAge FROM (
    SELECT
        @reportdate := CURDATE(),
        @datecode := LEFT(bc.LotId,4),
        @dcyear := CONCAT("20",LEFT(@datecode,2)),
        @dcweek := SUBSTRING(@datecode,3,2),
        @dcyearstart := CONCAT(@dcyear,'-01-01'),
        @firstWeekLength := MOD((8-DAYOFWEEK(@dcyearstart)),7),
        @builddate := ADDDATE(@dcyearstart,IF(@dcweek = 1, 0, @firstWeekLength + (7*(@dcweek-2)))),
        @partialmonth := (DAY(@reportdate) - DAY(@builddate))/31,
        @monthAge := ((YEAR(@reportdate) - @dcyear) * 12) + (MONTH(@reportdate) - MONTH(@builddate)) + @partialmonth as monthAge
) x, bin_content bc WHERE bc.Quantity <> 0;

The line below is the problem child:

下面的行是问题孩子:

        @datecode := LEFT(bc.LotId,4),

If I can get that line to return a value, the rest works. The basic idea is that the bc.LotId has a date code in the first four digits (YYWW, where YY=year, and WW=week number). I'm calculating the number of months that have passed since that date code.

如果我可以获得该行返回一个值,其余的工作。基本思想是bc.LotId的前四位数字为日期代码(YYWW,其中YY =年,WW =周数)。我正在计算自该日期代码以来经过的月数。

3 个解决方案

#1

Edited answer after the question was updated:

问题更新后编辑的答案:

Since you only rely on a single row for this calculation, you can move all the columns to the inner query and select from there:

由于您只依赖单行进行此计算,因此可以将所有列移动到内部查询并从中进行选择:

SELECT  x.LotId, 
        x.ModelId, 
        x.monthAge 
FROM    (SELECT  bc.LotId, 
                 bc.ModelId,
                 @reportdate := CURDATE(),
                 @datecode := LEFT(bc.LotId,4),
                 @dcyear := CONCAT("20",LEFT(@datecode,2)),
                 @dcweek := SUBSTRING(@datecode,3,2),
                 @dcyearstart := CONCAT(@dcyear,'-01-01'),
                 @firstWeekLength := MOD((8-DAYOFWEEK(@dcyearstart)),7),
                 @builddate := ADDDATE(@dcyearstart,IF(@dcweek = 1, 0, @firstWeekLength + (7*(@dcweek-2)))),
                 @partialmonth := (DAY(@reportdate) - DAY(@builddate))/31,
                 @monthAge := ((YEAR(@reportdate) - @dcyear) * 12) + (MONTH(@reportdate) - MONTH(@builddate)) + @partialmonth as monthAge
         FROM    bin_content bc
         WHERE   bc.Quantity <> 0) x

#2

The issue is because bin_content is not in the scope of your subquery.

问题是因为bin_content不在子查询的范围内。

Removing a lot of the code, you have a skeleton like this:

删除了很多代码,你有一个这样的骨架:

SELECT stuff
FROM(
   SELECT stuff
) x, bin_content bc...

There is no FROM clause inside your inner select query, so bc cannot be referenced. The query is complex, so I'm not sure if making it like this will work:

内部选择查询中没有FROM子句,因此无法引用bc。查询很复杂,所以我不确定是否可以这样做:

SELECT stuff
FROM(
   SELECT stuff
   FROM bin_content bc
) x, bin_content bc...

but the issue is definitely as a result of bc not being in the proper scope.

但问题肯定是因为bc不在适当的范围内。

#3

Hope this may help:

希望这可能有所帮助:

SELECT bc.LotId, bc.ModelId, x.datecode FROM (
    SELECT LEFT(bc2.LotId,4) as datecode
    From bin_content bc2 
    WHERE bc2.LotId = bc.LotId
) x, bin_content bc WHERE bc.Quantity <> 0;

Update : I think why this will not work in you case?

更新:我认为为什么这不适用于你的情况?

SELECT bc.LotId, bc.ModelId, LEFT(bc.LotId,4) as datecode 
From bin_content bc WHERE bc.Quantity <> 0;

#1