34,575
社区成员
发帖
与我相关
我的任务
分享
declare @评论表 table (id int,productid int,bigclassid int,author varchar(22),contents varchar(5),support int)
insert into @评论表
select 1,1,4,'whd','test1',17 union all
select 2,1,4,'whd','test2',38 union all
select 3,1,4,'whd','test3',19 union all
select 4,1,4,'whd','test4',16 union all
select 5,36,4,'wanghuaide','test5',0 union all
select 6,36,4,'wanghuaide','test6',6 union all
select 7,36,4,'wanghuaide','test7',0 union all
select 8,36,4,'wanghuaide2002@163.com','www',0 union all
select 9,35,4,'wanghuaide2002@163.com','0',null union all
select 10,35,4,'wanghuaide2002@163.com','sdf',1 union all
select 11,35,4,'wanghuaide2002@163.com','ggg',1 union all
select 12,34,4,'wanghuaide2002@163.com','www',5 union all
select 24,34,4,'wanghuaide2002@163.com','whd',1 union all
select 25,1,4,'whd','test4',16 union all
select 26,11,11,'whd','test4',14 union all
select 27,12,15,'whd','test4',14 union all
select 28,12,15,'whd','test4',14 union all
select 29,13,11,'whd','test4',14 union all
select 30,13,11,'whd','test4',14 union all
select 31,13,11,'whd','test4',14 union all
select 32,14,11,'whd','test4',14 union all
select 33,14,11,'whd','test4',14 union all
select 34,14,11,'whd','test4',14 union all
select 35,14,11,'whd','test4',14 union all
select 36,14,11,'whd','test4',14 union all
select 37,1,4,'wanghuaide2002@163.com','sdf',1 union all
select 38,1,4,'wanghuaide2002@163.com','tt',1
declare @A表 table (id int,Name varchar(5))
insert into @A表
select 4,'Aname'
declare @B表 table (id int,Name varchar(5))
insert into @B表
select 11,'Bname'
declare @C表 table (id int,Name varchar(5))
insert into @C表
select 15,'Cname'
select aa.*,isnull(isnull(a.name,b.name),c.name) as name from (
select top 10 productid,bigClassid,
count(id)+sum(support) as aField
from @评论表
group by productid,bigclassid )
aa left join @A表 a on aa.bigClassid=a.id
left join @B表 b on aa.bigClassid=b.id
left join @C表 c on aa.bigClassid=c.id
order by aa.aField desc
/*
productid bigClassid aField name
----------- ----------- ----------- -----
1 4 115 Aname
14 11 75 Bname
13 11 45 Bname
12 15 30 Cname
11 11 15 Bname
36 4 10 Aname
34 4 8 Aname
35 4 5 Aname
*/
declare @评论表 table (id int,productid int,bigclassid int,author varchar(22),contents varchar(5),support int)
insert into @评论表
select 1,1,4,'whd','test1',17 union all
select 2,1,4,'whd','test2',38 union all
select 3,1,4,'whd','test3',19 union all
select 4,1,4,'whd','test4',16 union all
select 5,36,4,'wanghuaide','test5',0 union all
select 6,36,4,'wanghuaide','test6',6 union all
select 7,36,4,'wanghuaide','test7',0 union all
select 8,36,4,'wanghuaide2002@163.com','www',0 union all
select 9,35,4,'wanghuaide2002@163.com','0',null union all
select 10,35,4,'wanghuaide2002@163.com','sdf',1 union all
select 11,35,4,'wanghuaide2002@163.com','ggg',1 union all
select 12,34,4,'wanghuaide2002@163.com','www',5 union all
select 24,34,4,'wanghuaide2002@163.com','whd',1 union all
select 25,1,4,'whd','test4',16 union all
select 26,11,11,'whd','test4',14 union all
select 27,12,11,'whd','test4',14 union all
select 28,12,11,'whd','test4',14 union all
select 29,13,11,'whd','test4',14 union all
select 30,13,11,'whd','test4',14 union all
select 31,13,11,'whd','test4',14 union all
select 32,14,11,'whd','test4',14 union all
select 33,14,11,'whd','test4',14 union all
select 34,14,11,'whd','test4',14 union all
select 35,14,11,'whd','test4',14 union all
select 36,14,11,'whd','test4',14 union all
select 37,1,4,'wanghuaide2002@163.com','sdf',1 union all
select 38,1,4,'wanghuaide2002@163.com','tt',1
declare @A表 table (id int,Name varchar(5))
insert into @A表
select 4,'Aname'
declare @B表 table (id int,Name varchar(5))
insert into @B表
select 11,'Bname'
select aa.*,isnull(a.name,b.name) as name from (
select top 10 productid,bigClassid,
count(id)+sum(support) as aField
from @评论表
group by productid,bigclassid )
aa left join @A表 a on aa.bigClassid=a.id
left join @B表 b on aa.bigClassid=b.id
order by aa.aField desc
/*
productid bigClassid aField name
----------- ----------- ----------- -----
1 4 115 Aname
14 11 75 Bname
13 11 45 Bname
12 11 30 Bname
11 11 15 Bname
36 4 10 Aname
34 4 8 Aname
35 4 5 Aname
*/