导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

请教这个sql错在哪里?

JayJay 2007-12-19 09:31:43
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错在哪里?
...全文
68 点赞 收藏 10
写回复
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
tim_spac 2007-12-19
恐怕是:
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将结果添加上该字段信息
回复
JayJay 2007-12-19
as current_location ===>as current_location ,

不是这个问题

and LOR.wh_id = LOR.wh_id
???
自己等于自己?
回复
yms_wangxm 2007-12-19
and LOR.wh_id = LOR.wh_id
有用吗???
回复
yms_wangxm 2007-12-19
t_hu_detail.status 和 t_hu_detail.wh_id
有问题 ,,
回复
pt1314917 2007-12-19
楼主这几个表之间的关系还真多。。
回复
chuifengde 2007-12-19
??
as current_location ===>as current_location ,
回复
dawugui 2007-12-19
分组字段不对?
回复
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
回复
pt1314917 2007-12-19

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




回复
JayJay 2007-12-19
贴错了:
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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告