恳请高手指出这sql语句的问题

[hhpyt]

select x.spmc 商品名称,x.spgg 商品规格,x.spdj 商品单价,x.xsyxm 销售员姓名,j.jhcj 进货厂家,x.xsrq 销售日期,x.xssl 销售数量,x.spdj*x.xssl 销售金额
from view_spxs x left jion view_spjh j on x.spmc=j.spmc
where (x.xsrq between '20100720'and'20100819')
and
x.spmc in ('a','b','c','e','f','g')


我想把两个视图view_spxx和view_spjh分别别名为x和j，然后取x视图的spmc、spgg、spdj、xsyxm、xxrq、xxsl及j视图的jhcj字段，并将这些字段分别改别名，查询商品‘a、b、c、d、e、f、g'在20100720到20100819之间的销售情况，该语句无法执行，恳请高手指教，谢谢！！！

[ys332617184]

我也估计是日期出的问题

[js_flybird]

left jion中的jion应改成join

[hhpyt]

回复10楼，jion 是我打错了，在实际中是正确的

[js_flybird]

where (x.xsrq between '20100720'and'20100819')改成
where (x.xsrq between to_date('20100720','yyyymmdd') and to_date('20100819','yyyymmdd'))

[tlxing]

[Quote=引用 5 楼 wkc168 的回复:]
引用楼主 hh371011618 的回复:
(x.xsrq between '20100720' and '20100819')

--x.xsrq 什么类型的 是日期的话 要
x.xsrq between to_date('20100720','yyyymmdd') and to_date('20100819','yyyymmdd')[/Quote]
日期不对

[da21]

有什么出错提示？

[da21]

'20100720'and'20100819'
and 前后加空格 ？？

[caofaping]

你用左连接可以取view_spjh 的值嘛？
改成等值连接应该就可以了

select x.spmc 商品名称,
       x.spgg 商品规格,
       x.spdj 商品单价,
       x.xsyxm 销售员姓名,
       j.jhcj 进货厂家,
       x.xsrq 销售日期,
       x.xssl 销售数量,
       x.spdj * x.xssl 销售金额
  from view_spxs x
  , view_spjh j
 where (x.xsrq between '20100720' and '20100819')
   and x.spmc in ('a', 'b', 'c', 'e', 'f', 'g')
   and x.spmc = j.spmc

[gelyon]

select x.spmc 商品名称,x.spgg 商品规格,x.spdj 商品单价,x.xsyxm 销售员姓名,j.jhcj 进货厂家,
       x.xsrq 销售日期,x.xssl 销售数量,x.spdj*x.xssl 销售金额  
from view_spxs x , view_spjh j  
where x.spmc=j.spmc 
     AND x.xsrq between '20100720'and'20100819'
     AND x.spmc in ('a','b','c','e','f','g');

[心中的彩虹]

[Quote=引用楼主 hh371011618 的回复:]
select x.spmc 商品名称,x.spgg 商品规格,x.spdj 商品单价,x.xsyxm 销售员姓名,j.jhcj 进货厂家,x.xsrq 销售日期,x.xssl 销售数量,x.spdj*x.xssl 销售金额
from view_spxs x left jion view_spjh j on x.spmc=j.spmc
where (x.xsrq between '20100……
[/Quote]

(x.xsrq between '20100720' and '20100819')

--x.xsrq 什么类型的 是日期的话 要
x.xsrq between to_date('20100720','yyyymmdd') and to_date('20100819','yyyymmdd')

[hhpyt]

and 前后有空格，具体提示我忘了，我们开始放假了，过几天我去单位再看一下，不过很感谢这位朋友，继续期待中

[minitoy]

貌似没什么大问题.

[sinpoal]

[Quote=引用楼主 hh371011618 的回复:]
select x.spmc 商品名称,x.spgg 商品规格,x.spdj 商品单价,x.xsyxm 销售员姓名,j.jhcj 进货厂家,x.xsrq 销售日期,x.xssl 销售数量,x.spdj*x.xssl 销售金额
from view_spxs x left jion view_spjh j on x.spmc=j.spmc
where (x.xsrq between '20100……
[/Quote]

select x.spmc 商品名称,x.spgg 商品规格,x.spdj 商品单价,x.xsyxm 销售员姓名,j.jhcj 进货厂家,x.xsrq 销售日期,x.xssl 销售数量,x.spdj*x.xssl 销售金额  
from view_spxs x , view_spjh j where x.spmc=j.spmc  
and (x.xsrq between to_date('20100720','yyyymmdd')and to_date('20100819','yyyymmdd'))
and x.spmc in ('a','b','c','e','f','g');

[fwx515354]

你的数据库如果是在Linux下的，要考虑考虑下中文的问题。或中文加上双引号

[taikongxinke]

日期问题，x.xsrq >=to_date('20100720','yyyymmdd') and x.xsrq <=to_date('20100819','yyyymmdd')

[lg200015017]

报错发出来再说吧。

[hhpyt]

感谢各位指点，近日内我会尽快把报错的具体提示发出来