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错在哪里?
10 个解决方案
#1
贴错了:
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
#2
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
#3
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.status,hud.wh_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
#4
分组字段不对?
#5
??
as current_location ===>as current_location ,
#6
楼主这几个表之间的关系还真多。。
#7
t_hu_detail.status 和 t_hu_detail.wh_id
有问题 ,,
有问题 ,,
#8
and LOR.wh_id = LOR.wh_id
有用吗???
有用吗???
#9
as current_location ===>as current_location ,
不是这个问题
and LOR.wh_id = LOR.wh_id
???
自己等于自己?
不是这个问题
and LOR.wh_id = LOR.wh_id
???
自己等于自己?
#10
恐怕是:
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将结果添加上该字段信息
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将结果添加上该字段信息
#1
贴错了:
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
#2
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
#3
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.status,hud.wh_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
#4
分组字段不对?
#5
??
as current_location ===>as current_location ,
#6
楼主这几个表之间的关系还真多。。
#7
t_hu_detail.status 和 t_hu_detail.wh_id
有问题 ,,
有问题 ,,
#8
and LOR.wh_id = LOR.wh_id
有用吗???
有用吗???
#9
as current_location ===>as current_location ,
不是这个问题
and LOR.wh_id = LOR.wh_id
???
自己等于自己?
不是这个问题
and LOR.wh_id = LOR.wh_id
???
自己等于自己?
#10
恐怕是:
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将结果添加上该字段信息
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将结果添加上该字段信息