表DD_YieldAccidentInfo结构 列名 数据类型 说明 AccidentDate DateTime 事故发生时间 Department char 事故发生部门(各部门名称,比如自动化中心,供电队,装车队等) EffectTime int 事故影响时间 AccidentType char 事故类型(机电,管理,运输)我想实现的目标是根据部门名称来统计各种类型事故(机电,管理,运输)在某月发生的次数和影响时间,以及合计。比如,8月装车队发生了5次运输事故,每次影响时间假如都是50分钟,发生管理事故一起,影响时间80分钟,则查询给出的结果是: Department(装车队) 机电事故次数(0) 机电影响时间(0) 管理事故次数(1) 管理影响时间(80) 运输事故次数(5) 运输影响时间(250) 合计事故次数(6) 合计影响时间(330)注:在括号里面是想要得到的结果,这里只能这样写,请各位把它放在数据库中测试我想用嵌套的select语句写出这种查询,请问如何实现才好?select SELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime FROM dbo.DD_YieldAccidentInfo WHERE AccidentType = '机电' -- GROUP BY Department,SELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime FROM dbo.DD_YieldAccidentInfo WHERE AccidentType = '管理' -- GROUP BY Department,
SELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime FROM dbo.DD_YieldAccidentInfo WHERE AccidentType = '机电' GROUP BY Department union allSELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime FROM dbo.DD_YieldAccidentInfo WHERE AccidentType = '管理' GROUP BY Department
select a.Department,a.AccidentCount as aAccidentCount , a.SumEffectTime as aSumEffectTime, b.AccidentCount as bAccidentCount,b.SumEffectTime as bSumEffectTime from (SELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime FROM dbo.DD_YieldAccidentInfo WHERE AccidentType = '机电' GROUP BY Department) as a inner join //具体用INNER OR LEFT OR ROGHT OR CROSS看你自己的情况了 (SELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime FROM dbo.DD_YieldAccidentInfo WHERE AccidentType = '管理' GROUP BY Department) as b on a.Department=b.Department
to myflok(阿棋) :union all肯定是不行的,并不能把列并列起来,只是增加了行数 ====>Just Seeing Hearing Saying Waitting Loving<====
select Department,sum(jdcs),sum(jdsj),sum(glcs),sum(glsj),sum(yscs),sum(yssj),sum(jdcs)+sum(jdsj)+sum(glcs) as hjcs,sum(glsj)+sum(yscs)+sum(yssj) as hjsj from ( select Department,count(*) as jdcs,sum(effecttime) as jdsj,0 as glcs,0 as glsj,0 as yscs,0 as yssj from DD_YieldAccidentInfo where accidenttype='机电' group by Department union all 参照以上方法写管理部分 union all 参照以上方法写运输部分 ) as a group by Department
to vincege(热得快):非常感谢您的帮助,我已经把它调试出来了,非常感谢 ====>Just Seeing Hearing Saying Waitting Loving<====
列名 数据类型 说明
AccidentDate DateTime 事故发生时间
Department char 事故发生部门(各部门名称,比如自动化中心,供电队,装车队等)
EffectTime int 事故影响时间
AccidentType char 事故类型(机电,管理,运输)我想实现的目标是根据部门名称来统计各种类型事故(机电,管理,运输)在某月发生的次数和影响时间,以及合计。比如,8月装车队发生了5次运输事故,每次影响时间假如都是50分钟,发生管理事故一起,影响时间80分钟,则查询给出的结果是:
Department(装车队) 机电事故次数(0) 机电影响时间(0) 管理事故次数(1) 管理影响时间(80) 运输事故次数(5) 运输影响时间(250) 合计事故次数(6) 合计影响时间(330)注:在括号里面是想要得到的结果,这里只能这样写,请各位把它放在数据库中测试我想用嵌套的select语句写出这种查询,请问如何实现才好?select
SELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime
FROM dbo.DD_YieldAccidentInfo
WHERE AccidentType = '机电'
-- GROUP BY Department,SELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime
FROM dbo.DD_YieldAccidentInfo
WHERE AccidentType = '管理'
-- GROUP BY Department,
====>Just Seeing Hearing Saying Waitting Loving<====
FROM dbo.DD_YieldAccidentInfo
WHERE AccidentType = '机电'
GROUP BY Department
union allSELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime
FROM dbo.DD_YieldAccidentInfo
WHERE AccidentType = '管理'
GROUP BY Department
b.AccidentCount as bAccidentCount,b.SumEffectTime as bSumEffectTime
from
(SELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime
FROM dbo.DD_YieldAccidentInfo
WHERE AccidentType = '机电'
GROUP BY Department) as a
inner join //具体用INNER OR LEFT OR ROGHT OR CROSS看你自己的情况了
(SELECT Department,count(*) AS AccidentCount,SUM(EffectTime) AS SumEffectTime
FROM dbo.DD_YieldAccidentInfo
WHERE AccidentType = '管理'
GROUP BY Department) as b
on a.Department=b.Department
====>Just Seeing Hearing Saying Waitting Loving<====
from (
select Department,count(*) as jdcs,sum(effecttime) as jdsj,0 as glcs,0 as glsj,0 as yscs,0 as yssj from DD_YieldAccidentInfo where accidenttype='机电' group by Department
union all
参照以上方法写管理部分
union all
参照以上方法写运输部分
) as a
group by Department
====>Just Seeing Hearing Saying Waitting Loving<====