日期:2014-05-17  浏览次数:20488 次

一个两表联合查询的问题!
有两张表
表1存储用户的基本信息
"userid" (主键)
"用户姓名"
.
.
.
表2:存储所用用户的购买信息
"userid"
"时间"
"购买金额"

现在是想查询某位用户具体的 "姓名", "最近一次消费记录", "平均每月消费金额"该怎样去做这样的一个查询?知道具体的userid



------解决方案--------------------
SQL code

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