Select Count(*) from ( select Distinct ChartNo, CkInDate from InpAcnt where UseDate between DateTime(2004-01-01) year to day and DateTime(2004-01-01) year to day and (RollBackAcntSeq is null or RollbackAcntSeq = '') Group by ChartNo, CkInDate) 請問以上語法哪裡有錯呢 DB: Informix

Select Count(*) from ( select Distinct  ChartNo, CkInDate from InpAcnt where UseDate between    DateTime(2004-01-01) year to day and DateTime(2004-01-01) year to day and (RollBackAcntSeq is null or RollbackAcntSeq = '') Group by ChartNo, CkInDate) as tmp 你開頭用了 distinct 後頭的 group by 顯得多餘了，多個 as tmp 這樣試試看。 P.S 我是用 SQL 2000，可是這段碼我自己沒跑過，看起來藍色字體部份在 SQL 2000 是過不了的 ~~ 發表人 -

樓上的大大 還是不行耶 不過錯誤的地方似乎是在Distinct上!!!

Select Count(*) from ( select Distinct ChartNo, CkInDate from InpAcnt where UseDate between DateTime(2004-01-01) year to day and DateTime(2004-01-01) year to day and (RollBackAcntSeq is null or RollbackAcntSeq = '') Group by ChartNo, CkInDate) as tmp 您如何得知錯誤是在 distinct 呢？那不用 distinct 改用 group by 呢？因為我沒有 infomix，所以沒辦法幫您實際測試看看。

Sorry 用Group還是不行耶!!!

先把問題單純化： 第一步： select Distinct ChartNo, CkInDate from InpAcnt 如果沒問題，第二步： select Distinct ChartNo, CkInDate from InpAcnt where RollBackAcntSeq is null or RollbackAcntSeq = '' 如果沒問題，第三步： select Distinct ChartNo, CkInDate from InpAcnt where UseDate between DateTime(2004-01-01) year to day and DateTime(2004-01-01) year to day and (RollBackAcntSeq is null or RollbackAcntSeq = '') 以此類推，順便試試看： Select Count(*) from ( select Distinct ChartNo, CkInDate from InpAcnt ) as tmp 或者 Select Count(*) from ( select Distinct ChartNo, CkInDate from InpAcnt )

建議如果有錯誤訊息的話可以貼出來,比較好知道問題出在哪裡,我按照上面的語法,寫了類似的一段在SQL 2000中Run並沒有問題,程式碼如下,您參考看看 SELECT COUNT(*) AS Expr1 FROM (SELECT DISTINCT ChartNo, CkInDate FROM InpAcnt WHERE UseDate BETWEEN '2004/01/01' AND '2004/12/01' GROUP BY ChartNo, CkInDate ) DERIVEDTBL 或者用下面這個方法試試 SELECT COUNT(*) AS Expr1 FROM (SELECT ChartNo, CkInDate FROM InpAcnt GROUP BY ChartNo, CkInDate HAVING (COUNT(*) = 1)) DERIVEDTBL 發表人 - supman 於 2004/12/08 17:18:03

Select Count(*) from ( select Distinct ChartNo, CkInDate from InpAcnt ) as tmp 小弟試過 只要是把Distinct放在子查詢裡就是會發生語法錯誤!!!