php - 所有用户根据其发出的交易获得佣金

Question

I have two tables   Transaction Table :-         
1       txn_id                      
2       txn_issued_by  (mid)        
3       dist_id                 
4       amount                  
5       bank_name           
6       account_no          
7       ben_name            
8       ben_contact         
9       date_of_transaction         
10      date_of_txn_modify          
11      txn_status          
12      bank_response_code          
13      status      
14      modify_by           
15      comm_amount_agent           
16      comm_amount_distributor     
17      comm_amount_sadmin          
18      client_name         
19      client_number           
20      client_add          
21      txn_serial_no           
22      comment_by_moderator    

2)Member Table
   - mid name username photo 

我只是想知道如何处理交易以获得所需的结果，例如......

• 获取金额在 525 到 2500 之间的谎言交易次数
• 获取金额在 2501 到 5050 之间的谎言交易次数

• 获取金额在 5051 到 10100 之间的谎言交易次数

• 所有用户(User Wise)我的代码是:

<?php
$q_c = "SELECT *,
      COUNT(txn_id) AS count_txn
    FROM TRANSACTION
    WHERE amount >= 525
      AND amount <= 2500
      AND txn_issued_by IN (
        SELECT mid
        FROM members
        )
    GROUP BY txn_issued_by";

$r_c = mysql_query($q_c) or die(mysql_error());
$row_c     = mysql_fetch_array($r_c);
$count_txn = $row_c['count_txn'];
?>

目标如下:

Answer 1

据我了解，您需要仅使用一个查询来计算所有用户给定金额范围内的交易数量和金额总和。
在这种情况下，像下面这样的查询可能会有所帮助:

SELECT txn_issued_by,
  SUM( CASE WHEN amount BETWEEN 525 AND 2500 
       THEN 1 ELSE 0 END ) count_525_to_2500,
  SUM( CASE WHEN amount BETWEEN 2501 AND 5050 
       THEN 1 ELSE 0 END ) count_2501_to_5050,
  SUM( IF( amount BETWEEN 5051 AND 10100, 1, 0 )) count_5051_to_10100,
  COUNT(*) count_total,
  SUM( CASE WHEN amount BETWEEN 525 AND 2500 
       THEN amount ELSE 0 END ) amount_525_to_2500,
  SUM( CASE WHEN amount BETWEEN 2501 AND 5050 
       THEN amount  ELSE 0 END ) amount_2501_to_5050,
  SUM( CASE WHEN amount BETWEEN 5051 AND 10100 
       THEN amount  ELSE 0 END ) amount_5051_to_10100,
  SUM( amount ) amount_total
FROM Transaction
GROUP BY txn_issued_by;

查询使用CASE WHEN ... THEN ... END表达式，IF也可以使用函数(注意查询中的第三个SUM)，但我个人更喜欢CASE WHEN因为IF特定于 MySql，而 CASE WHEN符合 ANSII SQL 标准并在大多数数据库上运行。

以下查询计算所有记录的总值:

SELECT 
  SUM( CASE WHEN amount BETWEEN 525 AND 2500 
       THEN 1 ELSE 0 END ) count_525_to_2500,
  SUM( CASE WHEN amount BETWEEN 2501 AND 5050 
       THEN 1 ELSE 0 END ) count_2501_to_5050,
  SUM( IF( amount BETWEEN 5051 AND 10100, 1, 0 )) count_5051_to_10100,
  COUNT(*) count_total,
  SUM( CASE WHEN amount BETWEEN 525 AND 2500 
       THEN amount ELSE 0 END ) amount_525_to_2500,
  SUM( CASE WHEN amount BETWEEN 2501 AND 5050 
       THEN amount  ELSE 0 END ) amount_2501_to_5050,
  SUM( CASE WHEN amount BETWEEN 5051 AND 10100 
       THEN amount  ELSE 0 END ) amount_5051_to_10100,
  SUM( amount ) amount_total
FROM Transaction;

演示 --> http://www.sqlfiddle.com/#!2/c017b/11