关于一个排行的sql语句

[chang1216]

我现在有这样 三个表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HrbWeb_department]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HrbWeb_department]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Hrbweb_FK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Hrbweb_FK]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hrbweb_bbs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hrbweb_bbs]
GO

CREATE TABLE [dbo].[HrbWeb_department] (
[ModelID] [int] IDENTITY (1, 1) NOT NULL ,
[ModelTitle] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[UpModelID] [int] NULL ,
[IsLeaf] [bit] NULL ,
[PageUrl] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[ImgFileName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[sort] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Hrbweb_FK] (
[fankui_id] [int] IDENTITY (1, 1) NOT NULL ,
[bbs_id] [int] NULL ,
[fankui_content] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[fankui_date] [datetime] NULL ,
[fankui_bm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[hrbweb_bbs] (
[bbs_id] [int] IDENTITY (1, 1) NOT NULL ,
[bbs_user_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bbs_user_ip] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bbs_img] [int] NOT NULL ,
[bbs_title] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bbs_context] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[bbs_viewCount] [int] NOT NULL ,
[bbs_shouli] [int] NOT NULL ,
[bbs_date] [datetime] NOT NULL ,
[bbs_pass] [int] NULL ,
[bbs_bm] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
部门表 HrbWeb_department 问题表 hrbweb_bbs 回复表Hrbweb_FK
我想得到的是 每个部门 的回复数除于问题数 然后 根据 这个 比率排行 谢谢大家

[chuifengde]

抛去部门的包含关系可以

SELECT bm,aa.sl*1.0/bb.sl rate
from
(
	SELECT A.ModelTitle bm,count(1) sl FROM  HrbWeb_department A INNER JOIN Hrbweb_FK B 
	ON A.ModelTitle=B.fankui_bm
	GROUP BY A.ModelTitle
)aa
INNER JOIN 
(
	SELECT A.ModelTitle bm,count(1) sl FROM  HrbWeb_department A INNER JOIN Hrbweb_bbs B 
	ON A.ModelTitle=B.bbs_bm
	GROUP BY A.ModelTitle
)bb
ON aa.bm=bb.bm
ORDER BY 2 desc

[pt1314917]

--try:

select * from 
(select a.*,比率=c.回复数*1.0/b.问题数 from HrbWeb_department a left join 
(select bbs_bm,count(1)[问题数] from hrbweb_bbs group by bbs_bm) b on a.ModelTitle=b.bbs_bm
(select fankui_bm,count(1)[回复数] from Hrbweb_FK group by fankui_bm) c on a.ModelTitle=c.fankui_bm)d
order by 比率

[chang1216]

sum(回复数)/sum(问题数 )
这里 我感觉有点问题 我要是 某个部门的 回复数/问题数 而不是所有阿
而结果要显示 所有的部门 如果没有 问题数或 回复数 他的回复率 就显示0%
请问怎么解决

[chang1216]

树上的鸟儿 这位兄弟 写的 只列出了 有回复数的 记录 我需要把 所有的部门 都列出来 然后排序 谢谢！！

[xiangjin0106]

用LEFT JOIN所有部门就出来

[chang1216]

那么请问 怎么写呢？

[xiangjin0106]

改写一下
SELECT aa.bm,aa.sl*1.0/bb.sl rate
from
(
SELECT A.ModelTitle bm,case when count(fankui_id) is null then 0 else count(fankui_id) end sl FROM HrbWeb_department A LEFT JOIN Hrbweb_FK B
ON A.ModelTitle=B.fankui_bm
GROUP BY A.ModelTitle
)aa
INNER JOIN
(
SELECT A.ModelTitle bm,case when count(bbs_id) is null then 1 else count(bbs_id) end sl FROM HrbWeb_department A LEFT JOIN Hrbweb_bbs B
ON A.ModelTitle=B.bbs_bm
GROUP BY A.ModelTitle
)bb
ON aa.bm=bb.bm
ORDER BY aa.sl*1.0/bb.sl desc

[chang1216]

服务器: 消息 8134，级别 16，状态 1，行 1
遇到被零除错误。
警告: 聚合或其它 SET 操作消除了空值。

？

[dawugui]

select m.ModelTitle , isnull(n.col , 0) col from HrbWeb_department m
left join
(select bbs_bm , count(*) * 1.0 / count(distinct bbs_id) col from Hrbweb_FK group by bbs_bm) n
on m.ModelTitle = n.bbs_bm
order by n.col desc

--不必使用hrbweb_bbs表.

[chang1216]

不用这个 hrbweb_bbs 表 不可能吧 兄弟 那么 你的语句里 bbs_id 和 bbs_bm 从哪里来呢？
你这个 我改成了

select m.ModelTitle , isnull(n.col , 0) col from HrbWeb_department m
left join
(select bbs_bm , count(*) * 1.0 / count(distinct hrbweb_bbs.bbs_id) col from Hrbweb_FK,hrbweb_bbs group by bbs_bm) n
on m.ModelTitle = n.bbs_bm
order by n.col desc

可是 出来的结果 是不对的

[xiangjin0106]

SELECT aa.bm,isnull(aa.sl*1.0,0)/isnull(bb.sl,1) rate
from
(
SELECT A.ModelTitle bm,count(fankui_id) sl FROM HrbWeb_department A LEFT JOIN Hrbweb_FK B
ON A.ModelTitle=B.fankui_bm
GROUP BY A.ModelTitle
)aa
INNER JOIN
(
SELECT A.ModelTitle bm, count(bbs_id) sl FROM HrbWeb_department A LEFT JOIN Hrbweb_bbs B
ON A.ModelTitle=B.bbs_bm
GROUP BY A.ModelTitle
)bb
ON aa.bm=bb.bm
ORDER BY 2 desc

[chang1216]

计算机中心 4.000000000000
民政局 4.000000000000
统计局 4.000000000000
人民医院 .000000000000
地震局 .000000000000
政府办 .000000000000
出来了以上的结果 怎么 都是 4.0000 呢？ 这样是不正确的

[chang1216]

ctrl+c/v 兄弟 你的回答 提示 这个
服务器: 消息 8134，级别 16，状态 1，行 1
遇到被零除错误。
警告: 聚合或其它 SET 操作消除了空值。

[lllhhu]

哦,我也知道了

[chang1216]

没有人帮我看了吗？ 谢谢了 再帮我看看 ！！

[chuifengde]

SELECT bm,CASE WHEN isnull(bb.sl,0)=0 THEN 0 ELSE isnull(aa.sl,0)*1.0/bb.sl END rate
from
(
	SELECT A.ModelTitle bm,count(*) sl FROM  HrbWeb_department A LEFT JOIN Hrbweb_FK B 
	ON A.ModelTitle=B.fankui_bm
	GROUP BY A.ModelTitle
)aa
INNER JOIN 
(
	SELECT A.ModelTitle bm,count(*) sl FROM  HrbWeb_department A LEFT JOIN Hrbweb_bbs B 
	ON A.ModelTitle=B.bbs_bm
	GROUP BY A.ModelTitle
)bb
ON aa.bm=bb.bm
ORDER BY 2 desc

[xiangjin0106]

先不排序试试看

[chang1216]

树上的鸟儿 你的写法 已经 差不多了

现在的结果 显示是这样
科信局 1.000000000000
粮食局 1.000000000000
民政局 1.000000000000
民族宗教局 1.000000000000
人民医院 1.000000000000
市场科 1.000000000000
双鸭山政务网 1.000000000000
体育局 1.000000000000
统计局 1.000000000000
信息科 1.000000000000
信息中心 1.000000000000
招商局 1.000000000000
政府办 1.000000000000
执法局 1.000000000000
中小企业局 1.000000000000
安监局 1.000000000000
地震局 1.000000000000
发改委 1.000000000000
计算机中心 .666666666666
只有 最后这个 是对的 其他的 都应该是 0 啊 请问怎么改？

[chuifengde]

SELECT bm,CASE WHEN bb.sl=0 THEN 0 ELSE aa.sl*1.0/bb.sl END rate
from
(
    SELECT A.ModelTitle bm,sum(case when B.fankui_id is null then 0 else 1 end) sl FROM  HrbWeb_department A LEFT JOIN Hrbweb_FK B 
    ON A.ModelTitle=B.fankui_bm
    GROUP BY A.ModelTitle
)aa
INNER JOIN 
(
    SELECT A.ModelTitle bm,sum(case when B.bbs_id is null then 0 else 1 end ) sl FROM  HrbWeb_department A LEFT JOIN Hrbweb_bbs B 
    ON A.ModelTitle=B.bbs_bm
    GROUP BY A.ModelTitle
)bb
ON aa.bm=bb.bm
ORDER BY 2 desc

[fcuandy]

[Quote=引用 28 楼 chuifengde 的回复:]

SELECT aa.bm, aa.sl*1.0/ISNULL(NULLIF(bb.sl,0),1) rate
from
(
    SELECT A.ModelTitle bm,COUNT(B.fankui_id) sl FROM  HrbWeb_department A LEFT JOIN Hrbweb_FK B 
    ON A.ModelTitle=B.fankui_bm
    GROUP BY A.ModelTitle
)aa
INNER JOIN 
(
    SELECT A.ModelTitle bm,COUNT(B.bbs_id) sl FROM  HrbWeb_department A LEFT JOIN Hrbweb_bbs B 
    ON A.ModelTitle=B.bbs_bm
    GROUP BY A.ModelTitle
)bb
ON aa.bm=bb.bm
ORDER BY 2 desc

[/Quote]