交易表
uid 金额 交易类型 交易日期
1 100 A 2009-1-1
1 100 A 2009-1-1
1 100 B 2009-1-2
2 100 A 2009-1-3
3 100 A 2009-1-4
交易表的交易类型有A,B两种.一个uid可以有交易类型AB两种或其中任意一种
我需要查询出某一用户一个时间段的交易查询UID为1的2009-1-1到2009-1-4的交易
交易日期 交易总金额类型A 交易总金额类型B
2009-1-1 200 0
2009-1-2 0 100
2009-1-3 0 0
2009-1-4 0 0查询UID为2的2009-1-1到2009-1-4的交易
交易日期 交易总金额类型A 交易总金额类型B
2009-1-1 0 0
2009-1-2 0 0
2009-1-3 100 0
2009-1-4 0 0
uid 金额 交易类型 交易日期
1 100 A 2009-1-1
1 100 A 2009-1-1
1 100 B 2009-1-2
2 100 A 2009-1-3
3 100 A 2009-1-4
交易表的交易类型有A,B两种.一个uid可以有交易类型AB两种或其中任意一种
我需要查询出某一用户一个时间段的交易查询UID为1的2009-1-1到2009-1-4的交易
交易日期 交易总金额类型A 交易总金额类型B
2009-1-1 200 0
2009-1-2 0 100
2009-1-3 0 0
2009-1-4 0 0查询UID为2的2009-1-1到2009-1-4的交易
交易日期 交易总金额类型A 交易总金额类型B
2009-1-1 0 0
2009-1-2 0 0
2009-1-3 100 0
2009-1-4 0 0
from tb
where UID=1 and 交易日期 between '2009-1-1' and '2009-1-4'
group by 交易日期
select 交易日期,sum(case 交易类型 when 'A' then 金额 else 0 end) 交易总金额类型A,sum(case 交易类型 when 'B' then 金额 else 0 end) 交易总金额类型B
from tb
where UID=2 and 交易日期 between '2009-1-1' and '2009-1-4'
group by 交易日期
-- Author : htl258(Tony)
-- Date : 2010-05-06 23:21:25
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [交易表]
IF OBJECT_ID('[交易表]') IS NOT NULL
DROP TABLE [交易表]
GO
CREATE TABLE [交易表] ([uid] [int],[金额] [int],[交易类型] [nvarchar](10),[交易日期] [datetime])
INSERT INTO [交易表]
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','B','2009-1-2' UNION ALL
SELECT '2','100','A','2009-1-3' UNION ALL
SELECT '3','100','A','2009-1-4'--SELECT * FROM [交易表]-->SQL查询如下:
select a.交易日期,sum(case 交易类型 when 'A' then 金额 else 0 end) 交易总金额类型A,sum(case 交易类型 when 'B' then 金额 else 0 end) 交易总金额类型B
from (select distinct convert(char,[交易日期],23) 交易日期 from [交易表]) a
left join [交易表] b
on a.交易日期=b.交易日期 and b.UID=1 and b.交易日期 between '2009-1-1' and '2009-1-4'
group by a.交易日期
/*
交易日期 交易总金额类型A 交易总金额类型B
------------------------------ ----------- -----------
2009-01-01 200 0
2009-01-02 0 100
2009-01-03 0 0
2009-01-04 0 0(4 行受影响)
*/
select a.交易日期,sum(case 交易类型 when 'A' then 金额 else 0 end) 交易总金额类型A,sum(case 交易类型 when 'B' then 金额 else 0 end) 交易总金额类型B
from (select distinct convert(char,[交易日期],23) 交易日期 from [交易表]) a
left join [交易表] b
on a.交易日期=b.交易日期 and b.UID=2 and b.交易日期 between '2009-1-1' and '2009-1-4'
group by a.交易日期
/*
交易日期 交易总金额类型A 交易总金额类型B
------------------------------ ----------- -----------
2009-01-01 0 0
2009-01-02 0 0
2009-01-03 100 0
2009-01-04 0 0(4 行受影响)
*/
sum(case 交易类型 when 'A' then 金额 else 0 end) as 易总金额类型A,
sum(case 交易类型 when 'B' then 金额 else 0 end) as 易总金额类型B
from 交易表
where 交易日期 between '2009-1-1' and '2009-1-4'
and uid=1
group by 交易日期
先看看行不行
if object_id('p_test') is not null
drop proc p_test
go
create proc p_test
@bt datetime,
@et datetime,
@uid int
as
select a.交易日期,sum(case 交易类型 when 'A' then 金额 else 0 end) 交易总金额类型A,sum(case 交易类型 when 'B' then 金额 else 0 end) 交易总金额类型B
from (select distinct convert(char,[交易日期],23) 交易日期 from [交易表] where 交易日期 between @bt and @et) a
left join [交易表] b
on a.交易日期=b.交易日期 and b.UID=@uid and b.交易日期 between @bt and @et
group by a.交易日期
goexec p_test '2009-1-1' , '2009-1-4',1
/*
交易日期 交易总金额类型A 交易总金额类型B
------------------------------ ----------- -----------
2009-01-01 200 0
2009-01-02 0 100
2009-01-03 0 0
2009-01-04 0 0(4 行受影响)
*/
exec p_test '2009-1-1' , '2009-1-4',2
/*
交易日期 交易总金额类型A 交易总金额类型B
------------------------------ ----------- -----------
2009-01-01 0 0
2009-01-02 0 0
2009-01-03 100 0
2009-01-04 0 0(4 行受影响)
*/
select t.交易日期 ,
sum(case 交易类型 when 'A' then 金额 else 0 end) as 易总金额类型A,
sum(case 交易类型 when 'B' then 金额 else 0 end) as 易总金额类型B
from (select distinct 交易日期 from 交易表)t left join 交易表 p
on t.交易日期=p.交易日期 and p.交易日期 between '2009-1-1' and '2009-1-4'
and p.uid=1
group by t.交易日期交易日期 易总金额类型A 易总金额类型B
------------------------------------------------------ ----------- -----------
2009-01-01 00:00:00.000 200 0
2009-01-02 00:00:00.000 0 100
2009-01-03 00:00:00.000 0 0
2009-01-04 00:00:00.000 0 0(所影响的行数为 4 行)
create table
trans(uid int,
trans_money decimal(18,4),
trans_type varchar(10),
transdate datetime
)insert into trans
select 1, 100, 'A', '2009-1-1'
union select 1, 100, 'A', '2009-1-1'
union select 1, 100, 'B', '2009-1-2'
union select 2, 100, 'A', '2009-1-3'
union select 3, 100, 'A', '2009-1-4'
select * from trans
/*****************************
1 100.0000 A 2009-01-01 00:00:00.000
1 100.0000 A 2009-01-01 00:00:00.000
1 100.0000 B 2009-01-02 00:00:00.000
2 100.0000 A 2009-01-03 00:00:00.000
3 100.0000 A 2009-01-04 00:00:00.000
*****************************/
create proc proc_find @id int,@begindate datetime,@enddate datetime
as
begin
select convert(char,transdate,23) as trans_date,
sum(case trans_type when 'A' then trans_money else 0 end) as total_typeA,
sum(case trans_type when 'B' then trans_money else 0 end) as total_typeB
from trans where transdate between @begindate and @enddate and uid=@id
group by transdate
endexec proc_find 1,'2009-1-1','2009-1-4'
/*****************************************
2009-01-01 200.0000 0.0000
2009-01-02 0.0000 100.0000
*****************************************/
if object_id('p_test') is not null
drop proc p_test
go
create proc p_test
@bt datetime,
@et datetime,
@uid int
as
select a.交易日期,sum(case 交易类型 when 'A' then 金额 else 0 end) 交易总金额类型A,
sum(case 交易类型 when 'B' then 金额 else 0 end) 交易总金额类型B
from (
select convert(varchar,dateadd(dd,number,@bt),23) 交易日期
from master..spt_values
where type='p'
and number <= datediff(dd,@bt,@et)
) a
left join [交易表] b
on a.交易日期=b.交易日期 and b.UID=@uid and b.交易日期 between @bt and @et
group by a.交易日期
goexec p_test '2009-1-1' , '2009-1-4',1
/*
交易日期 交易总金额类型A 交易总金额类型B
------------------------------ ----------- -----------
2009-01-01 200 0
2009-01-02 0 100
2009-01-03 0 0
2009-01-04 0 0(4 行受影响)
*/
exec p_test '2009-1-1' , '2009-1-4',2
/*
交易日期 交易总金额类型A 交易总金额类型B
------------------------------ ----------- -----------
2009-01-01 0 0
2009-01-02 0 0
2009-01-03 100 0
2009-01-04 0 0(4 行受影响)
*/考虑两个日期之间交易日期可能出现中段,故用自建的日期数据。
DROP TABLE [交易表]
GO
CREATE TABLE [交易表] ([uid] [int],[金额] [int],[交易类型] [nvarchar](10),[交易日期] [datetime])
INSERT INTO [交易表]
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','B','2009-1-2' UNION ALL
SELECT '2','100','A','2009-1-3' UNION ALL
SELECT '3','100','A','2009-1-4'
GO-->查询
declare @uid int,@sdt datetime,@edt datetime
select @uid=1
select @sdt='2009-1-1',@edt='2009-1-6'
select
[交易日期]=convert(varchar(10),dateadd(dd,a.number,@sdt),120),
[交易总金额类型A]=sum(case when 交易类型='A' then 金额 else 0 end),
[交易总金额类型B]=sum(case when 交易类型='B' then 金额 else 0 end)
from
master..spt_values a
left join
交易表 b
on
datediff(dd,dateadd(dd,a.number,@sdt),b.交易日期)=0 and b.uid=@uid
where
dateadd(dd,a.number,@sdt)<=@edt
and
a.type='P'
group by
convert(varchar(10),dateadd(dd,a.number,@sdt),120)
-->结果
/**
交易日期 交易总金额类型A 交易总金额类型B
---------- ----------- -----------
2009-01-01 200 0
2009-01-02 0 100
2009-01-03 0 0
2009-01-04 0 0
2009-01-05 0 0
2009-01-06 0 0(6 行受影响)**/
create table
trans(uid int,
trans_money decimal(18,4),
trans_type varchar(10),
transdate datetime
)insert into trans
select 1, 100, 'A', '2009-1-1'
union select 1, 100, 'A', '2009-1-1'
union select 1, 100, 'B', '2009-1-2'
union select 2, 100, 'A', '2009-1-3'
union select 3, 100, 'A', '2009-1-4'
select * from trans--uid=1
SELECT Sum(Case WHEN trans_type='A' AND uid=1 THEN trans_money ELSE 0 END) AS 交易总金额类型A,
Sum(Case WHEN trans_type='B' AND uid=1 THEN trans_money ELSE 0 END) AS 交易总金额类型B,
transdate
FROM trans
GROUP BY transdate/*
100.0000 0.0000 2009-01-01 00:00:00.000
0.0000 100.0000 2009-01-02 00:00:00.000
0.0000 0.0000 2009-01-03 00:00:00.000
0.0000 0.0000 2009-01-04 00:00:00.000*/
create table
trans(uid int,
trans_money decimal(18,4),
trans_type varchar(10),
transdate datetime
)insert into trans
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','B','2009-1-2' UNION ALL
SELECT '2','100','A','2009-1-3' UNION ALL
SELECT '3','100','A','2009-1-4'
--uid=1
SELECT Sum(Case WHEN trans_type='A' AND uid=1 THEN trans_money ELSE 0 END) AS 交易总金额类型A,
Sum(Case WHEN trans_type='B' AND uid=1 THEN trans_money ELSE 0 END) AS 交易总金额类型B,
transdate
FROM trans
GROUP BY transdate/*
200.0000 0.0000 2009-01-01 00:00:00.000
0.0000 100.0000 2009-01-02 00:00:00.000
0.0000 0.0000 2009-01-03 00:00:00.000
0.0000 0.0000 2009-01-04 00:00:00.000*/
CREATE TABLE [交易表] ([uid] [int],[金额] [int],[交易类型] [nvarchar](10),[交易日期] [datetime])
INSERT INTO [交易表]
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','B','2009-1-2' UNION ALL
SELECT '2','100','A','2009-1-3' UNION ALL
SELECT '3','100','A','2009-1-4'
SELECT 交易日期,
[交易总金额类型A]=SUM(CASE WHEN 交易类型='A' THEN 金额 ELSE 0 END),
[交易总金额类型A]=SUM(CASE WHEN 交易类型='B' THEN 金额 ELSE 0 END)
FROM 交易表 WHERE 交易日期 between '2009-1-1' and '2009-1-4' GROUP BY 交易日期
drop table 交易表
如果需要知道是UID的话
就用
CREATE TABLE [交易表] ([uid] [int],[金额] [int],[交易类型] [nvarchar](10),[交易日期] [datetime])
INSERT INTO [交易表]
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','B','2009-1-2' UNION ALL
SELECT '2','100','A','2009-1-3' UNION ALL
SELECT '3','100','A','2009-1-4'SELECT UID,交易日期,
[交易总金额类型A]=SUM(CASE WHEN 交易类型='A' THEN 金额 ELSE 0 END),
[交易总金额类型A]=SUM(CASE WHEN 交易类型='B' THEN 金额 ELSE 0 END)
FROM 交易表 WHERE 交易日期 between '2009-1-1' and '2009-1-4' GROUP BY UID,交易日期
CREATE TABLE [交易表] ([uid] [int],[金额] [int],[交易类型] [nvarchar](10),[交易日期] [datetime])
INSERT INTO [交易表]
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','A','2009-1-1' UNION ALL
SELECT '1','100','B','2009-1-2' UNION ALL
SELECT '2','100','A','2009-1-3' UNION ALL
SELECT '3','100','A','2009-1-4'
SELECT UID,交易日期,
[交易总金额类型A]=SUM(CASE WHEN 交易类型='A' THEN 金额 ELSE 0 END),
[交易总金额类型B]=SUM(CASE WHEN 交易类型='B' THEN 金额 ELSE 0 END)
FROM 交易表 WHERE 交易日期 between '2009-1-1' and '2009-1-4' GROUP BY UID,交易日期不好意思,上面的有两个交易类型A,复制的忘了改!