如何在SQL中定义变量并作为其他查询列使用？解决字段未找到报错

在筛选经多层折扣计算后价格符合要求的产品时，你编写的Laravel查询代码出现SQLSTATE[42S22]: Column not found: 1054 Unknown column 'price' in 'field list'错误，核心原因是SQL不允许在同一SELECT子句中引用刚定义的列别名——你在selectRaw里定义了price别名，紧接着又在计算site_price时直接引用它，这违反了SQL的解析规则。

下面提供两种可行的修复方案：

方案一：嵌套计算逻辑（直接复用price的计算规则） #

把price的完整计算逻辑嵌套到site_price的计算中，全程不依赖别名：

$products = Product::query()
    ->select('*')
    ->selectRaw('
        -- 计算基础折扣后的price
        IF(
            (base_discount_start_time IS NULL OR base_discount_start_time <= NOW()) AND (base_discount_end_time IS NULL OR base_discount_end_time >= NOW()),
            IF(base_discount_type = "percent", (customer_price - (customer_price * (base_discount / 100))), (customer_price - base_discount)),
            customer_price
        ) AS price,
        -- 嵌套price的计算逻辑，计算营销折扣后的site_price
        IF(
            (marketing_discount_start_time IS NULL OR marketing_discount_start_time <= NOW()) AND (marketing_discount_end_time IS NULL OR marketing_discount_end_time >= NOW()),
            IF(marketing_discount_type = "percent", 
                (
                    IF(
                        (base_discount_start_time IS NULL OR base_discount_start_time <= NOW()) AND (base_discount_end_time IS NULL OR base_discount_end_time >= NOW()),
                        IF(base_discount_type = "percent", (customer_price - (customer_price * (base_discount / 100))), (customer_price - base_discount)),
                        customer_price
                    ) - (
                        IF(
                            (base_discount_start_time IS NULL OR base_discount_start_time <= NOW()) AND (base_discount_end_time IS NULL OR base_discount_end_time >= NOW()),
                            IF(base_discount_type = "percent", (customer_price - (customer_price * (base_discount / 100))), (customer_price - base_discount)),
                            customer_price
                        ) * (marketing_discount / 100)
                    )
                ),
                (
                    IF(
                        (base_discount_start_time IS NULL OR base_discount_start_time <= NOW()) AND (base_discount_end_time IS NULL OR base_discount_end_time >= NOW()),
                        IF(base_discount_type = "percent", (customer_price - (customer_price * (base_discount / 100))), (customer_price - base_discount)),
                        customer_price
                    ) - marketing_discount
                )
            ),
            -- 营销折扣不生效时，直接使用基础折扣后的价格
            IF(
                (base_discount_start_time IS NULL OR base_discount_start_time <= NOW()) AND (base_discount_end_time IS NULL OR base_discount_end_time >= NOW()),
                IF(base_discount_type = "percent", (customer_price - (customer_price * (base_discount / 100))), (customer_price - base_discount)),
                customer_price
            )
        ) AS site_price
    ')
    ->having('site_price', '>', 300);

方案二：使用子查询/CTE（更易维护） #

先通过子查询或CTE计算出price，再在外层基于这个结果计算site_price，代码可读性和可维护性更高：

子查询版本 #

$products = Product::query()
    ->fromSub(
        // 子查询：先计算基础折扣后的price
        Product::query()
            ->select('*')
            ->selectRaw('
                IF(
                    (base_discount_start_time IS NULL OR base_discount_start_time <= NOW()) AND (base_discount_end_time IS NULL OR base_discount_end_time >= NOW()),
                    IF(base_discount_type = "percent", (customer_price - (customer_price * (base_discount / 100))), (customer_price - base_discount)),
                    customer_price
                ) AS price
            ),
        'products_with_price'
    )
    ->select('*')
    ->selectRaw('
        IF(
            (marketing_discount_start_time IS NULL OR marketing_discount_start_time <= NOW()) AND (marketing_discount_end_time IS NULL OR marketing_discount_end_time >= NOW()),
            IF(marketing_discount_type = "percent", (price - (price * (marketing_discount / 100))), (price - marketing_discount)),
            price
        ) AS site_price
    ')
    ->having('site_price', '>', 300);

CTE版本（Laravel 8及以上版本支持） #

$products = Product::query()
    ->withExpression('products_with_price', function ($query) {
        $query->select('*')
            ->selectRaw('
                IF(
                    (base_discount_start_time IS NULL OR base_discount_start_time <= NOW()) AND (base_discount_end_time IS NULL OR base_discount_end_time >= NOW()),
                    IF(base_discount_type = "percent", (customer_price - (customer_price * (base_discount / 100))), (customer_price - base_discount)),
                    customer_price
                ) AS price
            ');
    })
    ->from('products_with_price')
    ->select('*')
    ->selectRaw('
        IF(
            (marketing_discount_start_time IS NULL OR marketing_discount_start_time <= NOW()) AND (marketing_discount_end_time IS NULL OR marketing_discount_end_time >= NOW()),
            IF(marketing_discount_type = "percent", (price - (price * (marketing_discount / 100))), (price - marketing_discount)),
            price
        ) AS site_price
    ')
    ->having('site_price', '>', 300);

方案二的子查询/CTE方式更适合复杂的折扣规则，后续修改基础折扣逻辑时只需改动一处，比方案一的重复代码更易维护。

内容的提问来源于stack exchange，提问作者Hanie Asemi