我有以下无效的查询,但显示了我打算做什么:
SELECT
REPLACE(j.wo_id, 'PREFIX.', '') AS WOID,
j.act_finish_time_local,
STUFF ((SELECT ', '+item_id FROM bom_item WHERE parent_item_id = WOID AND bom_pos > 0 FOR XML PATH('')), 1, 1, '') AS Families
FROM job AS j
INNER JOIN job_event je ON je.wo_id = j.wo_id AND je.oper_id = j.oper_id
WHERE j.oper_id = 'Sawing' AND j.wo_id LIKE 'PREFIX.%' AND j.act_finish_time_local IS NOT NULL
GROUP BY j.wo_id, j.act_finish_time_local
ORDER BY j.act_finish_time_local
这是示例数据表:
| wo_id | act_finish_time_local | oper_id |
|-------------|-----------------------|---------|
| PREFIX.0001 | 2014/01/01 | Sawing |
| PREFIX.0002 | 2014/01/01 | Sawing |
| wo_id | oper_id |
|-------------|---------|
| PREFIX.0001 | Sawing |
| PREFIX.0002 | Sawing |
| parent_item_id | item_id | bom_pos |
|----------------|---------|---------|
| 0001 | abc | 1 |
| 0001 | def | 2 |
| 0002 | qrs | 1 |
| 0002 | tuv | 2 |
| WOID | act_finish_time_local | Families |
|------|-----------------------|----------|
| 0001 | 2014/01/01 | abc, def |
| 0002 | 2014/01/01 | qrs, tuv |
现在,查询无法编译,因为SQLServer抱怨WOID字段不可用。
有没有办法来解决这个问题?
您无法在子查询中引用计算字段或别名...
您必须在子查询中再次进行计算或在CTE中对其进行定义,然后在CTE之外重新使用它
关于此主题的一篇有趣的文章可能对您很有趣
http://joecelkothesqlapprentice.blogspot.ch/2006/06/reference-alias-field-name.html
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句