数据从mysql中的三个表中获取

本文介绍了数据从mysql中的三个表中获取的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

你好我使用reportviewer来显示3个表中的值。

1.table_sale1 ----------- ------------------------------------- bill_date | bill_amount | paid_advance | balance_due | | | | | | | | | | | | | ---------------------------------------------- -

2.table_invoice ------------------ ----------- invoice_date | invoice_amount | | | | | | | -----------------------------

3.table_expenses ----------------------------- expense_date | expense_amount | | | | | | | -----------------------------

这些是三张桌子 i需要外出像

------------------- -------------------------------------------------- ------------ date | bill_amount | paid_advance | balance_due | invoice_amount | expense_amount | | | | | | | | | | | | | | | | | | | ---------------------------------------------- -----------------------------------

i使用查询，我得到了输出但是重复了一些值。 如何解决这些pblm ..？ 查询是 -------------

SELECT table_sale1 .bill_date，table_invoice.invoice_date，table_expenses.dates，table_sale1.total_amount，table_sale1.amount_paid，table_sale1.amount_due， table_invoice.total_amount AS Expr1，table_expenses.amount AS Expr2 FROM table_sale1，table_invoice，table_expenses GROUP BY table_sale1.bill_date，table_invoice.invoice_date，table_expenses.dates

请帮帮我..........

解决方案

首先，阅读我的评论。 直到您的数据库不是关系数据库 [ ^ ]，你可以''简单地避免重复。 如何创建realational数据库？ 创建快速MySQL关系数据库 [ ^ ] 之后你需要了解加入 [ ^ ]。要了解LEFT，RIGHT，INNER联接之间的差异，请阅读以下文章： SQL联接的可视化表示 [ ^ ] 祝你有愉快的一天;）

如果你想要日期摘要，那么，

SELECT 日期（table_sale1.bill_date） as Date_，sum（table_sale1.total_amount） as bill_amt，sum（table_sale1.amount_paid） as amt_paid，sum（table_sale1.amount_due） as amt_due， sum（table_invoice.total_amount） AS invoice_amt，sum（table_expenses.amount） AS expense_amt FROM table_sale1 left join table_invoice 日期（table_sale1.bill_date ）= 日期（table_invoice.invoice_date） left join table_expenses 日期（table_invoice.invoice_date）= 日期（table_expenses.dates） GROUP BY date （table_sale1.bill_date）， date （table_invoice.invoice_date），日期（table_expenses.dates）

快乐编码！ ：）

hello i am using reportviewer for showing the values from 3 tables.

1.table_sale1 ------------------------------------------------ bill_date |bill_amount|paid_advance|balance_due| | | | | | | | | | | | | ------------------------------------------------

2.table_invoice ----------------------------- invoice_date |invoice_amount| | | | | | | -----------------------------

3.table_expenses ----------------------------- expense_date |expense_amount| | | | | | | -----------------------------

these are three tables i need the out put like

--------------------------------------------------------------------------------- date |bill_amount|paid_advance|balance_due|invoice_amount|expense_amount | | | | | | | | | | | | | | | | | | | ---------------------------------------------------------------------------------

i use the query,i got the output but some values repeated. how to solve these pblm..? query is -------------

SELECT table_sale1.bill_date, table_invoice.invoice_date, table_expenses.dates, table_sale1.total_amount, table_sale1.amount_paid, table_sale1.amount_due, table_invoice.total_amount AS Expr1, table_expenses.amount AS Expr2 FROM table_sale1, table_invoice, table_expenses GROUP BY table_sale1.bill_date, table_invoice.invoice_date, table_expenses.dates

please help me..........

解决方案

First of all, read my comment. Untill your database is not relational database[^], you can''t simply avoid duplicates. How to create realational database? Creating A Quick MySQL Relational Database[^] After that you need to learn about JOIN''s[^]. To understand differences between LEFT, RIGHT, INNER joins, read this article: Visual Representation of SQL Joins[^] Have a nice day ;)

If you want datewise summary then,

SELECT Date(table_sale1.bill_date) as Date_, sum(table_sale1.total_amount) as bill_amt, sum(table_sale1.amount_paid) as amt_paid, sum(table_sale1.amount_due) as amt_due, sum(table_invoice.total_amount) AS invoice_amt, sum(table_expenses.amount) AS expense_amt FROM table_sale1 left join table_invoice on Date(table_sale1.bill_date) = Date(table_invoice.invoice_date) left join table_expenses on Date(table_invoice.invoice_date) = Date(table_expenses.dates) GROUP BY date(table_sale1.bill_date), date(table_invoice.invoice_date), Date(table_expenses.dates)

Happy Coding! :)