select top 50 1 as sousid,a.ID,a.keyword,
sumcount=(select count(*) from Fact_Access b
where b.cid=23 and b.sousid=1 and b.keyid=a.ID
and b.intime>='2006-05-01' and b.intime<='2006-05-31'),
sumvalue=(select isnull(sum(c.accessvalue),0) from Fact_Access c
where c.cid=23 and c.sousid=1 and c.keyid=a.ID
and c.intime>='2006-05-01' and c.intime<='2006-05-31')
from stat_keyword a
order by sumvalue desc
union
select top 50 2 as sousid,j.ID,j.keyword,
sumcount=(select count(*) from Fact_Access h
where h.cid=23 and h.sousid=2 and h.keyid=j.ID
and h.intime>='2006-05-01' and h.intime<='2006-05-31'),
sumvalue=(select isnull(sum(i.accessvalue),0) from Fact_Access i
where i.cid=23 and i.sousid=2 and i.keyid=j.ID
and i.intime>='2006-05-01' and i.intime<='2006-05-31')
from stat_keyword j
order by sumvalue desc
sumcount=(select count(*) from Fact_Access b
where b.cid=23 and b.sousid=1 and b.keyid=a.ID
and b.intime>='2006-05-01' and b.intime<='2006-05-31'),
sumvalue=(select isnull(sum(c.accessvalue),0) from Fact_Access c
where c.cid=23 and c.sousid=1 and c.keyid=a.ID
and c.intime>='2006-05-01' and c.intime<='2006-05-31')
from stat_keyword a
order by sumvalue desc
union
select top 50 2 as sousid,j.ID,j.keyword,
sumcount=(select count(*) from Fact_Access h
where h.cid=23 and h.sousid=2 and h.keyid=j.ID
and h.intime>='2006-05-01' and h.intime<='2006-05-31'),
sumvalue=(select isnull(sum(i.accessvalue),0) from Fact_Access i
where i.cid=23 and i.sousid=2 and i.keyid=j.ID
and i.intime>='2006-05-01' and i.intime<='2006-05-31')
from stat_keyword j
order by sumvalue desc
解决方案 »
- 末设置主键的情况下,如何实现将A表中的前10条记录剪切到B表中?
- 请教一个关于联合查询的SQL语句?请高手帮帮忙!谢谢!!
- 如何判断某一个登陆SQLServer的用户是不是远程登陆的用户(外网用户) ???
- 求一select统计语句(续)
- 急死啦!卸载了sqlserver2008,安装2005,报错!在线等
- 咨询一下:在编写存储过程时,大家喜欢编写通用型的存储过程还是专用型的存储过程
- 求问有关check约束 检查表满足条件的行数的语句
- 禁止SQL本机Windows验证登录是如何做到的?
- sql server 写个存储过程计算平均单价!
- 为了帮助新手的成长,哪位肯捐点分给我?
- 求值問題!急!!
- 求一个E-R图设计,谢谢了!
sumcount=(select count(*) from Fact_Access b
where b.cid=23 and b.sousid=1 and b.keyid=a.ID
and b.intime>='2006-05-01' and b.intime<='2006-05-31'),
sumvalue=(select isnull(sum(c.accessvalue),0) from Fact_Access c
where c.cid=23 and c.sousid=1 and c.keyid=a.ID
and c.intime>='2006-05-01' and c.intime<='2006-05-31')
from stat_keyword a
order by sumvalue desc
union
select top 50 2 as sousid,j.ID as ID ,j.keyword as keyword,
sumcount=(select count(*) from Fact_Access h
where h.cid=23 and h.sousid=2 and h.keyid=j.ID
and h.intime>='2006-05-01' and h.intime<='2006-05-31'),
sumvalue=(select isnull(sum(i.accessvalue),0) from Fact_Access i
where i.cid=23 and i.sousid=2 and i.keyid=j.ID
and i.intime>='2006-05-01' and i.intime<='2006-05-31')
from stat_keyword j
order by sumvalue desc
这样写只能一个,你把union前面那个order by排序去掉就可以了,不过可能不是你要的结果
sumcount=(select count(*) from Fact_Access b
where b.cid=23 and b.sousid=1 and b.keyid=a.ID
and b.intime>='2006-05-01' and b.intime<='2006-05-31'),
sumvalue=(select isnull(sum(c.accessvalue),0) from Fact_Access c
where c.cid=23 and c.sousid=1 and c.keyid=a.ID
and c.intime>='2006-05-01' and c.intime<='2006-05-31')
from stat_keyword a
--order by sumvalue desc
union
select top 50 2 as sousid,j.ID,j.keyword,
sumcount=(select count(*) from Fact_Access h
where h.cid=23 and h.sousid=2 and h.keyid=j.ID
and h.intime>='2006-05-01' and h.intime<='2006-05-31'),
sumvalue=(select isnull(sum(i.accessvalue),0) from Fact_Access i
where i.cid=23 and i.sousid=2 and i.keyid=j.ID
and i.intime>='2006-05-01' and i.intime<='2006-05-31')
from stat_keyword j
order by sumvalue desc
FROM
(SELECT TOP 50 1 AS sousid,a.ID,a.keyword,
sumcount=(SELECT COUNT(*) FROM Fact_Access b
WHERE b.cid=23 AND b.sousid=1 AND b.keyid=a.ID
AND b.intime>='2006-05-01' AND b.intime<='2006-05-31'),
sumvalue=(SELECT isnull(SUM(c.accessvalue),0) FROM Fact_Access c
WHERE c.cid=23 AND c.sousid=1 AND c.keyid=a.ID
AND c.intime>='2006-05-01' AND c.intime<='2006-05-31')
FROM stat_keyword a
UNION
SELECT TOP 50 2 AS sousid,j.ID,j.keyword,
sumcount=(SELECT COUNT(*) FROM Fact_Access h
WHERE h.cid=23 AND h.sousid=2 AND h.keyid=j.ID
AND h.intime>='2006-05-01' AND h.intime<='2006-05-31'),
sumvalue=(SELECT isnull(SUM(i.accessvalue),0) FROM Fact_Access i
WHERE i.cid=23 AND i.sousid=2 AND i.keyid=j.ID
AND i.intime>='2006-05-01' AND i.intime<='2006-05-31')
FROM stat_keyword j) a
ORDER BY sumvalue DESC