动态表名的查询

wanghuaide 2011-01-27 02:45:08
这里是评论表
id productid bigclassid author contents support
1 1 4 whd test1 17
2 1 4 whd test2 38
3 1 4 whd test3 19
4 1 4 whd test4 16
5 36 4 wanghuaide test5 0
6 36 4 wanghuaide test6 6
7 36 4 wanghuaide test7 0
8 36 4 wanghuaide2002@163.com www 0
9 35 4 wanghuaide2002@163.com 0
10 35 4 wanghuaide2002@163.com sdf 1
11 35 4 wanghuaide2002@163.com ggg 1
12 34 4 wanghuaide2002@163.com www 5
24 34 4 wanghuaide2002@163.com whd 1
25 1 4 whd test4 16
26 11 11 whd test4 14
27 12 11 whd test4 14
28 12 11 whd test4 14
29 13 11 whd test4 14
30 13 11 whd test4 14
31 13 11 whd test4 14
32 14 11 whd test4 14
33 14 11 whd test4 14
34 14 11 whd test4 14
35 14 11 whd test4 14
36 14 11 whd test4 14
37 1 4 wanghuaide2002@163.com sdf 1
38 1 4 wanghuaide2002@163.com tt 1
经过如下语句
得到每个大类下面评论人数最多的排行榜(即每个产品的行数加每行的支持数)
productid为产品表里的ID号,现在还需要产品名称一列,根据大类ID(bigclassid)
从不同表名中根据productid取出产品名称
如当bigclassid为4时,去A表中取Name,为11时,去B表中调用Name
A表结构如下:
id Name
select top 10 productid,bigClassid,count(id)+sum(supportNum) as aField from tbcomments
group by productid,bigclassid order by aField desc
得到结果如下
productid bigclassid aField Name
1 4 115 A表中的name
14 11 75 B表中的name
13 11 45
12 11 30
11 11 15
36 4 10
34 4 8
35 4 5
bigclassid为大类的表名
...全文
259 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
叶子 2011-01-29
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 wanghuaide 的回复:]
如果还有@C表 呢?
[/Quote]

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
*/
wanghuaide 2011-01-29
  • 打赏
  • 举报
回复
如果还有@C表 呢?
叶子 2011-01-28
  • 打赏
  • 举报
回复

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
*/

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧