sql-server - 主查询中的子查询条件选择

Question

我想做一些子查询选择，其条件取自主查询选择。

这是我的查询:

SELECT 
    DISTINCT MONTH(datetime_end) as B,
    (
        SELECT 
                SUM(DATEDIFF(DAY, datetime_start, datetime_end))
            FROM 
                    [simrke].[trx_medical_ruang] MR,
                    [simrke].[trx_ruang] R
            WHERE
                MR.ruang_cd = R.ruang_cd AND 
                R.ruang_nm like 'BAKUNG%'   AND
                MONTH(datetime_end) = B
    )
FROM 
        [simrke].[trx_medical_ruang] M
ORDER BY B
    ;

子查询选择条件 MONTH(datetime_end) = B 来自之前的选择，但我收到此错误消息:

[Err] 42S22 - [SQL Server]Invalid column name 'B'.

指向第一个 B 选择的正确方法是什么？

Answer 1

试试这个:MONTH(MR.datetime_end) = MONTH(M.datetime_end)

SELECT 
    DISTINCT MONTH(datetime_end) as B,
    (
        SELECT 
                SUM(DATEDIFF(DAY, datetime_start, datetime_end))
            FROM 
                    [simrke].[trx_medical_ruang] MR,
                    [simrke].[trx_ruang] R
            WHERE
                MR.ruang_cd = R.ruang_cd AND 
                R.ruang_nm like 'BAKUNG%'   AND
                MONTH(MR.datetime_end) = MONTH(M.datetime_end)
    )
FROM 
        [simrke].[trx_medical_ruang] M
ORDER BY B