可能你按3个字段分组了,虽然t.mstr_day_id相同,但是后面2个字段不同,而你在结果中却没列出来,所以结果看起来会重复,你试试:select t.mstr_day_id, z.dest_offi_name, z.trk_name ... ... group by t.mstr_day_id, z.dest_offi_name, z.trk_name
select t.mstr_day_id, z.dest_offi_name, z.trk_name from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z where t.trk_id = z.trk_id and z.trk_name = 901 and t.s_hour = 19 and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1 and t.mstr_day_id >= 100*t.mstr_month_id+1 and t.mstr_month_id = round(20060423/100,0) group by t.mstr_day_id, z.dest_offi_name, z.trk_name这个没问题啊,有24条记录
group by t.mstr_day_id, z.dest_offi_name, z.trk_name应该是24条记录啊,你前面怎么出来170多条记录了
select t.mstr_day_id, z.dest_offi_name, z.trk_name from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z where t.trk_id = z.trk_id --and z.trk_name = 901 and t.s_hour = 19 and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1 and t.mstr_day_id >= 100*t.mstr_month_id+1 and t.mstr_month_id = round(20060423/100,0) group by t.mstr_day_id, z.dest_offi_name, z.trk_name-------------------------------------------------------- 你把and z.trk_name = 901条件去掉再看看
恩,搞清楚了:) select t.mstr_day_id, z.dest_offi_name, z.trk_name from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z where t.trk_id = z.trk_id --and z.trk_name = 901 and t.s_hour = 19 and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1 and t.mstr_day_id >= 100*t.mstr_month_id+1 and t.mstr_month_id = round(20060423/100,0) group by t.mstr_day_id, z.dest_offi_name, z.trk_name这样有2500多条,因为这里没有指定trk_name,所有的全部都列出来了, 把你写的那个sql加一个指定的trk_name就ok了:)select distinct t.mstr_day_id, sum(decode(z.trk_name,901, t.seiz,0)), sum(decode(z.trk_name,901, t.answ,0)), math_div(sum(decode(z.trk_name,901, t.answ,0)), sum(decode(z.trk_name,901, t.seiz,0)), 0, 4), sum(decode(z.trk_name,902, t.seiz,0)), sum(decode(z.trk_name,902, t.answ,0)), math_div(sum(decode(z.trk_name,902, t.answ,0)), sum(decode(z.trk_name,902, t.seiz,0)), 0, 4) from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z where t.trk_id = z.trk_id and z.trk_name = 901 and t.s_hour = 19 and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1 and t.mstr_day_id >= 100*t.mstr_month_id+1 and t.mstr_month_id = round(20060423/100,0) group by t.mstr_day_id, z.dest_offi_name, z.trk_name,t.trk_id order by t.mstr_day_id万分感谢 !!! duanzilin(寻) ( ) 信誉:120 这个decode函数还不错,以后要多多学习:)
select t.mstr_day_id,
sum(t.seiz),
sum(t.answ),
math_div(sum(t.answ), sum(t.seiz), 0, 4)
from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z
where t.trk_id = z.trk_id
and z.trk_name = 901
and t.s_hour = 19
and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1
and t.mstr_day_id >= 100*t.mstr_month_id+1
and t.mstr_month_id = round(20060423/100,0)
group by t.mstr_day_id, z.dest_offi_name, z.trk_name
结果:
1 20060401 2061 994 0.4823
2 20060402 1786 879 0.4922
3 20060403 1305 679 0.5203
......sql(2):
select t.mstr_day_id,
sum(t.seiz),
sum(t.answ),
math_div(sum(t.answ), sum(t.seiz), 0, 4)
from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z
where t.trk_id = z.trk_id
and z.trk_name = 902
and t.s_hour = 19
and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1
and t.mstr_day_id >= 100*t.mstr_month_id+1
and t.mstr_month_id = round(20060423/100,0)
group by t.mstr_day_id, z.dest_offi_name, z.trk_name
结果:
1 20060401 1293 715 0.553
2 20060402 1187 623 0.5249
3 20060403 1060 624 0.5887
4 20060404 1276 701 0.5494
......两个sql中只有trk_name 不同,最后我要实现如下的格式:
1 20060401 1293 715 0.553 2061 994 0.4823
2 20060402 1187 623 0.5249 2061 994 0.4823
3 20060403 1060 624 0.5887 2061 994 0.4823
4 20060404 1276 701 0.5494 2061 994 0.4823
.......
刚刚试过了你的方法好像不行 :(
sum(decode(z.trk_name,901, t.seiz,0)),
sum(decode(z.trk_name,901, t.answ,0)),
math_div(sum(decode(z.trk_name,901, t.answ,0)), sum(decode(z.trk_name,901, t.seiz,0)), 0, 4),
sum(decode(z.trk_name,902, t.seiz,0)),
sum(decode(z.trk_name,902, t.answ,0)),
math_div(sum(decode(z.trk_name,902, t.answ,0)), sum(decode(z.trk_name,902, t.seiz,0)), 0, 4),
from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z
where t.trk_id = z.trk_id
--and z.trk_name = 901
and t.s_hour = 19
and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1
and t.mstr_day_id >= 100*t.mstr_month_id+1
and t.mstr_month_id = round(20060423/100,0)
group by t.mstr_day_id, z.dest_offi_name, z.trk_name
1 20060401 2061 994 0.4823 0 0 0
2 20060401 0 0 0 0 0 0
3 20060401 0 0 0 0 0 0
...
66 20060401 0 0 0 1293 715 0.553
67 20060401 0 0 0 0 0 0
68 20060401 0 0 0 0 0 0
....
108 20060401 0 0 0 0 0 0
109 20060402 1786 879 0.4922 0 0 0
110 20060402 0 0 0 0 0 0
...
173 20060402 0 0 0 0 0 0
174 20060402 0 0 0 1187 623 0.5249
175 20060402 0 0 0 0 0 0
...
在t.mstr_day_id,前加distinct 还是不行,如下:
1 20060401 0 0 0 0 0 0
2 20060401 0 0 0 1293 715 0.553
3 20060401 2061 994 0.4823 0 0 0
4 20060402 0 0 0 0 0 0
5 20060402 0 0 0 1187 623 0.5249
6 20060402 1786 879 0.4922 0 0 0
7 20060403 0 0 0 0 0 0
8 20060403 0 0 0 1060 624 0.5887
9 20060403 1305 679 0.5203 0 0 0
10 20060404 0 0 0 0 0 0
11 20060404 0 0 0 1276 701 0.5494
12 20060404 1752 826 0.4715 0 0 0
13 20060405 0 0 0 0 0 0
......
位置都错开了,问题出在哪呢??
3q~~~~~~
...
group by t.mstr_day_id, z.dest_offi_name, z.trk_name
from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z
where t.trk_id = z.trk_id
and z.trk_name = 901
and t.s_hour = 19
and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1
and t.mstr_day_id >= 100*t.mstr_month_id+1
and t.mstr_month_id = round(20060423/100,0)
group by t.mstr_day_id, z.dest_offi_name, z.trk_name这个没问题啊,有24条记录
from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z
where t.trk_id = z.trk_id
--and z.trk_name = 901
and t.s_hour = 19
and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1
and t.mstr_day_id >= 100*t.mstr_month_id+1
and t.mstr_month_id = round(20060423/100,0)
group by t.mstr_day_id, z.dest_offi_name, z.trk_name--------------------------------------------------------
你把and z.trk_name = 901条件去掉再看看
select t.mstr_day_id, z.dest_offi_name, z.trk_name
from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z
where t.trk_id = z.trk_id
--and z.trk_name = 901
and t.s_hour = 19
and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1
and t.mstr_day_id >= 100*t.mstr_month_id+1
and t.mstr_month_id = round(20060423/100,0)
group by t.mstr_day_id, z.dest_offi_name, z.trk_name这样有2500多条,因为这里没有指定trk_name,所有的全部都列出来了,
把你写的那个sql加一个指定的trk_name就ok了:)select distinct t.mstr_day_id,
sum(decode(z.trk_name,901, t.seiz,0)),
sum(decode(z.trk_name,901, t.answ,0)),
math_div(sum(decode(z.trk_name,901, t.answ,0)), sum(decode(z.trk_name,901, t.seiz,0)), 0, 4),
sum(decode(z.trk_name,902, t.seiz,0)),
sum(decode(z.trk_name,902, t.answ,0)),
math_div(sum(decode(z.trk_name,902, t.answ,0)), sum(decode(z.trk_name,902, t.seiz,0)), 0, 4)
from tsppi_trfs_tkgp_i t, zhangsj_ts_tkgp z
where t.trk_id = z.trk_id
and z.trk_name = 901
and t.s_hour = 19
and t.mstr_day_id <= 100*(t.mstr_month_id+1)+1
and t.mstr_day_id >= 100*t.mstr_month_id+1
and t.mstr_month_id = round(20060423/100,0)
group by t.mstr_day_id, z.dest_offi_name, z.trk_name,t.trk_id
order by t.mstr_day_id万分感谢 !!! duanzilin(寻) ( ) 信誉:120
这个decode函数还不错,以后要多多学习:)
sum(decode(z.trk_name,902, t.seiz,0)),
sum(decode(z.trk_name,902, t.answ,0)),
math_div(sum(decode(z.trk_name,902, t.answ,0)), sum(decode(z.trk_name,902, t.seiz,0)),
的结果了:(