你真的是百折不曲XXXX :) 怕你了,我的语句在SQL SERVER 中是对的,ACCESS中没有case when 有IIF,所以该语句在ACCESS中改成:
select max(idate) as idate,sum(iamount) as iamount,
max(odate) as odate,sum(oamount) as oamount
from
(select fdate,iif( status="I",fdate, null ) as idate,
iif(status="I" ,amount,null ) as iamount,
iif(status="O" ,fdate,null) as odate,
iif(status="O" ,amount,null) as oamount
from io)
t1
group by fdate
order by fdate试试?!为什么不早说是ACCESS呢?哎
select max(idate) as idate,sum(iamount) as iamount,
max(odate) as odate,sum(oamount) as oamount
from
(select fdate,iif( status="I",fdate, null ) as idate,
iif(status="I" ,amount,null ) as iamount,
iif(status="O" ,fdate,null) as odate,
iif(status="O" ,amount,null) as oamount
from io)
t1
group by fdate
order by fdate试试?!为什么不早说是ACCESS呢?哎
解决方案 »
- --请用一条 SQL 语句查询出每门课都大于 80 分的学生姓名。
- SQL时间格式
- sql server2005
- 求救 如何使用触发器?
- 求一SQL語句
- SQL安装得问题!跳出"无法读取指定文件。极有可能是由于网络连接不正常所致。请更正该问题然后重新启动安装程序。
- 后面跟个数量(小问题)
- SQL server 2008 怎么卸载?很急很急求大神
- 我在 查询分析器中使用 函数 FN_LISTEXTENDEDPROPERTY 获取扩展属性 可以成功,可是在delphi6里面却失败,为什么?
- How to write sql script to Sort?
- 用ADO如何压缩一个ACCESS数据库(mdb文件)?
- client端联到Sql Server上有时行有时不行,请问是什么原因?
(
SELECT A.DATE AS IDATE,A.AMOUNT AS IAMOUNT,'' AS ODATE,'' AS OAMOUNT,A.DATE
FROM TABLE1 AS A WHERE A.STATUS='I'
) C
FULL JOIN
(
SELECT '' AS IDATE,'' AS IAMOUNT,B.DATE AS ODATE,B.AMOUNT AS OAMOUNT,B.DATE
FROM TABLE1 AS B WHERE B.STATUS='O'
) D
ON C.IDATE=D.ODATE
from ((select distinct io_date from io) x
left join
(select distinct io_date as idate, sum(amount) as iamount from io where status = 'I' group by io_date) as y on x.io_date = y.idate)
left join
(select distinct io_date as odate, sum(amount) as oamount from io where status = 'O' group by io_date) as z on x.io_date = z.odate
order by x.io_date