情况是这样的,表callinfo有四个字段
1:业务名称(varchar) ; 2:等待时长(int) ;3:办理时长(int); 4:日期(datetime)
表中字段和数据如下
====================================================
业务名称;等待时长;办理时长;日期;
业务A 0 268 2010-09-01
业务A 240 294 2010-09-01
业务A 840 168 2010-09-01
业务A 1006 243 2010-09-01
业务B 0 326 2010-09-01
业务B 323 237 2010-09-01
业务B 536 585 2010-09-01
业务B 1115 123 2010-09-01
业务C 0 590 2010-09-01
业务C 587 204 2010-09-01
业务C 788 480 2010-09-01
业务C 1264 420 2010-09-01
业务A 0 604 2010-09-02
业务A 601 363 2010-09-02
业务A 960 300 2010-09-02
业务A 1256 240 2010-09-02
业务B 0 1492 2010-09-02
业务B 1488 300 2010-09-02
业务B 1785 480 2010-09-02
业务B 2259 240 2010-09-02
业务C 0 360 2010-09-02
业务C 359 240 2010-09-02
业务C 594 360 2010-09-02
业务C 951 420 2010-09-02
业务A 0 420 2010-09-03
业务A 360 240 2010-09-03
业务A 540 120 2010-09-03
业务A 640 600 2010-09-03
=====================================================================
现在我要得到每天的每个业务的最大等待时间和最大办理时间,查询出来的结果,类似如下:业务名称;等待时长;办理时长;日期;
业务A 1006 294 2010-09-01
业务B 1115 585 2010-09-01
业务C 1264 590 2010-09-01
业务A 1256 604 2010-09-02
业务B 2259 1492 2010-09-02
业务C 951 420 2010-09-02
业务A 640 600 2010-09-03
========================================================================
sql语句怎么写???
1:业务名称(varchar) ; 2:等待时长(int) ;3:办理时长(int); 4:日期(datetime)
表中字段和数据如下
====================================================
业务名称;等待时长;办理时长;日期;
业务A 0 268 2010-09-01
业务A 240 294 2010-09-01
业务A 840 168 2010-09-01
业务A 1006 243 2010-09-01
业务B 0 326 2010-09-01
业务B 323 237 2010-09-01
业务B 536 585 2010-09-01
业务B 1115 123 2010-09-01
业务C 0 590 2010-09-01
业务C 587 204 2010-09-01
业务C 788 480 2010-09-01
业务C 1264 420 2010-09-01
业务A 0 604 2010-09-02
业务A 601 363 2010-09-02
业务A 960 300 2010-09-02
业务A 1256 240 2010-09-02
业务B 0 1492 2010-09-02
业务B 1488 300 2010-09-02
业务B 1785 480 2010-09-02
业务B 2259 240 2010-09-02
业务C 0 360 2010-09-02
业务C 359 240 2010-09-02
业务C 594 360 2010-09-02
业务C 951 420 2010-09-02
业务A 0 420 2010-09-03
业务A 360 240 2010-09-03
业务A 540 120 2010-09-03
业务A 640 600 2010-09-03
=====================================================================
现在我要得到每天的每个业务的最大等待时间和最大办理时间,查询出来的结果,类似如下:业务名称;等待时长;办理时长;日期;
业务A 1006 294 2010-09-01
业务B 1115 585 2010-09-01
业务C 1264 590 2010-09-01
业务A 1256 604 2010-09-02
业务B 2259 1492 2010-09-02
业务C 951 420 2010-09-02
业务A 640 600 2010-09-03
========================================================================
sql语句怎么写???
FROM [TB]
group by 业务名称,日期
,max([办理时长]) as [办理时长]
,[日期]
from TB
group by [业务名称],[日期]
DROP TABLE [tb]
CREATE TABLE [tb]
(
[业务名称] varchar(100) NULL ,
[等待时长] int NULL ,
[办理时长] int NULL ,
[日期] datetime NULL
)
GO--插入测试数据
INSERT INTO [tb] ([业务名称],[等待时长],[办理时长],[日期])
SELECT '业务A','0','268','2010-09-01' UNION
SELECT '业务A','240','294','2010-09-01' UNION
SELECT '业务A','840','168','2010-09-01' UNION
SELECT '业务A','1006','243','2010-09-01' UNION
SELECT '业务B','0','326','2010-09-01' UNION
SELECT '业务B','323','237','2010-09-01' UNION
SELECT '业务B','536','585','2010-09-01' UNION
SELECT '业务B','1115','123','2010-09-01' UNION
SELECT '业务C','0','590','2010-09-01' UNION
SELECT '业务C','587','204','2010-09-01' UNION
SELECT '业务C','788','480','2010-09-01' UNION
SELECT '业务C','1264','420','2010-09-01' UNION
SELECT '业务A','0','604','2010-09-02' UNION
SELECT '业务A','601','363','2010-09-02' UNION
SELECT '业务A','960','300','2010-09-02' UNION
SELECT '业务A','1256','240','2010-09-02' UNION
SELECT '业务B','0','1492','2010-09-02' UNION
SELECT '业务B','1488','300','2010-09-02' UNION
SELECT '业务B','1785','480','2010-09-02' UNION
SELECT '业务B','2259','240','2010-09-02' UNION
SELECT '业务C','0','360','2010-09-02' UNION
SELECT '业务C','359','240','2010-09-02' UNION
SELECT '业务C','594','360','2010-09-02' UNION
SELECT '业务C','951','420','2010-09-02' UNION
SELECT '业务A','0','420','2010-09-03' UNION
SELECT '业务A','360','240','2010-09-03' UNION
SELECT '业务A','540','120','2010-09-03' UNION
SELECT '业务A','640','600','2010-09-03'
GO
SELECT 业务名称,max(等待时长)等待时长,max(办理时长)办理时长,日期 FROM tb group by 日期,业务名称 order by 日期
第一行:SELECT 业务名称,max(等待时长)等待时长,max(办理时长)办理时长,日期 FROM 第二行:SELECT max(等待时长)等待时长,max(办理时长)办理时长 FROM
========================================================================
按照第二行写能够查询出来正确的数据,
按照第一行那样写,不行,报如下错:服务器: 消息 8120,级别 16,状态 1,行 1
列 '业务名称' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
列 '业务名称' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
说明GROUP BY 后面无【业务名称】,加之即可IF EXISTS (SELECT * FROM sys.objects WHERE NAME ='tb')
DROP TABLE [tb]
CREATE TABLE [tb]
(
[业务名称] varchar(100) NULL ,
[等待时长] int NULL ,
[办理时长] int NULL ,
[日期] datetime NULL
)
GO--插入测试数据
INSERT INTO [tb] ([业务名称],[等待时长],[办理时长],[日期])
SELECT '业务A','0','268','2010-09-01' UNION
SELECT '业务A','240','294','2010-09-01' UNION
SELECT '业务A','840','168','2010-09-01' UNION
SELECT '业务A','1006','243','2010-09-01' UNION
SELECT '业务B','0','326','2010-09-01' UNION
SELECT '业务B','323','237','2010-09-01' UNION
SELECT '业务B','536','585','2010-09-01' UNION
SELECT '业务B','1115','123','2010-09-01' UNION
SELECT '业务C','0','590','2010-09-01' UNION
SELECT '业务C','587','204','2010-09-01' UNION
SELECT '业务C','788','480','2010-09-01' UNION
SELECT '业务C','1264','420','2010-09-01' UNION
SELECT '业务A','0','604','2010-09-02' UNION
SELECT '业务A','601','363','2010-09-02' UNION
SELECT '业务A','960','300','2010-09-02' UNION
SELECT '业务A','1256','240','2010-09-02' UNION
SELECT '业务B','0','1492','2010-09-02' UNION
SELECT '业务B','1488','300','2010-09-02' UNION
SELECT '业务B','1785','480','2010-09-02' UNION
SELECT '业务B','2259','240','2010-09-02' UNION
SELECT '业务C','0','360','2010-09-02' UNION
SELECT '业务C','359','240','2010-09-02' UNION
SELECT '业务C','594','360','2010-09-02' UNION
SELECT '业务C','951','420','2010-09-02' UNION
SELECT '业务A','0','420','2010-09-03' UNION
SELECT '业务A','360','240','2010-09-03' UNION
SELECT '业务A','540','120','2010-09-03' UNION
SELECT '业务A','640','600','2010-09-03'
SELECT 业务名称,max(等待时长)等待时长,max(办理时长)办理时长,日期 FROM tb group by 日期,业务名称 order by 日期业务名称 等待时长 办理时长 日期
---------------------------------------------------------------------------------------------------- ----------- ----------- -----------------------
业务A 1006 294 2010-09-01 00:00:00.000
业务B 1115 585 2010-09-01 00:00:00.000
业务C 1264 590 2010-09-01 00:00:00.000
业务A 1256 604 2010-09-02 00:00:00.000
业务B 2259 1492 2010-09-02 00:00:00.000
业务C 951 420 2010-09-02 00:00:00.000
业务A 640 600 2010-09-03 00:00:00.000(7 行受影响)
--------------------------------------------------------------
业务id;业务名称;等待时长;办理时长;日期;
1001 业务A 1006 294 2010-09-01
1002 业务B 1115 585 2010-09-01
1003 业务C 1264 590 2010-09-01
1001 业务A 1256 604 2010-09-02
1002 业务B 2259 1492 2010-09-02
1003 业务C 951 420 2010-09-02
1001 业务A 640 600 2010-09-03
========================================================================
select
[业务id],
[业务名称],
max([等待时长]) as [等待时长],
max([办理时长]) as [办理时长],
[日期]
from TB
group by [业务名称],[日期] order by [日期]
=========================================================================
还是报上面的错误;
在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
明显的.select [业务id],[业务名称],group by [业务名称],[日期] ,
业务ID不在group by 中.要得ID,必须再与其它表连接.
select A.[业务id],B.* from [业务表] as A inner join
(select
[业务名称],
max([等待时长]) as [等待时长],
max([办理时长]) as [办理时长],
[日期]
from TB
group by [业务名称],[日期] order by [日期]) as B
on A.[业务名称]=B.[业务名称]
这里姑且认定有个业务表,业务名称应该是唯一的.
不知是否能理解?
你这里面又多了一个 业务IDgroup by 后面没有的;都要报错
我要的是开始一样的结果;
FROM [TB]
group by 业务名称,日期正确
-现在领导又说要得到每周的每个业务的最大等待时间和最大办理时间,
该如何来写sql语句呢?
call.oname oname ,
max(call.waittime) max_waittime ,
max(call.worktime) max_worktime ,
DATENAME(wk,call.dd) dddfrom
(
SELECT A.oid oid,
A.oname oname,
A.wname wname ,
DATEDIFF(minute, A.getdate,A.calldate) waittime ,
DATEDIFF(minute, A.calldate,A.finishdate) worktime ,
A.predate dd
FROM callinfo A where predate between '2010-9-1' and '2010-9-11 23:59:59') call
group by DATENAME(wk,dd),oname order by DATENAME(wk,dd)
-------------------------------------------------------------------------
等待时长通过getdate和calldate的差值得到,
办理时长通过calldate和finishdate的差值得到;
功能:统计每个业务的每一周的最大等待时长和最大办理时长;
原始表中的必需字段要有:oname,getdate,calldate,predate;
-------------------------------------------------------------------------
上面的sql语句,稍加改动,就可以实现你想要的,(子查询的结果就是你原表中的数据)