sql - 错误 : syntax error at or near "and"

Question

我有这样的查询。

select 
  ad.escore,
  ad.mscore,
  round(sum(ps.cnt) / sum(n.cnt) * 100,1) as percent
from 
(
  select 
    account_no,
    -- 602 becomes '595-604'
    to_char(trunc(empirica_score - 5, -1) + 5, '9999') || '-' || to_char(trunc(empirica_score - 5, -1) + 14, '9999') as escore,
    -- 97 becomes '76-100'. Change the expression to group differently.
    cast(((mfin_score - 1) / 25) * 25 + 1 as text) || '-' || cast(((mfin_score - 1) / 25) * 25 + 25 as text) as mscore
  from account_details
) ad
join 
(
  select custno, count(*) as cnt
  from paysoft_results 
  where result = 'Successful' 
  and resultdate >= '13/08/2014'     <------- HERE
  and resultdate <= '12/19/2014'     <------- HERE
  group by custno
) ps on ps.custno = ad.account_no
join 
(
  select customer_code, count(distinct start_date) as cnt
  from naedo 
  and start_date >= '13/08/2014'      <------- HERE
  and start_date <= '12/19/2014'      <------- HERE
  group by customer_code
) n on n.customer_code = ad.account_no
group by ad.escore, ad.mscore;

如果我没有像上面那样安装日期，它就完美了。

如果我确实输入了日期，我会得到一个错误 ERROR: syntax error at or near "and"

有什么想法吗？

更新

好吧，我想我现在可以问一个问题，所以如果我可以追加这个问题的话。

错误:日期/时间字段值超出范围:“13/08/2014”

我查询中的日期比较。正确的做法是什么？

Answer 1

好吧，这个位行不通:

select customer_code, count(distinct start_date) as cnt
from naedo 
and start_date >= '13/08/2014'      <------- HERE
and start_date <= '12/19/2014'      <------- HERE
group by ...

因为 where 子句必须以 where 开头，而不是 and。否则，我们都将其称为 and 子句:-)

它需要是:

select customer_code, count(distinct start_date) as cnt
from naedo 
where start_date >= '13/08/2014'
  and start_date <= '12/19/2014'
group by ...

您用 HERE 标记的另一部分(第二段，第一个 join 子句)看起来不错，应该可以正常工作。

---

顺便说一句，至少一个您的日期格式不正确。分割:

and start_date >= '13/08/2014'
and start_date <= '12/19/2014'

日期要么是 Undecimber 的 8^th 要么是 12^th，好吧，我什至不知道是什么十九(或十七，基于实际月份已经不合时宜)的拉丁前缀是。

您需要弄清楚您的数据库支持 mm/dd/yyyy 或 dd/mm/yyyy 中的哪一个，然后坚持使用那个 .

鉴于您质疑更新状态它提示 13/08/2014，您可能会发现它应该写成 08/13/2014，在 mm/dd/yyyy 格式。