34,587
社区成员
发帖
与我相关
我的任务
分享
declare @userid int
declare @avgMoney decimal(12,2)
set @userid=4---这里取你要查询的用户ID
CREATE TABLE #a(
[ID] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NULL ,
[用户姓名][nvarchar](50) NULL
)
CREATE TABLE #b(
[ID] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NULL ,
[时间] datetime NULL,
[购买金额] decimal(12,2) NULL
)
insert into #a
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'赵六'
insert into #b
select 1,'2012-01-01',10.00 union all
select 1,'2012-02-01',20.00 union all
select 1,'2012-03-01',30.00 union all
select 1,'2012-04-01',40.00 union all
select 2,'2012-01-01',10.00 union all
select 2,'2012-02-01',100.00 union all
select 2,'2012-03-01',200.00 union all
select 2,'2012-04-01',300.00 union all
select 3,'2012-01-01',111.00 union all
select 3,'2012-02-01',222.00 union all
select 3,'2012-03-01',333.00 union all
select 3,'2012-04-01',444.00 union all
select 4,'2012-01-01',1.00 union all
select 4,'2012-02-01',2.00 union all
select 4,'2012-03-01',3.00 union all
select 4,'2012-04-01',4.00
select @avgMoney =(select sum(购买金额)/datediff(m,min(时间),max(时间)) from #b where userid=@userid)
select top 1 a.用户姓名,b.时间,b.购买金额, @avgMoney 平均每月消费
from #a a join #b b on a.userid=b.userid
where a.userid=@userid
order by b.时间 desc
drop table #a
drop table #b
---查询结果
(4 行受影响)
(16 行受影响)
(1 行受影响)
----------------------------------------
用户姓名 时间 购买金额 平均每月消费
赵六 2012-04-01 00:00:00.000 4.00 3.33
-- 最近一次消费记录
select b.用户姓名,a.时间,a.购买金额
from 表2 a
inner join 表1 b on a.userid=b.userid
inner join
(select userid,max(时间) '时间'
from 表2 where userid=[userid]) c
on a.userid=c.userid and a.时间=c.时间
where a.userid=[userid]
-- 平均每月消费金额
select b.用户姓名,
rtrim(datepart(mm,a.时间))+'月' '月份',
avg(a.购买金额) '平均消费金额'
from 表2 a
inner join 表1 b on a.userid=b.userid
where a.userid=[userid]
group by datepart(mm,a.时间)
declare @userid int
set @userid=3---这里取你要查询的用户ID
CREATE TABLE #a(
[ID] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NULL ,
[用户姓名][nvarchar](50) NULL
)
CREATE TABLE #b(
[ID] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NULL ,
[时间] datetime NULL,
[购买金额] decimal(12,2) NULL
)
insert into #a
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'赵六'
insert into #b
select 1,'2012-01-01',10.00 union all
select 1,'2012-02-01',21.00 union all
select 1,'2012-03-01',32.00 union all
select 1,'2012-04-01',43.00 union all
select 2,'2012-01-01',10.00 union all
select 2,'2012-02-01',24.00 union all
select 2,'2012-03-01',35.00 union all
select 2,'2012-04-01',46.00 union all
select 3,'2012-01-01',10.00 union all
select 3,'2012-02-01',25.00 union all
select 3,'2012-03-01',34.00 union all
select 3,'2012-04-01',43.00 union all
select 4,'2012-01-01',10.00 union all
select 4,'2012-02-01',22.00 union all
select 4,'2012-03-01',33.00 union all
select 4,'2012-04-01',44.00
select top 1 a.用户姓名,b.时间,b.购买金额,(select avg(购买金额) from #b where userid=@userid) 平均消费
from #a a join #b b on a.userid=b.userid
where a.userid=@userid
order by b.时间 desc
drop table #a
drop table #b
-----查询结果
用户姓名 时间 购买金额 平均消费
王五 2012-04-01 00:00:00.000 43.00 28.000000
(4 行受影响)
(16 行受影响)
(1 行受影响)