SQL语句简单问题

[win2000pega]

下面的SQL，哪里错了？

select * from
(
select
iod.goods_id,
DATENAME(year, io.goods_out_date),
-1*sum(iod.goods_count)
from
goods_in_out_detail IOD
inner join goods_in_out IO on io.goods_in_out_id=iod.goods_in_out_id
where
iod.in_out_type like '%入库%'
and iod.bill_state<>'2'
and io.bill_state<>'3'
and io.goods_out_date is not null
group by iod.goods_id,DATENAME(year, io.goods_out_date)
) I

[OracleRoob]

io

-->

[io]

[nobody@noone]

你报错是在哪一行？
貌似楼上说的io每问题啊
我用自己的表

select * from tb io where io.id >1000

这个是可以到

[OracleRoob]

--try


select * from 
( 
select 
 iod.goods_id, 
DATENAME(year, io.goods_out_date) as t1, 
-1*sum(iod.goods_count) as t2 
from 
goods_in_out_detail IOD 
inner join goods_in_out [IO] on io.goods_in_out_id=iod.goods_in_out_id 
where 
iod.in_out_type like '%入库%' 
and iod.bill_state <> '2' 
and io.bill_state <> '3' 
and io.goods_out_date is not null 
group by iod.goods_id,DATENAME(year, io.goods_out_date) 
) I

[chuifengde]

似乎没错，莫非你的goods_out_date有不是日期的格式？

[OracleRoob]

必须为子查询中的列指定别名，如：


select *
from (select getdate()) as t   --没有为子查询指定别名，报错



select *
from (select getdate() as a) as t   --为子查询的列指定别名，正确

[fellowcheng]

up

[chuifengde]

对了，没有指定列名，外层的select语句没有必要要啊

[kk19840210]

io [io]

select   *   from   
( 
select 
  iod.goods_id, 
datename(year,   io.goods_out_date), 
-1*sum(iod.goods_count)   
from   
goods_in_out_detail   IOD 
inner   join   goods_in_out   [IO]   on   io.goods_in_out_id=iod.goods_in_out_id 
where   
iod.in_out_type   like   '%入库%'   
and   iod.bill_state <> '2' 
and   io.bill_state <> '3' 
and   io.goods_out_date   is   not   null 
group   by   iod.goods_id,datename(year,   io.goods_out_date) 
)   I