日期:2014-05-17 浏览次数:20571 次
create table #USER (id int,name nvarchar(10))
insert into #USER values(1,'张三')
insert into #USER values(2,'李四')
create table #Assess (id int , Aname nvarchar(10))
insert into #Assess values(1,'好评')
insert into #Assess values(2,'一般')
insert into #Assess values(3,'差评')
create table #UANDA ([uid] int,aid int)
insert into #UANDA values (1,1)
insert into #UANDA values (1,2)
insert into #UANDA values (1,1)
insert into #UANDA values (1,3)
insert into #UANDA values (2,1)
insert into #UANDA values (2,1)
insert into #UANDA values (2,3)
select u.name,
服务次数=(select COUNT(1) from #UANDA where uid=u.id),
好评次数=(select COUNT(1) from #UANDA where uid=u.id and aid=1),
一般次数=(select COUNT(1) from #UANDA where uid=u.id and aid=2),
差次数=(select COUNT(1) from #UANDA where uid=u.id and aid=3)
from #USER u
INNER JOIN #UANDA ua
ON u.id=ua.uid
INNER JOIN #Assess a
on ua.aid=a.id
group by u.name,u.id