mysql - 从两个以上的表中选择，每个表都没有关系

Question

我有三种不同类型的交易表，其中包含 3 种不同类型交易的数据。我必须显示其中每一个的数据，但问题是我必须混合这些行并根据日期排序。例如，我有 transac1、transac2、transac1 和一个用户表。transac1 就像

id txn_id user_id field_a date
1   223    1       23   12/12/12
2   r23    1       33   12/12/12

transac2 像

id txn_id user_id field_b date
1   nne    1       53     12/12/12
2   wr3    1       93     11/12/12

transac3 像

id txn_id user_id field_c  date
1   g4t3    1       73     12/12/12
2   d3ts    1       83     12/12/12

当我从这些数据中获取数据时，数据应该是这样的

id row_id txn_id user_id field_a field_b field_c table_name date
1  2   wr3        1       0       93      0      transac_2   11/12/12
2  1   223        1       23      0       0      transac_1   12/12/12
3  2   r23        1       33      0       0      transac_1   12/12/12
4  1   nne        1       0       53      0      transac_2   12/12/12
5  1   g4t3       1       0       0       73     transac_3   12/12/12
6  2   dt3s       1       0        0      83     transac_3   12/12/12

数据应该根据这个日期排序，表格将与其他表格分开连接。任何人都知道我该怎么做。顺便说一句，如果可能的话，我正在使用 Codeigniter 框架和 CI 的查询构建器，我将不胜感激。谢谢

Answer 1

试试这个解决方案:

This solution is for WITH ROW NUMBER

SELECT  @rownum := @rownum + 1 rownum , t.*
 FROM ( 
                 select id,txn_id,user_id,field_a, 0 as field_b, 0 as field_c,
                     'transac_1' as table_name , date from transac_1
               UNION ALL 
                 select id,txn_id,user_id,0 as field_a, field_b, 0 as field_c,
                     'transac_2' as table_name , date from transac_2
               UNION ALL
                 select id,txn_id,user_id,0 as field_a, 0 as field_b, field_c,
                     'transac_3' as table_name , date from transac_3 
    )t , (SELECT @rownum := 0) r Order by t.date desc

This solution is for WITHOUT ROW NUMBER

 SELECT  t.*
     FROM ( 
                     select id,txn_id,user_id,field_a, 0 as field_b, 0 as field_c,
                         'transac_1' as table_name , date from transac_1
                   UNION ALL 
                     select id,txn_id,user_id,0 as field_a, field_b, 0 as field_c,
                         'transac_2' as table_name , date from transac_2
                   UNION ALL
                     select id,txn_id,user_id,0 as field_a, 0 as field_b, field_c,
                         'transac_3' as table_name , date from transac_3 
        )t Order by t.date desc

希望此解决方案对您有所帮助。如果您需要其他任何东西或此解决方案不适合您，请告诉我。