我有一张表 字段 username type createtime remit
type 分为提款与存款
时间是一天
我想查询 一天的总提款-总存款,该怎么写?
之前我有想到子查询,但是性能太差,所以求高手赐教,希望能考虑到性能上的问题!模拟数据username type createtime renmit张山 cashin 2012-12-01 00:00:00 1000
李四 cashout 2012-12-01 00:00:00 1000
王五 cashin 2012-12-01 00:00:00 1000
sql
type 分为提款与存款
时间是一天
我想查询 一天的总提款-总存款,该怎么写?
之前我有想到子查询,但是性能太差,所以求高手赐教,希望能考虑到性能上的问题!模拟数据username type createtime renmit张山 cashin 2012-12-01 00:00:00 1000
李四 cashout 2012-12-01 00:00:00 1000
王五 cashin 2012-12-01 00:00:00 1000
sql
username 总提款 总存款
-- Author :DBA_Huangzj
-- Date :2013-01-08 19:34:08
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([username] varchar(4),[type] varchar(7),[createtime] datetime,[renmit] int)
insert [huang]
select '张山','cashin','2012-12-01 00:00:00',1000 union all
select '李四','cashout','2012-12-01 00:00:00',1000 union all
select '王五','cashin','2012-12-01 00:00:00',1000
--------------开始查询--------------------------
SELECT SUM(renmitout)renmitout,SUM(renmitint)renmitint,SUM(renmitout)-SUM(renmitint)
FROM (
select SUM(renmit)renmitout,0 renmitint
from [huang]
WHERE [createtime] BETWEEN CONVERT(VARCHAR(10),[createtime],120)+' 00:00:00.000' AND CONVERT(VARCHAR(10),[createtime],120)+' 23:59:59.997'
AND [type]='cashout'
UNION ALL
select 0 renmitout,SUM(renmit) renmitint
from [huang]
WHERE [createtime] BETWEEN CONVERT(VARCHAR(10),[createtime],120)+' 00:00:00.000' AND CONVERT(VARCHAR(10),[createtime],120)+' 23:59:59.997'
AND [type]='cashin')a
----------------结果----------------------------
/*
renmitout renmitint
----------- ----------- -----------
1000 2000 -1000(1 行受影响)*/
-- Author :DBA_Huangzj
-- Date :2013-01-08 19:34:08
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([username] varchar(4),[type] varchar(7),[createtime] datetime,[renmit] int)
insert [huang]
select '张山','cashin','2012-12-01 00:00:00',1000 union all
select '李四','cashout','2012-12-01 00:00:00',1000 union all
select '王五','cashin','2012-12-01 00:00:00',1000
--------------开始查询--------------------------
SELECT [username],SUM(renmitout)renmitout,SUM(renmitint)renmitint,SUM(renmitout)-SUM(renmitint)
FROM (
select [username],SUM(renmit)renmitout,0 renmitint
from [huang]
WHERE [createtime] BETWEEN CONVERT(VARCHAR(10),[createtime],120)+' 00:00:00.000' AND CONVERT(VARCHAR(10),[createtime],120)+' 23:59:59.997'
AND [type]='cashout'
GROUP BY [username]
UNION ALL
select [username],0 renmitout,SUM(renmit) renmitint
from [huang]
WHERE [createtime] BETWEEN CONVERT(VARCHAR(10),[createtime],120)+' 00:00:00.000' AND CONVERT(VARCHAR(10),[createtime],120)+' 23:59:59.997'
AND [type]='cashin'
GROUP BY [username]
)a
GROUP BY [username]
----------------结果----------------------------
/*
username renmitout renmitint
-------- ----------- ----------- -----------
李四 1000 0 1000
王五 0 1000 -1000
张山 0 1000 -1000(3 行受影响)*/
from tb where createtime >='2012-12-01' AND createtime <'2012-12-02' 要分级另外加字段进来
GO
-->生成表tbif object_id(N'tb') is not null
drop table [tb]
Go
Create table [tb]([username] nvarchar(2),[type] nvarchar(7),[createtime] datetime,[renmit] smallint)
Insert into [tb]
Select N'张山',N'cashin','2012-12-01 00:00:00',1000
Union all Select N'李四',N'cashout','2012-12-01 00:00:00',1000
Union all Select N'王五',N'cashin','2012-12-01 00:00:00',1000
--Union all Select N'王五',N'cashout','2012-12-01 00:00:00',500 -- test
--Union all Select N'王五',N'cashin','2012-12-02 00:00:00',1000 -- test
SELECT [createtime],[username],SUM(CASE WHEN [type]='cashout' THEN [renmit] ELSE 0 END) AS 总提款,SUM(CASE WHEN [type]='cashin' THEN [renmit] ELSE 0 END) AS 总存款
FROM tb
GROUP BY [createtime],[username]
ORDER BY [createtime],[username]
/*
createtime username 总提款 总存款
----------------------- -------- ----------- -----------
2012-12-01 00:00:00.000 王五 0 1000
2012-12-01 00:00:00.000 李四 1000 0
2012-12-01 00:00:00.000 张山 0 1000
*/