有一表tableA 字段如下:
Name ManageNO Task Pretime Realtime
a 001 x 4 6
a 002 y 5 3
a 003 z 8 10
b 004 t 5 3
b 006 n 7 10
字段XL為 Pretime/Realtime 字段SumXL 為 name 對應的 sum(pretime)/sum(realtime)
實現
Name ManageNO Task Pretime Realtime XL SumXL
a 001 x 4 6 0.66 0.89
002 y 5 3 1.66
003 z 8 10 0.8
b 004 t 5 3 1.66 0.92
006 n 7 10 0.7
請大俠們指點
Name ManageNO Task Pretime Realtime
a 001 x 4 6
a 002 y 5 3
a 003 z 8 10
b 004 t 5 3
b 006 n 7 10
字段XL為 Pretime/Realtime 字段SumXL 為 name 對應的 sum(pretime)/sum(realtime)
實現
Name ManageNO Task Pretime Realtime XL SumXL
a 001 x 4 6 0.66 0.89
002 y 5 3 1.66
003 z 8 10 0.8
b 004 t 5 3 1.66 0.92
006 n 7 10 0.7
請大俠們指點
解决方案 »
- 连接MYSQL遇到的问题,希望和大家讨论(不仅仅是针对这个问题)
- 企业名称、注册地址的智能分词 (RMB1000元 )
- 如何在delphi获得时间戳?
- 答的多,分得多?报表工具问题
- 请问这段程序在Delphi中该怎么写
- 语音交流
- 请问哪有把jpg,gif,png,bmp32和256色格式的文件转换成bmp16格式文件的源程序
- 在delphi6中fastreport应该安装哪个呢?
- ≈≈≈≈≈≈响应号召,请大家多多推荐Win32 SDK/API版2002年内的精华帖,感谢! ≈≈≈≈≈≈
- 用delphi操纵excel如:进行统计、过滤、查询、打印,用那种连接方式好?如何连,谢谢
- 急!如何让DLL和Exe文件共享同一个bpl的变量
- mysql连接问题?重金酬谢!再开贴!
a 001 x 4 6 0.66 0.89
002 y 5 3 1.66
003 z 8 10 0.8
b 004 t 5 3 1.66 0.92
006 n 7 10 0.7 請大俠們指點有點變形
tableA a,(select Name,sum(pretime)/sum(realtime) group by Name from TableA) b
不是用等号
select *,(Pretime/Realtime) as XL,(sum(pretime)/sum(realtime)) as SumXL from table
from tableA a,
(select Name,sum(pretime)/sum(realtime) as sumxl from TableA group by Name) b
查询出结果后用报表来做吧
name manageno task pretime realtime
a 001 x
a 002 y
a 003 z
b 004 t
b 005 m
已經實現
a 001 x
002 y
003 z
b 004 t
005 m可是客戶還要加總效率 sql就不好實現了
from tableA a
left join (select name, min(ManageNo) as ManageNo, sum(Pretime)/sum(Realtime) as sumXL from tableA group by name) b on a.ManageNo = b.ManageNo
a.Realtime, convert(decimal,a.Pretime,0)/convert(decimal,a.Realtime,0) as XL, b.sumXL
from tableA a
left join
(select name, min(ManageNo) as ManageNo,
convert(decimal, sum(pretime), 0)/convert(decimal, sum(realtime),0) as sumXL
from tableA group by name) b on a.ManageNo = b.ManageNo
a.Realtime, convert(decimal(4,2),a.Pretime,0)/convert(decimal(4,2),a.Realtime,0) as XL, b.sumXL
from mytest a
left join
(select name, min(ManageNo) as ManageNo,
convert(decimal(4,2), sum(pretime), 0)/convert(decimal(4,2), sum(realtime),0) as sumXL
from mytest group by name) b on a.ManageNo = b.ManageNo