数据从mysql中的三个表中获取 [英] data fetching from three table in 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关系数据库 [ ^ ]



之后你需要了解< a href =http://dev.mysql.com/doc/refman/5.0/en/join.html>加入 [ ^ ]。要了解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!
:)

这篇关于数据从mysql中的三个表中获取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！