Sql统计语句求解

Question

数据库里有两个表
cash 表
id money datetime oparetor
==============================

expend 表
id subname money datetime oparetor
====================================

一个是收入表，一个是支出表
想用一句sql统计出每天收入支出情况
结果为：
日期 收入 支出
================

求指点

Answer 1

select Convert(varchar(10),datetime,23) as 日期,sum(in) as 收入,sum(out ) as 支出
from
(
select datetime, money as in, 0 as out from cash
union all
select datetime, 0 as in, money as out from expend
) as p
group by Convert(varchar(10),datetime,23)

把两张表合并成一张表然后将时间转换成日期分组统计。

采用吧。。。。

Answer 2

/* 首先按日期把收入和支出聚合出来 */
WITH IN AS(
SELECT CONVERT(date,datetime) AS 'DAY', SUM(money) OVER (PARTITION BY CONVERT(date,datetime)) AS 'INCOME'
FROM cash
)
OUT AS(
SELECT CONVERT(date,datetime) AS 'DAY', SUM(money) OVER (PARTITION BY CONVERT(date,datetime)) AS 'OUTPUT'
FROM expend
)

/* 然后拼接 */
SELECT IN.DAY, IN.INCOME, OUT.OUTPUT
FROM IN
INNER JOINE
OUT
ON IN.DAY = OUT.DAY

Answer 3

declare@income money
declare@expend money
select @income=count(money) from cash where strattime<datatime<endtime
select @expend=count(money) from expend where strattime<datatime<endtime
select @datetime as riqi ,@income as 收入,@expend as 支出

Answer 4

select convert(date, CT.datetime) as 日期,
SUM(CT.money) as 收入,
SUM(ET.money) as 支出 from cash CT, expend ET
where datediff(day,CT.datetime ,ET.datetime)=0
group by 日期;

Answer 5

select a.日期,a.收入,b.支出 from cash a,expend b where a.日期=b.日期