编程论坛 → SQL Server论坛

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

qinyun 发布于 2008-06-09 12:01， 2006 次点击

SQL查询语句问题,请大家帮忙看一下
我现在有一个表如下
user_feiyong(应收) weixiu_feiyong(实收) chuangshou(创收) paodanfei(单费) paidan_name(工程师) keping(客户评价)

只有本站会员才能查看附件，请登录

如今我想用查询语句将其汇总

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

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

只有本站会员才能查看附件，请登录

<%
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
%>

13 回复

#2

qinyun2008-06-09 23:01

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

#3

bygg2008-06-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

#4

qinyun2008-06-11 21:05

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 "

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

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

#5

bygg2008-06-12 09:04

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

#6

qinyun2008-06-12 12:25

count(tno)这句是什么意思

#7

bygg2008-06-12 15:03

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

#8

jxyga1112008-06-12 15:05

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

count(tno)这句是什么意思

集合傳回集合中的項目數目

#9

qinyun2008-06-12 19:32

还是不行啊

#10

qinyun2008-06-12 19:44

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

附件:

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

#11

qinyun2008-06-13 17:54

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

#12

qinyun2008-06-15 16:02

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

#13

永夜的极光2008-06-15 16:48

数据库见附件

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

#14

yms1232008-06-15 17:47

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

1