从两个表中选择数据，mysql。

My database is having two tables customer and transaction.

我的数据库有两个表客户和事务。

customer table is having 8 columns,transaction table is having 4 columns,

customer表有8个列，事务表有4个列，

customer table primary key is id,and in transaction table there is no primary key,per customer in transaction table there exists more than one record.

客户表主键是id，在事务表中没有主键，每个客户在事务表中存在多个记录。

I need to select 4 columns from customer ,3 columns from transaction.

我需要从客户中选择4列，从事务中选择3列。

here is query:

这是查询:

query ='SELECT c.customer_id AS "Customer ID",
               c.father_name AS "Father Name",
               c.address AS Address,
               c.phone_number AS "Phone Number",
               c.mobile_number AS "Mobile Number",
               c.id_proof AS "ID Proof",
               c.area AS "Area",
               c.ip_address AS "IP Address",
               c.mac_address AS "MAC Address",
               c.package_type AS "Package Type",
               c.name AS Name,
               c.activation_date AS "Activation Date",
               c.status AS "Status",
               c.installation_cost AS "Installation Cost",
               c.totalamount_paid AS "Total Amount Paid",
               c.monthly_amount AS "Monthly Amount",
               c.lastpaid_date AS "Last Paid Date",
               c.lastpaid_amount AS "Last Paid Amount",
               c.nextpay_date AS "Next Pay Date",
               c.totaldue_amount AS "Total Due Amount",
               t.agent_id AS "Agent ID",
               t.token_number AS "Token Number",
               t.machine_id AS "Machine ID"
          FROM customer c INNER JOIN transaction t ON c.customer_id = t.customer_id
        WHERE DATE(t.paid_date)BETWEEN '" + fromDate + "'AND '" + toDate + "' ";

But here the problem is:

但问题是:

How many times the record exists in transaction table,those times its giving records , But i need only once.

记录在事务表中存在多少次，那些次数是它的提供记录，但我只需要一次。

How can i do this ?

我该怎么做呢?

4 个解决方案

#1

... GROUP BY c.customer_id

You'd most likely want to SUM some of the columns or use other aggregate functions.

您很可能想要对某些列进行求和，或者使用其他聚合函数。

#2

The problem is with your query logic. There are three columns that you are selecting from transaction table. Now, for one single customer, there can be multiple transactions. If you are selecting t.agent_id, t.token_number and t.machine_id, this will give you these values for every transaction made by the customer.

问题在于您的查询逻辑。您从事务表中选择了三列。现在，对于单个客户，可以有多个事务。如果你选择t。agent_id,t。token_number和t。machine_id，这将为客户提供的每笔交易提供这些值。

If your business logic permits this, remove these three columns from the select list and use an EXISTS clause instead of an INNER JOIN like this:

如果您的业务逻辑允许这样做，那么从select列表中删除这三列，并使用EXISTS子句，而不是这样的内部连接:

query ='SELECT c.customer_id AS "Customer ID",
               c.father_name AS "Father Name",
               c.address AS Address,
               c.phone_number AS "Phone Number",
               c.mobile_number AS "Mobile Number",
               c.id_proof AS "ID Proof",
               c.area AS "Area",
               c.ip_address AS "IP Address",
               c.mac_address AS "MAC Address",
               c.package_type AS "Package Type",
               c.name AS Name,
               c.activation_date AS "Activation Date",
               c.status AS "Status",
               c.installation_cost AS "Installation Cost",
               c.totalamount_paid AS "Total Amount Paid",
               c.monthly_amount AS "Monthly Amount",
               c.lastpaid_date AS "Last Paid Date",
               c.lastpaid_amount AS "Last Paid Amount",
               c.nextpay_date AS "Next Pay Date",
               c.totaldue_amount AS "Total Due Amount" /*,
               t.agent_id AS "Agent ID",
               t.token_number AS "Token Number",
               t.machine_id AS "Machine ID" -- */
          FROM customer c 
        WHERE EXISTS (SELECT 1
                        FROM transaction t
                       WHERE c.customer_id = t.customer_id
                         AND DATE(t.paid_date)BETWEEN '"
                                                        + fromDate
                                                        + "'AND '"
                                                        + toDate + "' "

Now, if your business does need those three values, you can simply use a SELECT DISTINCT and stop worrying about duplicate values.

现在，如果您的业务确实需要这三个值，您可以简单地使用SELECT DISTINCT并停止担心重复的值。

#3

Try this-

试试这个,

query = " ... ... ... GROUP BY c.customer_id";

#4

query ='SELECT c.customer_id AS "Customer ID", c.father_name AS "Father Name", c.address AS Address, c.phone_number AS "Phone Number", c.mobile_number AS "Mobile Number", c.id_proof AS "ID Proof", c.area AS "Area", c.ip_address AS "IP Address", c.mac_address AS "MAC Address", c.package_type AS "Package Type", c.name AS Name, c.activation_date AS "Activation Date", c.status AS "Status", c.installation_cost AS "Installation Cost", c.totalamount_paid AS "Total Amount Paid", c.monthly_amount AS "Monthly Amount", c.lastpaid_date AS "Last Paid Date", c.lastpaid_amount AS "Last Paid Amount", c.nextpay_date AS "Next Pay Date", c.totaldue_amount AS "Total Due Amount", t.agent_id AS "Agent ID", t.token_number AS "Token Number", t.machine_id AS "Machine ID" FROM customer c INNER JOIN transaction t ON c.customer_id = t.customer_id WHERE DATE(t.paid_date)BETWEEN '" + fromDate + "'AND '" + toDate + "' " GROUP BY c.customer_id;

查询= '选择c。customer_id为“客户ID”，c。父亲的名字是“父亲的名字”，c。地址地址,c。电话号码为“电话号码”，c。mobile_number为“移动电话号码”，c。id_proof为“ID Proof”，c。区域为“区”,c。ip_address为“IP地址”，c。mac_address为“MAC地址”，c。package_type为“Package Type”，c. Name为名称，c。激活日期为“激活日期”，c。作为“地位”,c。installation_cost为“安装成本”，c。totalamount_pay为“支付总额”，c。每月金额为“每月金额”，c。lastpaid_date为“最后支付日期”，c。最后支付金额为“最后支付金额”，c。nextpay_date为“下次付款日期”，c。totaldue_amount为“Total Due Amount”，t。agent_id为“Agent ID”，t。token_number为“Token Number”，t。机器ID为“机器ID”，从客户c内部连接事务t到c。customer_id = t。customer_id在“+ fromDate +”和“+ toDate +”之间的日期(t.paid_date)，由c.customer_id组成;

#1

... GROUP BY c.customer_id