从两个表中选择结果并合并结果

Given the following example tables.

给出以下示例表。

 CREDITS

 +-------+--------------+--------------+---------------------------+
 |   ID  |   userid     |     value    |  date_activated           |
 +-------+--------------+--------------+---------------------------+
 |   1   |   1722       |     50       |  2012-11-08 22:24:41      |
 +-------+--------------+--------------+---------------------------+
 |   2   |   3242       |     10       |  2012-11-07 22:24:41      |
 +-------+--------------+--------------+---------------------------+
 |   3   |   23232      |     20       |  2012-11-06 22:24:41      |
 +-------+--------------+--------------+---------------------------+
 |   4   |   1722       |     30       |  2012-11-19 22:24:33      |
 +-------+--------------+--------------+---------------------------+

 EXPENSE

 +-------+--------------+--------------+---------------------------+
 |   ID  |   userid     |     value    |  date_spent               |
 +-------+--------------+--------------+---------------------------+
 |   1   |   1722       |     10.20    |  2012-11-18 22:24:41      |
 +-------+--------------+--------------+---------------------------+
 |   2   |   3242       |     2.00     |  2012-11-03 22:24:41      |
 +-------+--------------+--------------+---------------------------+
 |   3   |   23232      |     20.00    |  2012-11-01 22:24:41      |
 +-------+--------------+--------------+---------------------------+
 |   4   |   1722       |     18.00    |  2012-11-20 22:24:33      |
 +-------+--------------+--------------+---------------------------+

I would like to achieve the following result.

我想实现以下结果。

 |   1722       |     50       |  2012-11-08 22:24:41      |
 |   1722       |     10.20    |  2012-11-18 22:24:41      |
 |   1722       |     30       |  2012-11-19 22:24:33      |
 |   1722       |     30       |  2012-11-19 22:24:33      |
 |   1722       |     18.00    |  2012-11-20 22:24:33      |
 |   3242       |     10       |  2012-11-07 22:24:41      |    
 |   3242       |     2.00     |  2012-11-03 22:24:41      |
 |   23232      |     20.00    |  2012-11-01 22:24:41      |
 |   23232      |     20.00    |  2012-11-01 22:24:41      |

How I would normally do this would be to do something like the following

我通常如何做这件事就是做以下事情

  $query = "SELECT * FROM CREDITS ORDER BY user id";
  $result = mysql_query($query);
  while ($row = mysql_fetch_array( $result ))
  {
       $combind_result = $combind_result.$row['userid']."|".$row['value']."|".$row['date_activated']."|"; 
       $user_id = $row['user_id'];
       $query2 = "SELECT * FROM EXPENSE userid = '$user_id' ORDER BY date_spent";
       $result2 = mysql_query($query2);
       while ($row2 = mysql_fetch_array( $result2 ))
           {
           $combind_result.$row2['userid']."|".$row2['value']."|".$row2['spent']."|"; 
           }
   }        

   echo $combind_result;

Probably some typos in there but I think it shows the concept. Is there a better way to do this?

可能在那里有一些错别字,但我认为它显示了这个概念。有一个更好的方法吗?

3 个解决方案

#1

Try using a UNION ALL.

尝试使用UNION ALL。

SELECT user_id, value, date_activated FROM CREDITS
UNION ALL
SELECT user_id, value, date_activated FROM EXPENSE

And you might want the second select to read:

你可能想要第二个选择:

SELECT user_id, value * -1, date_activated FROM EXPENSE, so that you can get the math right between a credit versus an expense. You could also add a code to further help with differentiation between credit and expense.

SELECT user_id,value * -1,date_activated FROM EXPENSE,这样您就可以在信用与费用之间获得数学权利。您还可以添加代码以进一步帮助区分信用和费用。

SELECT user_id, value, date_activated, 'c' as `type` FROM CREDITS
UNION ALL
SELECT user_id, value * - 1, date_activated,'e' as `type` FROM EXPENSE

3 个解决方案

#1

更多相关文章

随机推荐