有两张表
表1存储用户的基本信息
"userid" (主键)
"用户姓名"
.
.
.
表2:存储所用用户的购买信息
"userid"
"时间"
"购买金额"
现在是想查询某位用户具体的 "姓名", "最近一次消费记录", "平均每月消费金额"该怎样去做这样的一个查询?知道具体的userid
------解决方案--------------------
- SQL code
declare @userid intdeclare @avgMoney decimal(12,2)set @userid=4---这里取你要查询的用户IDCREATE 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 #aselect 1,'张三' union allselect 2,'李四' union allselect 3,'王五' union allselect 4,'赵六' insert into #bselect 1,'2012-01-01',10.00 union allselect 1,'2012-02-01',20.00 union allselect 1,'2012-03-01',30.00 union allselect 1,'2012-04-01',40.00 union allselect 2,'2012-01-01',10.00 union allselect 2,'2012-02-01',100.00 union allselect 2,'2012-03-01',200.00 union allselect 2,'2012-04-01',300.00 union allselect 3,'2012-01-01',111.00 union allselect 3,'2012-02-01',222.00 union allselect 3,'2012-03-01',333.00 union allselect 3,'2012-04-01',444.00 union allselect 4,'2012-01-01',1.00 union allselect 4,'2012-02-01',2.00 union allselect 4,'2012-03-01',3.00 union allselect 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=@useridorder by b.时间 desc drop table #adrop table #b---查询结果(4 行受影响)(16 行受影响)(1 行受影响)----------------------------------------用户姓名 时间 购买金额 平均每月消费赵六 2012-04-01 00:00:00.000 4.00 3.33