select max(idate) as idate,max(iamount) as iamount,
max(odate) as odate,max(oamount) as oamount
from
(select date,case status when 'I' then date else null end as idate,
case status when 'I' then amount else null end as iamount,
case status when '0' then date else null end as odate,
case status when '0' then amount else null end as oamount
from io)
t1
group by date
max(odate) as odate,max(oamount) as oamount
from
(select date,case status when 'I' then date else null end as idate,
case status when 'I' then amount else null end as iamount,
case status when '0' then date else null end as odate,
case status when '0' then amount else null end as oamount
from io)
t1
group by date
select max(idate) as idate,max(iamount) as iamount,
max(odate) as odate,max(oamount) as oamount
from
(select date,case status when 'I' then date else null end as idate,
case status when 'I' then amount else null end as iamount,
case status when '0' then date else null end as odate,
case status when '0' then amount else null end as oamount
from io)
t1
group by date
order by date
select iDate=a.date ,iAmount=a.amount,
oDate=b.date ,oAmout=b.amount
from io a full join io b
ON a.date=b.date
WHERE (a.status='I' AND b.status='O')
or a.status is null or b.status is null
order by a.date,b.date
select y.idate, y.iamount, z.odate, z.oamount
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) y on x.io_date = y.i_date
left join
(select distinct io_date as odate, sum(amount) as oamount from io where status = 'O' group by io_date) z on x.io_date = z.i_date
order by y.idate, z.odate
select iDate=a.date ,iAmount=a.amount,
oDate=b.date ,oAmout=b.amount
from io a full join io b
ON a.date=b.date
WHERE (a.status='I' or a.status is null)
AND (b.status='O' or b.status is null)
order by a.date,b.date
(a.status='I' or a.status is null) and
(b.status='O' or b.status is null)
max(odate) as odate,sum(oamount) as oamount
from
(select fdate,case status when 'I' then fdate else null end as idate,
case status when 'I' then amount else null end as iamount,
case status when 'O' then fdate else null end as odate,
case status when 'O' then amount else null end as oamount
from io)
t1
group by fdate
order by fdate
你的错误是没有把数据检索出来就连接了,你应该把数据检索出来以后再连接!就可以了!
select iDate=isnull(a.idate,' ') ,iAmount=isnull(a.iamount,0),oDate=isnull(b.odate,' ') ,oAmout=isnull(b.oamount,0)
from (select date as idate,amount as iamount from io where status='I') as a
full join
(select date as odate,amount as oamount from io where status='o') as b
on a.idate=b.odate
order by a.idate,b.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
我这里大date字段用的是dateteime类型。然后更改如下:我想应该没有问题了: icevi(按钮工厂) 。
select iDate=isnull(convert(varchar(10),a.idate,120),' ') ,iAmount=isnull(a.iamount,0),oDate=isnull(convert(varchar(10),b.odate,120),' ') ,oAmout=isnull(b.oamount,0)
from (select date as idate,amount as iamount from io where status='I') as a
full join
(select date as odate,amount as oamount from io where status='o') as b
on a.idate=b.odate
order by isnull(a.idate,b.odate)
除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。在 MS sql server 中,下面的语句通过测试。假定你每天有不止多次的收入/支出,所以用sum,否则不需要。create table io(id int identity,status char(1),date datetime,amount float)
insert into io values(1,'I','2001-01-01',100.0)
insert into io values(2,'O','2001-02-02',50.0)
insert into io values(3,'I','2001-05-01',50.0)
insert into io values(4,'O','2001-05-01',100.0)
insert into io values(5,'I','2001-05-01',50.0)
insert into io values(6,'O','2001-05-01',100.0)select case when iAmount=0 then null else date end as iDate
,case when iAmount=0 then null else iAmount end as iAmount
,case when oAmount=0 then null else date end as iDate
,case when oAmount=0 then null else oAmount end as oAmount
from (select date
,sum(case when status='I' then amount else 0 end) as iAmount
,sum(case when status='O' then amount else 0 end) as OAmount
from io group by date) t
order by date