php - 如何从三个不同的表php中查询总计的SELECT SUM

Question

希望你们好人!我在 SELECT 三个不同的表和 SUM 那里有一个大问题 Total 这样我就可以得到这三个表的总计。表格如下，我只是提到了一些字段:

1:付款

id idnumber school_fee  trans_fee
1  va03     10000       20000

2:payment_one

id idnumber school_fee  trans_fee
1  va01     10000       30000

3:payment_two

id idnumber school_fee  trans_fee
1  va02     40000       50000

我已经从每个表中得到“总计”，我想要的是对我得到的那些总计求和，从这三个表中得到总计。

这是我的 php 代码；

1:付款方式:

  <?php

     //include mysql connect

   if (isset($_GET['query'])) 
{    
      $query=$_GET['query'];

      // Instructions if $_POST['value'] exist    
      }  

   // gets value sent over search form

     $min_length = 3;
    // you can set minimum length of the query if you want

    if(strlen($query) >= $min_length){ // if query length is more or equal minimum  
           length then

       $query = htmlspecialchars($query); 
        // changes characters used in html to their equivalents, for example: < to &gt;

    $query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

       $raw_results = mysql_query("SELECT      
    *,SUM(school_fee+trans_fee) 
                    As Total  FROM payment
            WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());

  $raw_results2 = mysql_query("SELECT * FROM payment
        WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());





    // * means that it selects all fields, you can also write: `id`, `title`, `text`
    // articles is the name of our table

       // '%$query%' is what we're looking for, % means anything, for example if $query  
         is  Hello
    // it will match "hello", "Hello man", "gogohello", if you want exact match use  
      `title`='$query'
       // or if you want to match just full word so "gogohello" is out use '% $query %' 
          ...OR ... '$query %' ... OR ... '% $query'


    if(mysql_num_rows($raw_results) > 0){
    if(mysql_num_rows($raw_results2) > 0){
     // if one or more rows are returned do following

        while($results = mysql_fetch_array($raw_results)){
         while($results2 = mysql_fetch_array($raw_results2)){

        // $results = mysql_fetch_array($raw_results) puts data from database into   
               array, while it's valid it does the loop


            // posts results gotten from database(title and text) you can also show id  
                    ($results['id'])
        }{



              echo " &nbsp;Total amount of money payed by&nbsp;" .$results['class']  
                  ."&nbsp;"."class is&nbsp;" . $results ['Total'] . "&nbsp;/=Tshs";



              echo"<br>";   echo"<br>"; 
                               }


                  }
            }
            }
       }

                      ?>

2:payment_one

 <?php

     //include mysql connect

   if (isset($_GET['query'])) 
{    
      $query=$_GET['query'];

      // Instructions if $_POST['value'] exist    
      }  

   // gets value sent over search form

     $min_length = 3;
    // you can set minimum length of the query if you want

    if(strlen($query) >= $min_length){ // if query length is more or equal minimum  
           length then

       $query = htmlspecialchars($query); 
        // changes characters used in html to their equivalents, for example: < to &gt;

    $query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

       $raw_results = mysql_query("SELECT      
    *,SUM(school_fee+trans_fee) 
                    As Total  FROM payment_one
            WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());

  $raw_results2 = mysql_query("SELECT * FROM payment_one
        WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());





    // * means that it selects all fields, you can also write: `id`, `title`, `text`
    // articles is the name of our table

       // '%$query%' is what we're looking for, % means anything, for example if $query  
         is  Hello
    // it will match "hello", "Hello man", "gogohello", if you want exact match use  
      `title`='$query'
       // or if you want to match just full word so "gogohello" is out use '% $query %' 
          ...OR ... '$query %' ... OR ... '% $query'


    if(mysql_num_rows($raw_results) > 0){
    if(mysql_num_rows($raw_results2) > 0){
     // if one or more rows are returned do following

        while($results = mysql_fetch_array($raw_results)){
         while($results2 = mysql_fetch_array($raw_results2)){

        // $results = mysql_fetch_array($raw_results) puts data from database into   
               array, while it's valid it does the loop


            // posts results gotten from database(title and text) you can also show id  
                    ($results['id'])
        }{



              echo " &nbsp;Total amount of money payed by&nbsp;" .$results['class']  
                  ."&nbsp;"."class is&nbsp;" . $results ['Total'] . "&nbsp;/=Tshs";



              echo"<br>";   echo"<br>"; 
                               }


                  }
            }
            }
       }

                      ?>

3:payment_two

 <?php

     //include mysql connect

   if (isset($_GET['query'])) 
{    
      $query=$_GET['query'];

      // Instructions if $_POST['value'] exist    
      }  

   // gets value sent over search form

     $min_length = 3;
    // you can set minimum length of the query if you want

    if(strlen($query) >= $min_length){ // if query length is more or equal minimum  
           length then

       $query = htmlspecialchars($query); 
        // changes characters used in html to their equivalents, for example: < to &gt;

    $query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

       $raw_results = mysql_query("SELECT      
    *,SUM(school_fee+trans_fee) 
                    As Total  FROM payment_two
            WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());

  $raw_results2 = mysql_query("SELECT * FROM payment_two
        WHERE (`class` LIKE '%".$query."%')") or die(mysql_error());





    // * means that it selects all fields, you can also write: `id`, `title`, `text`
    // articles is the name of our table

       // '%$query%' is what we're looking for, % means anything, for example if $query  
         is  Hello
    // it will match "hello", "Hello man", "gogohello", if you want exact match use  
      `title`='$query'
       // or if you want to match just full word so "gogohello" is out use '% $query %' 
          ...OR ... '$query %' ... OR ... '% $query'


    if(mysql_num_rows($raw_results) > 0){
    if(mysql_num_rows($raw_results2) > 0){
     // if one or more rows are returned do following

        while($results = mysql_fetch_array($raw_results)){
         while($results2 = mysql_fetch_array($raw_results2)){

        // $results = mysql_fetch_array($raw_results) puts data from database into   
               array, while it's valid it does the loop


            // posts results gotten from database(title and text) you can also show id  
                    ($results['id'])
        }{



              echo " &nbsp;Total amount of money payed by&nbsp;" .$results['class']  
                  ."&nbsp;"."class is&nbsp;" . $results ['Total'] . "&nbsp;/=Tshs";



              echo"<br>";   echo"<br>"; 
                               }


                  }
            }
            }
       }

                      ?>

如有任何帮助，我将不胜感激。

Answer 1

如果我没理解错的话，你所有的三个表 [payment][payment_one][payment_two] 都有相同的列:id、idnumber、school_fee trans_fee。

您可以使用一个单独的表来代替，并通过引入一个新的列来区分它们:tablenum，然后很容易获得您想要的。 请注意[id]和[tablenum]现在都是主键(复合主键)。

新表架构和数据将是(我不太确定您的 idnumber 列的用途):

[payment] 
id tablenum idnumber school_fee  trans_fee 
1  0        va03     10000       20000
1  1        va01     10000       30000
1  2        va02     40000       50000

SQL 示例:

SELECT 
school_fee,
trans_fee,
(school_fee + trans_fee) as 'total'
FROM payment WHERE id=1