SQL查询语句问题,请大家帮忙看一下(页 1) - SQL Server论坛

qinyun 发表于 2008-6-9 12:01

SQL查询语句问题,请大家帮忙看一下

SQL查询语句问题,请大家帮忙看一下
我现在有一个表如下
user_feiyong(应收)  weixiu_feiyong(实收) chuangshou(创收) paodanfei(单费) paidan_name(工程师) keping(客户评价)
[attach]35341[/attach]
如今我想用查询语句将其汇总

目前我已能汇总user_feiyong(应收)  weixiu_feiyong(实收) chuangshou(创收) paodanfei(单费)也就是数字类型的汇总已完成,但不能统计客户评价这一栏数量,我要的结果是:

非常满意及比较满意(满意) 不满意投诉未联系上
         80%             10%    1%    9%

[attach]35340[/attach]
<%
dim xingming,date1,date2
xingming=request("paidan_name")
IF request("baoxiu_date1")<>"" And request("baoxiu_date2")<>"" Then
date1=request("baoxiu_date1")
date2=request("baoxiu_date2")
End IF
IF request("date1")<>"" And request("date2")<>"" Then
date1=request("date1")
date2=request("date2")
End IF

sql="select paidan_name,count(bx_id) as danshu,sum(user_feiyong) as user_feiyong,sum((chuangshou))as chuangshou,sum(weixiu_feiyong) as  weixiu_feiyong,sum(paodanfei) as paodanfei from baoxiu  where  baoxiu_date  between #"&date1&"#  and  #"&DateAdd("d",1,date2)&"#  group by paidan_name "
Set rs= Server.CreateObject("ADODB.Recordset")
rs.open sql,conn,1,1
%>

qinyun 发表于 2008-6-9 23:01

怎么就没有一位高手帮我一把呢.

bygg 发表于 2008-6-11 15:22

select
  convert(decimal(18,2),(sum(case when keping='非常满意' then 1 when keping='比较满意' then 1 else 0 end)+0.0)/count(tno)) as '满意',
  convert(decimal(18,2),(sum(case when keping='不满意' then 1 else 0 end)+0.0)/count(tno)) as '不满意',
  convert(decimal(18,2),(sum(case when keping='投诉' then 1 else 0 end)+0.0)/count(tno)) as '投诉',
  convert(decimal(18,2),(sum(case when keping='未联系上' then 1 else 0 end)+0.0)/count(tno)) as '未联系上'
from baoxiu

qinyun 发表于 2008-6-11 21:05

[code]sql="select paidan_name,convert(decimal(18,2),(sum(case when keping='非常满意' then 1 when keping='比较满意' then 1 else 0 end)+0.0)/count(tno)) as '满意',convert(decimal(18,2),(sum(case when keping='不满意' then 1 else 0 end)+0.0)/count(tno)) as '不满意',convert(decimal(18,2),(sum(case when keping='投诉' then 1 else 0 end)+0.0)/count(tno)) as '投诉',convert(decimal(18,2),(sum(case when keping='未联系上' then 1 else 0 end)+0.0)/count(tno)) as '未联系上',count(bx_id) as danshu,sum(user_feiyong) as user_feiyong,sum((chuangshou))as chuangshou,sum(weixiu_feiyong) as weixiu_feiyong,sum(paodanfei) as paodanfei from baoxiu where baoxiu_date between #"&date1&"# and #"&DateAdd("d",1,date2)&"# group by paidan_name "[/code]

这样用用不了啊,请指点一下,谢谢!

[[it] 本帖最后由 qinyun 于 2008-6-11 21:24 编辑 [/it]]

bygg 发表于 2008-6-12 09:04

你把下面这段去掉试试看
where baoxiu_date between #"&date1&"# and #"&DateAdd("d",1,date2)&"#

qinyun 发表于 2008-6-12 12:25

count(tno)这句是什么意思

bygg 发表于 2008-6-12 15:03

总数,你把tno换成你的表中的一个字段

jxyga111 发表于 2008-6-12 15:05

[quote][bo][un]qinyun[/un] 在 2008-6-12 12:25 的发言：[/bo]

count(tno)这句是什么意思 [/quote]
集合傳回集合中的項目數目

qinyun 发表于 2008-6-12 19:32

还是不行啊

qinyun 发表于 2008-6-12 19:44

这个客户评价让我伤透了脑筋,就是搞不定,我传上我的的数据库及页面代码请大家帮我看一下

附件:

[[it] 本帖最后由 qinyun 于 2008-6-12 20:01 编辑 [/it]]

qinyun 发表于 2008-6-13 17:54

大家帮个忙，帮看一下我的这个问题吧，我一直无法解决这个问题。郁闷啦！

qinyun 发表于 2008-6-15 16:02

这个汇总真是让我头痛啊.

永夜的极光 发表于 2008-6-15 16:48

数据库见附件

打开里面的"查询一"看看结果,用SQL视图看sql语句

yms123 发表于 2008-6-15 17:47

非常满意及比较满意(满意) 不满意投诉未联系上
80% 10% 1% 9%
这种效果应该是分组统计，估计一条SQL语句实现这个效果有些难度。

页: [1]