有两张表T1,T2
T1(消费记录表):
日期 帐号 姓名 消费金额
090601 123 XX 85
090601 123 XX 96
090601 123 XX 120
090601 888 YY 87
090601 888 YY 88
090602 123 XX 83
090602 123 XX 89
090603 456 ZZ 130T2(存款表):
日期 帐号 姓名 存入金额
090601 123 XX 100
090601 888 YY 80
090602 123 XX 82
090603 456 ZZ 28 现在想求累计当日消费金额减去存入金额差额大于 100元的数据
所谓的数据就是把符合条件的帐号的消费记录显示出来
T1(消费记录表):
日期 帐号 姓名 消费金额
090601 123 XX 85
090601 123 XX 96
090601 123 XX 120
090601 888 YY 87
090601 888 YY 88
090602 123 XX 83
090602 123 XX 89
090603 456 ZZ 130T2(存款表):
日期 帐号 姓名 存入金额
090601 123 XX 100
090601 888 YY 80
090602 123 XX 82
090603 456 ZZ 28 现在想求累计当日消费金额减去存入金额差额大于 100元的数据
所谓的数据就是把符合条件的帐号的消费记录显示出来
from(
select 日期,账号,姓名,sum(消费金额) as sum1
from t1
group by 日期,账号,姓名
)T1,(
select 日期,账号,姓名,sum(存入金额) as sum1
from t1
group by 日期,账号,姓名)T2
where T1.日期=T2.日期 and T1.账号=T2.账号 and T1.姓名=T2.姓名
from (select 日期, 帐号, 姓名,sum(金额) 消费總金额 from T1 group by 日期,帐号) a,T2 b
where a.日期=b.日期 and a.帐号=b.帐号 and a.消费總金额-b.存入金额>100
GO
CREATE TABLE T1([日期] VARCHAR(6),[帐号] INT,[姓名] VARCHAR(2),[消费金额] INT)
INSERT T1
SELECT '090601',123,'XX',85 UNION ALL
SELECT '090601',123,'XX',96 UNION ALL
SELECT '090601',123,'XX',120 UNION ALL
SELECT '090601',888,'YY',87 UNION ALL
SELECT '090601',888,'YY',88 UNION ALL
SELECT '090602',123,'XX',83 UNION ALL
SELECT '090602',123,'XX',89 UNION ALL
SELECT '090603',456,'ZZ',130IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
GO
CREATE TABLE T2([日期] VARCHAR(6),[帐号] INT,[姓名] VARCHAR(2),[存入金额] INT)
INSERT T2
SELECT '090601',123,'XX',100 UNION ALL
SELECT '090601',888,'YY',80 UNION ALL
SELECT '090602',123,'XX',82 UNION ALL
SELECT '090603',456,'ZZ',28SELECT [日期],[帐号],[姓名],[消费金额]
FROM T1 B
WHERE EXISTS
(
SELECT 1
FROM
(
SELECT [日期],[帐号],[姓名],[消费金额] FROM T1
UNION ALL
SELECT [日期],[帐号],[姓名],0-[存入金额] FROM T2
) A
WHERE A.[日期]=B.[日期] AND A.[帐号]=B.[帐号] AND A.[姓名]=B.[姓名]
GROUP BY [日期],[帐号],[姓名]
HAVING SUM(A.[消费金额])>100
)
日期 帐号 姓名 消费金额
------ ----------- ---- -----------
090601 123 XX 85
090601 123 XX 96
090601 123 XX 120
090603 456 ZZ 130(4 行受影响)
SET NOCOUNT ON
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
GO
CREATE TABLE T1([日期] VARCHAR(6),[帐号] INT,[姓名] VARCHAR(2),[消费金额] INT)
INSERT T1
SELECT '090601',123,'XX',85 UNION ALL
SELECT '090601',123,'XX',96 UNION ALL
SELECT '090601',123,'XX',120 UNION ALL
SELECT '090601',888,'YY',87 UNION ALL
SELECT '090601',888,'YY',88 UNION ALL
SELECT '090602',123,'XX',83 UNION ALL
SELECT '090602',123,'XX',89 UNION ALL
SELECT '090603',456,'ZZ',130
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
GO
CREATE TABLE T2([日期] VARCHAR(6),[帐号] INT,[姓名] VARCHAR(2),[存入金额] INT)
INSERT T2
SELECT '090601',123,'XX',100 UNION ALL
SELECT '090601',888,'YY',80 UNION ALL
SELECT '090602',123,'XX',82 UNION ALL
SELECT '090603',456,'ZZ',28
SELECT T1.[日期], T1.[帐号],T1.[姓名],SUM([消费金额])-SUM([存入金额])
FROM T1 ,T2 WHERE T1.[日期]=T2.[日期] AND T1.[帐号]=T2.[帐号] GROUP BY
T1.[日期], T1.[帐号],T1.[姓名] HAVING SUM([消费金额])-SUM([存入金额])>100
/*日期 帐号 姓名
------ ----------- ---- -----------
090603 456 ZZ 102*/
where ABS(t2.存入金额-(select sum(消费金额) from t1 where t2.帐号=t1.帐号 and t2.日期=t1.日期 ))>100
GO
CREATE TABLE T1([日期] VARCHAR(6),[帐号] INT,[姓名] VARCHAR(2),[消费金额] INT)
INSERT T1
SELECT '090601',123,'XX',85 UNION ALL
SELECT '090601',123,'XX',96 UNION ALL
SELECT '090601',123,'XX',120 UNION ALL
SELECT '090601',888,'YY',87 UNION ALL
SELECT '090601',888,'YY',88 UNION ALL
SELECT '090602',123,'XX',83 UNION ALL
SELECT '090602',123,'XX',89 UNION ALL
SELECT '090603',456,'ZZ',130IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
GO
CREATE TABLE T2([日期] VARCHAR(6),[帐号] INT,[姓名] VARCHAR(2),[存入金额] INT)
INSERT T2
SELECT '090601',123,'XX',100 UNION ALL
SELECT '090601',888,'YY',80 UNION ALL
SELECT '090602',123,'XX',82 UNION ALL
SELECT '090603',456,'ZZ',28select * from t1
select * from t2
---------------------4---------------
select t1.日期,t1.姓名,t1.帐号,(SUM(t1.[消费金额])-sum(T2.存入金额)) as money from t1
join t2 on t2.姓名=t1.姓名 and T2.帐号=t1.帐号 group by t1.日期,t1.姓名,t1.帐号 having SUM(t1.[消费金额])-sum(T2.存入金额)>100-----------------------1------------------
select * from t1 where exists
(
select * from t2 join t1 on t1.姓名=T2.姓名
having SUM(t1.消费金额)-SUM(T2.存入金额)>100
)