c# - 如何将变量(别名)传递给where条件SQL

Question

大家好

我想将 'coalesce(MDT.DeptName,DD5.Name) as departname' 传递到 where 条件中。现在错误显示无效列。有什么可能吗？下面是我的代码:

SELECT op_id, 
       DD.NAME                                                    Unit, 
       DD1.NAME                                                   Freq, 
       DD2.NAME                                                   Calc, 
       COALESCE(MDT.deptname, DD5.NAME)                           AS departname, 
       COALESCE(CO.yearlytarget + ' ' + dd3.NAME, CO.yearlytarget)AS 
       YearlyTarget, 
       CO.pastyearresult, 
       CO.weight, 
       CO.project, 
       CO.description, 
       Co.datecreated, 
       MDT.weightvalue 
FROM   [MBO].[dbo].[m_newcaloprt] CO 
       LEFT JOIN [MBO].[dbo].[m_ddl] DD 
              ON CO.unit_ddl = DD.d_id 
       LEFT JOIN [MBO].[dbo].[m_ddl] DD1 
              ON CO.freq_ddl = DD1.d_id 
       LEFT JOIN [MBO].[dbo].[m_ddl] DD2 
              ON CO.calc_ddl = DD2.d_id 
       LEFT JOIN [MBO].[dbo].[m_user] US 
              ON CO.uid = US.id 
       LEFT JOIN [MBO].[dbo].[m_user] US1 
              ON US1.deptid = US.deptid 
       LEFT JOIN [MBO].[dbo].[m_ddl] DD5 
              ON US1.deptid = DD5.d_id 
       LEFT JOIN [MBO].[dbo].[m_multipledept] MDT 
              ON MDT.projectid = CO.op_id 
       LEFT JOIN [MBO].[dbo].[m_ddl] DD3 
              ON CO.calcsymbol = DD3.d_id 
WHERE  departname = '' 
       AND isactive = 0 
       AND isverifed = 1 

我想将此代码用于 C#。请指教

Answer 1

您不能在 Where 子句中使用 Alias 名称。使用原始列

WHERE  COALESCE(MDT.deptname, DD5.NAME) = '' 
       AND isactive = 0 
       AND isverifed = 1 

这是逻辑上处理查询的方式

1. FROM
2. ON
3. OUTER
4. WHERE --here
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT --here
9. DISTINCT
10 ORDER BY
11. TOP

由于 Where 子句在 Select 之前处理，我们将不会在 Where 子句中有 Alias 名称