在MySQL中创建自定义结果集 [英] Creating a custom result set in MySQL

问题描述

我正在建立一个计算客户每月帐单金额的应用程式。

我从3个不同的查询中取得MySQL的所有资料，填入1个表格。 / p>

我想知道是否有一种方法来加入这些查询，并创建一个带有我选择的默认表的结果集。

例如，我单独使用这些quires，并将数据转到同一个表。

 code> //这将得到我的客户的每小时的价格。 
 
 select * from RoomManager.CompanyFinance where ProjectName ='xxx'; 
 
输出将是：
 ShiftType |价格
 ---------- | ------ 
 OL | 555 
 OFF | 548 
 BKG | 666 
 SND | 422 
 
 //这将得到每个产品的客户到本月的产品数量。 
 
 SELECT ShiftType，COUNT（*）FROM RoomManager.dailyrooms 
 WHERE Project ='xxx'
 AND Company ='yyy'
 AND DayDate LIKE \ yyyy-dd％
 GROUP BY ShiftType;; 
 
输出将类似于：
 ShiftType | COUNT 
 --------- -  | ------ 
 OL | 2 
 OFF | 1 
 BKG | 0 
 SND | 3 
 
 //这将让我取消金额
 
 SELECT WasCancelled，COUNT（*）FROM RoomManager.dailyrooms 
 WHERE WasCancelled = 2 
 AND Project ='xxx'
 GROUP BY WasCancelled; 
 
输出结果如下：
 WasCancelled | COUNT 
 ------------- | ------ 
 2 | 6 
  

有办法组合查询并获得单个结果集， / p>

  ShiftType | Price | COUNT |已取消
 ---------- | ---- ----------- | -------------- 
 OL | 555 | 2 | 6 
 OFF | 548 | 1 | 
 BKG | 666 | 0 | 
 SND | 422 | 3 | 
  

解决方案

我的坏， UNION 基本concat结果集。您想自定义结果集（类似这样）：

SELECT Orders.OrderID，Customers.CustomerName，Orders.OrderDate FROM Orders INNER JOIN客户有关Orders.CustomerID = Customers.CustomerID;

JOIN 两个表由一个公共字段，例如您的ProjectName和Project字段，允许SQL引擎将两个不同的结果合并为公共字段标识的一个结果。

请参阅： http://www.w3schools.com/sql/sql_join.asp

注意：我写的是这样的东西，因为它是一个例子，而不是解决方案。它的目的是显示一个可能性，组合两个或更多的表的字段 - 它仍然取决于你的工作，并让它运行为您的具体情况。

---

旧（这种情况下不正确）答案：

一种可能的方式可能是使用MySQL的 UNION 关键字：

SELECT * FROM mySchema.myTable UNION SELECT * FROM mySchema.myOtherTable

请参阅： http：/ /www.mysqltutorial.org/sql-union-mysql.aspx

I'm building an app that calculates the monthly bill amount for clients.

I am getting all data from MySQL in 3 different queries to populate 1 table.

I wanted to know if there is a way to join these queries, and create a result set with a default table of my choice.

For example, I use these quires separately, and the data goes to the same table.

// this will get me the prices per hour for the client.

select * from RoomManager.CompanyFinance where ProjectName = 'xxx';

the output will be something like:
ShiftType | Price
----------|------
  OL      |  555
  OFF     |  548
  BKG     |  666
  SND     |  422

//this will get me the amount of products per product for the client to this month.

SELECT ShiftType, COUNT(*) FROM RoomManager.dailyrooms
WHERE Project = 'xxx' 
AND Company = 'yyy'
AND DayDate LIKE \"%yyyy-dd%"                               
GROUP BY ShiftType;";

the output will be something like:
ShiftType | COUNT
----------|------
  OL      |  2
  OFF     |  1
  BKG     |  0
  SND     |  3

//this will get me the amount of cancellations

SELECT WasCancelled, COUNT(*) FROM RoomManager.dailyrooms
WHERE WasCancelled = 2 
AND Project = 'xxx'
GROUP BY WasCancelled;

the output will be something like:
WasCancelled | COUNT
-------------|------
  2          |  6

Is there a way to combine queries and get a single result set, something like this:

ShiftType | Price | COUNT | Was Cancelled
----------|---------------|--------------
  OL      |  555  |   2   |       6
  OFF     |  548  |   1   |
  BKG     |  666  |   0   |
  SND     |  422  |   3   |

解决方案

My bad, UNION basically concats resultsets. You wanted to customize the result set (something like that):

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

JOIN combines the two tables by a common field, such as your ProjectName and Project field, allowing the SQL engine to combine the two different results into one result identified by the common field.

See: http://www.w3schools.com/sql/sql_join.asp

NOTE: I'm writing 'something like that' because it is an example, not a solution. It's aimed to show you a possibility to combine fields of two or more tables - it's still up to you to do the work and get it running for your specific case.

---

Old (incorrect for this case) answer:

A possible way might be to use MySQL's UNION keyword:

SELECT * FROM mySchema.myTable UNION SELECT * FROM mySchema.myOtherTable

See: http://www.mysqltutorial.org/sql-union-mysql.aspx

这篇关于在MySQL中创建自定义结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！