MYSQL组合视图的两个表有不同数量的条目记录 [英] MYSQL combine view of two table having different number of entry records
问题描述
工作我发现一些错误，而选择查询，coz我想有一个视图两个表..但有不同数量的条目记录。
示例如下：
表1（SALE）
+ ---- + ------------ + ----------------- + ------ - +
| ID |日期|说明|金额|
+ ---- + ------------ + ----------------- + ------- +
| 1 | 2014-09-01 |书店| 10000 |
| 2 | 2014-09-01 |一般商店| 5000 |
+ ---- + ------------ + ----------------- + ------- +
现在其查询是：
select * from sale;
表2（费用）
+ ---- + ------------ + ----------------- + - ----- +
| ID |日期|说明|金额|
+ ---- + ------------ + ----------------- + ------- +
| 1 | 2014-09-01 |酒店| 2000 |
+ ---- + ------------ + ----------------- + ------- +
，其查询为：
select * from Expense
现在运行的查询如下： / p>
选择Sale.Date，Sale.Description，Expense.Description，Sale.Amount，
Expense.Amount from sale，费用where Sale.Date ='2014-09-01'and
Expense.Date ='2014-09-01'
上述查询的结果如下：
+ --------- --- + ----------------- + -------------------- + ------- ----- + --------------- +
|日期|出售说明|费用说明| SaleAmount | ExpenseAmount |
+ ------------ + ----------------- + -------------- ------ + ------------ + --------------- +
| 2014-09-01 |书店|酒店| 10000 | 2000 |
| 2014-09-01 |一般商店|酒店| 5000 | 2000 |
+ ------------ + ----------------- + -------------- ------ + ------------ + --------------- +
现在的问题是，表1（销售）数据是OK，显示其所有条目..但表2（费用）也显示两个条目，只有一个条目记录！
选择Sale.Date为Date，Sale.Description as SaleDescription，
null as ExpenseDescription， Sale.Amount as SaleAmount，null as ExpenseAmount
from Sale where Sale.Date ='2014-09-01'
UNION
选择Expense.Date作为日期，将null作为SaleDescription，
Expense.Description as ExpenseDescription，null as SaleAmount，Expense.Amount as
ExpenseAmount
from Expense where Expense.Date ='2014-09-01';
这个查询告诉CodeNewbie结果如下：
+ ------------ + ----------------- + ------- ------------- + ------------ + --------------- +
|日期|出售说明|费用说明| SaleAmount | ExpenseAmount |
+ ------------ + ----------------- + -------------- ------ + ------------ + --------------- +
| 2014-09-01 |书店| NULL | 10000 | NULL |
| 2014-09-01 | NULL |酒店| NULL | 2000 |
+ ------------ + ----------------- + -------------- ------ + ------------ + --------------- +
任何人都可以通过这个帮助我吗？
提前感谢
您的意思是创建 SALE 和 EXPENSE ，但最终创建了一个加入。你提到 SALE 的数据是可以的，但这也不是真的。
请参阅本教程以了解关于联接的一点。您的查询大致如下：
选择Sale.Date，Sale.Description，Expense.Description，Sale.Amount，Expense。金额
从销售，费用
其中Sale.Date = Expense.Date和
Expense.Date ='2014-09-01';
这是一个连接的语法，就像上面链接中的例子。
您需要执行的操作是对两个表的行执行 UNION 操作。参见[本教程]在MySQL中的 UNION 操作。您需要的查询如下：
选择Sale.Date为Date，Sale.Description as SaleDescription，
null as ExpenseDescription，Sale.Amount as SaleAmount，null as ExpenseAmount
from Sale where Sale.Date ='2014-09-01'
UNION ALL
选择Expense.Date为Date，null为SaleDescription，
Expense.Description as ExpenseDescription，null as SaleAmount，Expense.Amount as ExpenseAmount
from Expense where Expense.Date ='2014-09-01';
I am working on WampServer/MySQL, I found some errors while select query, coz I want to have one view of two tables.. but having different number of entry records. Example is given in below:
Table 1 (SALE)
+----+------------+-----------------+-------+
|ID | Date | Description |Amount |
+----+------------+-----------------+-------+
|1 |2014-09-01 | Book Store | 10000 |
|2 |2014-09-01 | General Store | 5000 |
+----+------------+-----------------+-------+
now its query is:
select * from sale;
Table 2 (Expense)
+----+------------+-----------------+-------+
|ID | Date | Description |Amount |
+----+------------+-----------------+-------+
|1 |2014-09-01 | Hospitality | 2000 |
+----+------------+-----------------+-------+
and its query is:
select * from Expense
Now the Query I am running is below:
select Sale.Date,Sale.Description,Expense.Description,Sale.Amount,
Expense.Amount from sale,expense where Sale.Date ='2014-09-01' and
Expense.Date='2014-09-01'
the result of above query is below:
+------------+-----------------+--------------------+------------+---------------+
| Date | SaleDescription | ExpenseDescription | SaleAmount | ExpenseAmount |
+------------+-----------------+--------------------+------------+---------------+
|2014-09-01 | Book Store | Hospitality | 10000 | 2000 |
|2014-09-01 | General Store | Hospitality | 5000 | 2000 |
+------------+-----------------+--------------------+------------+---------------+
now the problem is that Table 1 (Sale) data is OK, showing all its entries.. but Table 2 (expense) is also showing two entries where as it has only one entry record!
select Sale.Date as Date,Sale.Description as SaleDescription,
null as ExpenseDescription, Sale.Amount as SaleAmount, null as ExpenseAmount
from Sale where Sale.Date ='2014-09-01'
UNION
select Expense.Date as Date, null as SaleDescription,
Expense.Description as ExpenseDescription, null as SaleAmount, Expense.Amount as
ExpenseAmount
from Expense where Expense.Date ='2014-09-01';
this query told by CodeNewbie results below:
+------------+-----------------+--------------------+------------+---------------+
| Date | SaleDescription | ExpenseDescription | SaleAmount | ExpenseAmount |
+------------+-----------------+--------------------+------------+---------------+
|2014-09-01 | Book Store | NULL | 10000 | NULL |
|2014-09-01 | NULL | Hospitality | NULL | 2000 |
+------------+-----------------+--------------------+------------+---------------+
Can anyone help me out through this..? thanks in advance
You meant to create a union of SALE and EXPENSE but ended up creating a join. You mentioned that the data from SALE is OK, but that isn't true either.
Refer to this tutorial to learn a bit about joins. Your query roughly reads like this:
select Sale.Date,Sale.Description,Expense.Description,Sale.Amount,Expense.Amount
from sale,expense
where Sale.Date = Expense.Date and
Expense.Date='2014-09-01';
This is the syntax for a join much like the one in the example in the above link.
What you need to perform is a UNION operation on the rows of both tables. See [this tutorial] on UNION operations in MySQL. The query you need is below:
select Sale.Date as Date,Sale.Description as SaleDescription,
null as ExpenseDescription, Sale.Amount as SaleAmount, null as ExpenseAmount
from Sale where Sale.Date ='2014-09-01'
UNION ALL
select Expense.Date as Date, null as SaleDescription,
Expense.Description as ExpenseDescription, null as SaleAmount, Expense.Amount as ExpenseAmount
from Expense where Expense.Date ='2014-09-01';
这篇关于MYSQL组合视图的两个表有不同数量的条目记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
