SELECT convert(char(7),date,120),sum(case when inout = 1 then sum else 0 end) r1, sum(case when inout = 0 then sum else 0 end) r2 from ( select * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="...\aa.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Orders$]) b group by convert(char(7),date,120),
导入sqlserver然后运行sql语句select * from ( select convert(varchar(7),date,120),case when inout='支出' then sum(金额) else 0 end as 支出,when inout='收入' then sum(金额) else 0 end as 收入 )t where 支出>收入
sum(case when inout = 0 then sum else 0 end) r2
from (
select *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source="...\aa.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Orders$]) b
group by convert(char(7),date,120),
(
select convert(varchar(7),date,120),case when inout='支出' then sum(金额) else 0 end
as 支出,when inout='收入' then sum(金额) else 0 end
as 收入
)t
where 支出>收入