全部
基础类
应用实例
新技术前沿

关于一个排行的sql语句

chang1216 2008-04-10 08:21:36

我现在有这样三个表
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
我想得到的是每个部门的回复数除于问题数然后根据这个比率排行谢谢大家

...全文

310 点赞收藏 30

写回复

30 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

fcuandy 2008-04-10

[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]

回复赞

chuifengde 2008-04-10

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

回复赞

chang1216 2008-04-10

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

现在的结果显示是这样
科信局 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 啊请问怎么改？

回复赞

xiangjin0106 2008-04-10

先不排序试试看

回复赞

chuifengde 2008-04-10

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

回复赞

chang1216 2008-04-10

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

回复赞

lllhhu 2008-04-10

哦,我也知道了

回复赞

chang1216 2008-04-10

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

回复赞

chang1216 2008-04-10

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

回复赞

xiangjin0106 2008-04-10

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 2008-04-10

不用这个 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

可是出来的结果是不对的

回复赞

dawugui 2008-04-10

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 2008-04-10

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

？

回复赞

xiangjin0106 2008-04-10

改写一下
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 2008-04-10

那么请问怎么写呢？

回复赞

xiangjin0106 2008-04-10

用LEFT JOIN所有部门就出来

回复赞

chang1216 2008-04-10

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

回复赞

chang1216 2008-04-10

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

回复赞

pt1314917 2008-04-10



--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 比率

回复赞

chuifengde 2008-04-10

抛去部门的包含关系可以

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