select dbo.usf_get_wh_name(parentHUM.wh_id) as wh_name,parentHUM.hu_id,hud.item_number,
hud.lot_number,sum(hud.actual_qty) as actual_qty,parentHUM.location_id as current_location
(select top 1 l.location_id from t_location_relation l where l.relation_location_id = hud.status and l.wh_id =hud.wh_id) as supposed_zone
from t_hu_detail hud,t_hu_master parentHUM,t_location_relation LOR,t_location LOC
where hud.serial_number = parentHUM.hu_id
and LOR.relation_type='ITEM_STATUS'
and hud.status<>LOR.related_location_id
and LOR.location_id = parentHUM.location_id
and LOR.wh_id = parentHUM.wh_id
and LOC .location_id = LOR.location_id
and LOR.wh_id = LOR.wh_id
and LOC.type in ('M','R','U','P','C','V')
and hud.wh_id like '01'
and hud.item_number like '~item_number~'
and hud.lot_number like '~lot_number~'
group by parentHUM.wh_id,parentHUM.hu_id,hud.item_number,hud.lot_number,parentHUM.location_id,LOR.relation_location_id报错:
Msg 8120, Level 16, State 1, Line 1
Column 't_hu_detail.status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 't_hu_detail.wh_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.请教这个sql错在哪里?
hud.lot_number,sum(hud.actual_qty) as actual_qty,parentHUM.location_id as current_location
(select top 1 l.location_id from t_location_relation l where l.relation_location_id = hud.status and l.wh_id =hud.wh_id) as supposed_zone
from t_hu_detail hud,t_hu_master parentHUM,t_location_relation LOR,t_location LOC
where hud.serial_number = parentHUM.hu_id
and LOR.relation_type='ITEM_STATUS'
and hud.status<>LOR.related_location_id
and LOR.location_id = parentHUM.location_id
and LOR.wh_id = parentHUM.wh_id
and LOC .location_id = LOR.location_id
and LOR.wh_id = LOR.wh_id
and LOC.type in ('M','R','U','P','C','V')
and hud.wh_id like '01'
and hud.item_number like '~item_number~'
and hud.lot_number like '~lot_number~'
group by parentHUM.wh_id,parentHUM.hu_id,hud.item_number,hud.lot_number,parentHUM.location_id,LOR.relation_location_id报错:
Msg 8120, Level 16, State 1, Line 1
Column 't_hu_detail.status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 't_hu_detail.wh_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.请教这个sql错在哪里?
解决方案 »
- 急!!一个检索问题(在线等)
- 如何把以下结果集分组,请各位高手赐教!(急)
- 请解释一下Money数据类型
- 编译 libpqxx出错 正在创建库 lib\libpqxxD.lib 和对象 lib\libpqxxD.exp
- 我的SQLSERVER有个怪问题!关于大批量入库
- 开发软件时,SQL Sever2000好还是Oracle 数据库好啊?
- 怎么在程序里执行sql 2000批处理脚本(*.sql)
- 菜问题
- 为什么SQL server中,IIF函数不能用?
- 求FoxBase!DOS版或window版,[email protected]或给出链接,谢谢~!
- sql中字符串中含有数字时的逆序排序方法,如何让a10在a9前面?
- sql排列
select dbo.usf_get_wh_name(parentHUM.wh_id) as wh_name,parentHUM.hu_id,hud.item_number,
hud.lot_number,sum(hud.actual_qty) as actual_qty,parentHUM.location_id as current_location,
(select top 1 l.location_id from t_location_relation l where l.relation_location_id = hud.status and l.wh_id =hud.wh_id) as supposed_zone
from t_hu_detail hud,t_hu_master parentHUM,t_location_relation LOR,t_location LOC
where hud.serial_number = parentHUM.hu_id
and LOR.relation_type='ITEM_STATUS'
and hud.status <> LOR.related_location_id
and LOR.location_id = parentHUM.location_id
and LOR.wh_id = parentHUM.wh_id
and LOC .location_id = LOR.location_id
and LOR.wh_id = LOR.wh_id
and LOC.type in ('M','R','U','P','C','V')
and hud.wh_id like '01'
and hud.item_number like '~item_number~'
and hud.lot_number like '~lot_number~'
group by parentHUM.wh_id,parentHUM.hu_id,hud.item_number,hud.lot_number,parentHUM.location_id,LOR.relation_location_id
select dbo.usf_get_wh_name(parentHUM.wh_id) as wh_name,parentHUM.hu_id,hud.item_number,
hud.lot_number,sum(hud.actual_qty) as actual_qty,parentHUM.location_id as current_location, --这里加上个逗号。
(select top 1 l.location_id from t_location_relation l where l.relation_location_id = hud.status and l.wh_id =hud.wh_id) as supposed_zone
from t_hu_detail hud,t_hu_master parentHUM,t_location_relation LOR,t_location LOC
where hud.serial_number = parentHUM.hu_id
and LOR.relation_type='ITEM_STATUS'
and hud.status <> LOR.related_location_id
and LOR.location_id = parentHUM.location_id
and LOR.wh_id = parentHUM.wh_id
and LOC .location_id = LOR.location_id
and LOR.wh_id = LOR.wh_id
and LOC.type in ('M','R','U','P','C','V')
and hud.wh_id like '01'
and hud.item_number like '~item_number~'
and hud.lot_number like '~lot_number~'
group by parentHUM.wh_id,parentHUM.hu_id,hud.item_number,hud.lot_number,parentHUM.location_id,LOR.relation_location_id
hud.lot_number,sum(hud.actual_qty) as actual_qty,parentHUM.location_id as current_location
(select top 1 l.location_id from t_location_relation l where l.relation_location_id = hud.status and l.wh_id =hud.wh_id) as supposed_zone
from t_hu_detail hud,t_hu_master parentHUM,t_location_relation LOR,t_location LOC
where hud.serial_number = parentHUM.hu_id
and LOR.relation_type='ITEM_STATUS'
and hud.status <> LOR.related_location_id
and LOR.location_id = parentHUM.location_id
and LOR.wh_id = parentHUM.wh_id
and LOC .location_id = LOR.location_id
and LOR.wh_id = LOR.wh_id
and LOC.type in ('M','R','U','P','C','V')
and hud.wh_id like '01'
and hud.item_number like '~item_number~'
and hud.lot_number like '~lot_number~'
group by parentHUM.wh_id,parentHUM.hu_id,hud.item_number,hud.lot_number,parentHUM.location_id,LOR.relation_location_id
,hud.status,hud.wh_id
as current_location ===>as current_location ,
有问题 ,,
有用吗???
???
自己等于自己?
select ...,
(select top 1 l.location_id from t_location_relation l where l.relation_location_id = hud.status and l.wh_id =hud.wh_id) as supposed_zone
from ...
在捣乱。
建议先不包含该字段进行group by统计
然后再用join将结果添加上该字段信息