SELECT A.FJOBID,MIN(A.FLSTUPDTIME),a.fpickarea,sum(a.frealchests),count(distinct b.farticle),count(distinct a.fstore)
FROM TPICKUP A,TPICKUPDTL B
WHERE A.NUM = B.NUM
AND A.FJOBID >= '0809010001'
AND A.FJOBID <= '0809020001'
GROUP BY A.FJOBID,a.fpickarea;
结果
0809010001 2008-9-1 上午 08:16:18 20 25124 967 876
0809010001 2008-9-1 上午 07:30:50 30 3640 303 799
0809010001 2008-9-1 上午 08:09:08 38 0 3 4
0809010001 2008-9-1 上午 07:25:23 40 0 514 936
0809010001 2008-9-1 上午 08:21:40 1001 19007 299 914
0809010001 2008-9-1 上午 08:21:37 1002 20382 305 911
0809010001 2008-9-1 上午 08:16:16 1003 8688 271 880
0809010001 2008-9-1 上午 08:19:29 1004 8484 272 888
select FJOBID,fpickarea,sum(frealchests)
from tpickup
where FJOBID >= '0809010001'
AND FJOBID <= '0809020001'
GROUP BY FJOBID ,fpickarea;
结果
0809010001 20 955
0809010001 30 803
0809010001 38 0
0809010001 40 0
0809010001 1001 1062
0809010001 1002 1041
0809010001 1003 907
0809010001 1004 914
0809020001 20 1192
0809020001 30 842
0809020001 38 0 sum(frealchests),求出来的结果不同,实际上是每二个那个求和是对的,第一个问题在哪里
FROM TPICKUP A,TPICKUPDTL B
WHERE A.NUM = B.NUM
AND A.FJOBID >= '0809010001'
AND A.FJOBID <= '0809020001'
GROUP BY A.FJOBID,a.fpickarea;
结果
0809010001 2008-9-1 上午 08:16:18 20 25124 967 876
0809010001 2008-9-1 上午 07:30:50 30 3640 303 799
0809010001 2008-9-1 上午 08:09:08 38 0 3 4
0809010001 2008-9-1 上午 07:25:23 40 0 514 936
0809010001 2008-9-1 上午 08:21:40 1001 19007 299 914
0809010001 2008-9-1 上午 08:21:37 1002 20382 305 911
0809010001 2008-9-1 上午 08:16:16 1003 8688 271 880
0809010001 2008-9-1 上午 08:19:29 1004 8484 272 888
select FJOBID,fpickarea,sum(frealchests)
from tpickup
where FJOBID >= '0809010001'
AND FJOBID <= '0809020001'
GROUP BY FJOBID ,fpickarea;
结果
0809010001 20 955
0809010001 30 803
0809010001 38 0
0809010001 40 0
0809010001 1001 1062
0809010001 1002 1041
0809010001 1003 907
0809010001 1004 914
0809020001 20 1192
0809020001 30 842
0809020001 38 0 sum(frealchests),求出来的结果不同,实际上是每二个那个求和是对的,第一个问题在哪里
然后再说清楚逆向要的结果,
一会就有人帮你搞定了看你的sql,搞不懂要干什么
SELECT A.FJOBID,MIN(A.FLSTUPDTIME),a.fpickarea,sum(a.frealchests),count(distinct b.farticle),count(distinct a.fstore)
FROM TPICKUP A,TPICKUPDTL B
WHERE A.NUM = B.NUM(+)
AND A.FJOBID >= '0809010001'
AND A.FJOBID <= '0809020001'
GROUP BY A.FJOBID,a.fpickarea;加个外连接试试,或者你两个表连接出现重复记录
而第二个查询没有加min条件,会把 所有的FLSTUPDTIME的值进行汇总
num fjobid time pickarea total frealchests
90760610290001 0610290001 2006-10-29 上午 11:09:53 0312 30 286.50 1.0000
90760610290002 0610290001 2006-10-29 上午 11:09:53 0323 30 77.20 1.0000
90760610290003 0610290001 2006-10-29 上午 11:13:04 0385 30 129.40 1.0000
90760610290004 0610290001 2006-10-29 上午 11:14:05 0517 30 54.35 1.0000
90760610290005 0610290001 2006-10-29 上午 11:16:15 0574 30 150.10 1.0000
90760610290006 0610290001 2006-10-29 上午 11:16:15 0720 30 61.96 1.0000
90760610290007 0610290001 2006-10-29 上午 11:18:21 0303 30 190.55 1.0000B表:
num fbin farticle
90760610290001 30010222 1025015 1.0000 1.0000 1 14
90760610290001 30010352 1019119 1.0000 12.0000 12 11
90760610290001 30010441 1021897 1.0000 3.0000 3 13
90760610290001 30020922 1000790 1.0000 12.0000 12 7
90760610290001 30021041 1004409 1.0000 3.0000 3 2
90760610290001 30030152 1000782 1.0000 8.0000 8 6
90760610290001 30030222 1003539 1.0000 1.0000 1 8
90760610290001 30030233 1012324 1.0000 2.0000 2 10
90760610290001 30030432 1019120 1.0000 12.0000 12 12
90760610290001 30030512 1010320 1.0000 1.0000 1 9
90760610290001 30040552 1004457 1.0000 5.0000 5 3
90760610290001 30041032 1000643 1.0000 4.0000 4 4
90760610290002 30010252 1005554 1.0000 6.0000 6 1
90760610290002 30020922 1000790 1.0000 12.0000 12 3
90760610290002 30040742 1009469 1.0000 1.0000 1 4
FROM TPICKUP A,TPICKUPDTL B
WHERE A.NUM = B.NUM
AND A.FJOBID >= '0809010001'
AND A.FJOBID <= '0809020001'
GROUP BY A.FJOBID,a.fpickarea;
得出来的结果与
select count(distinct farticle) from tpickupdtl where jobid>=......select count(distinct fstore) from tpickup where ...
是一样的.就是SUM(FREALFCHEST)不同