SQL: Multi-Table Association take this record by far the largest

Author: Iamlasong

1. Demand

Two tables, delivery record table and sealing open the record table, now want to know the delivery date distance from the last date of the distribution of the number of days.

For this demand, you need to check out the delivery details, the same time to know the corresponding mail last closure. such as institutions, dates and so on.

2. Detailed inquiry

Consider that a day may be marshaled multiple times, so take the date and time is the largest one, the statement such as the following:

/p>

Select D.city,d.ssxs,d.zj_code,d.zj_mc,c.mail_num,       c.dlv_date,to_char (c.dlv_time, ' Hh24miss '), C.actual_goods _fee,       c.dlv_pseg_code,c.dlv_pseg_name,c.dlv_bureau_name,       c.dlv_staff_code,c.dlv_staff_name,c.signer_ Name,       a.deal_org_code,a.dlv_org_code,a.label_strip,a.deal_date,a.deal_time from  Tb_evt_bag_mail_rela A, TB_EVT_DLV C, tb_jg d where a.mail_num = C.mail_num and   a.bag_actn_code = ' 3 ' and   c.dlv_date between To_date (' 2014 -6-1 ', ' yyyy-mm-dd ') and       to_date (' 2014-6-1 ', ' yyyy-mm-dd ') and   C.dlv_bureau_org_code = D.zj_code   and C.dlv_sts_code = ' I ' and   d.jgfl = ' yz '   and (a.deal_date, a.deal_time) =       (select Max (t.deal_date), Max ( T.deal_time) from          Tb_evt_bag_mail_rela t         where t.mail_num = A.mail_num and           t.bag_actn_code = ' 3 '         Group by T.mail_num, T.bag_actn_code)

3. Time limit distribution

With the detail statement. The time distribution is simpler. Statements such as the following:

Select D.city, D.ssxs, D.zj_code, D.ZJ_MC, COUNT (*) Ttzl, Sum (Decode (c.dlv_date-a.deal_date, 0, 1, 0)) t0, S Um (Decode (c.dlv_date-a.deal_date, 1, 1, 0)) T1, sum (Decode (C.dlv_date-a.deal_date, 2, 1, 0)) T2, SUM (decod E (C.dlv_date-a.deal_date, 3, 1, 0)) T3, sum (Decode (C.dlv_date-a.deal_date, 4, 1, 0)) T4, sum (Decode (c.dlv_ Date-a.deal_date, 5, 1, 0)) T5 from Tb_evt_bag_mail_rela A, tb_evt_dlv C, tb_jg d where a.mail_num = C.mail_num and a . Bag_actn_code = ' 3 ' and c.dlv_date between To_date (' 2014-6-1 ', ' yyyy-mm-dd ') and to_date (' 2014-6-1 ', ' yyyy-mm-dd ') ) and C.dlv_bureau_org_code = D.zj_code and C.dlv_sts_code = ' I ' and D.JGFL = ' yz ' and (A.deal_date, A.deal_time) = (select Max (t.deal_date), Max (t.deal_time) from Tb_evt_bag_mail_rela t where T.mail_num = A.mail_ num and T.bag_actn_code = ' 3 ' GROUP by T.mail_num, T.bag_actn_code) group by d.city, D.ssxs, D.zj_code, D.ZJ_MC ORDER by D.city, D.ssxs, D.zj_code 

4. Existing problems and solutions

After the query results for the above statement come out. After checking, the number is not on, the records become less, the difference is very much. the check found that some of the messages did not have a distribution record. But that's a very small number, so what's the reason?

The original reason was on the last condition. The last condition is to find out the maximum date and maximum time. But. The maximum date of the record time is not necessarily the largest, resulting in these messages have been rinsed down. In order to get the right result. The last condition should read:

   and To_char (a.deal_date, ' YYYYMMDD ') | | To_char (a.deal_time, ' 000000 ') =       (select Max (To_char (t.deal_date, ' YYYYMMDD ') | | To_char (t.deal_time, ' 000000 ')) from          Tb_evt_bag_mail_rela t         where t.mail_num = A.mail_num and           t.bag_actn _code = ' 3 '         Group by T.mail_num, T.bag_actn_code)

the time in the format "000000" is converted because the time in the table is a numeric field of seconds, variable length. By the format "000000" converted after the uniform length, easy to compare size . For example, date-time synthesis results: 20140530 091239, that is, May 30, 2014 9:12 39 seconds.

Select D.city, D.ssxs, D.zj_code, D.ZJ_MC, COUNT (*) Ttzl, Sum (Decode (c.dlv_date-a.deal_date, 0, 1, 0)) t0, Sum (Decode (c.dlv_date-a.deal_date, 1, 1, 0)) T1, sum (Decode (C.dlv_date-a.deal_date, 2, 1, 0)) T2, S Um (Decode (c.dlv_date-a.deal_date, 3, 1, 0)) T3, sum (Decode (C.dlv_date-a.deal_date, 4, 1, 0)) T4, sum (D Ecode (C.dlv_date-a.deal_date, 5, 1, 0)) T5 from Tb_evt_bag_mail_rela A, tb_evt_dlv C, tb_jg d where a.mail_num = C. Mail_num and A.bag_actn_code = ' 3 ' and c.dlv_date between To_date (' 2014-6-1 ', ' yyyy-mm-dd ') and To_date (' 2 014-6-1 ', ' yyyy-mm-dd ') and C.dlv_bureau_org_code = D.zj_code and C.dlv_sts_code = ' I ' and D.JGFL = ' YZ ' A nd to_char (a.deal_date, ' YYYYMMDD ') | | To_char (a.deal_time, ' 000000 ') = (select Max (To_char (t.deal_date, ' YYYYMMDD ') | | To_char (t.deal_time, ' 000000 ')) from Tb_evt_bag_mail_rela t where T.mail_num = A.mail_num and T.bag _actn_code = ' 3 ' GrouP by T.mail_num, T.bag_actn_code) group by d.city, D.ssxs, D.zj_code, d.zj_mc order by d.city, D.ssxs, D.zj_code   

Finally, we need to explain. To_char in the specified format "000000" after the conversion, will be preceded by a space, but this does not affect the comparison. To_char This function after the assumption that there is no format specified, after the conversion there is no space, but the length is the actual length of the number, want to unify the length. Be able to add a large number. Like what

To_char (t.deal_time+9000000)

Conversion results: 201405309091239

Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced.

SQL: Multi-Table Association take this record by far the largest