mysql 自定义结果集,在MySQL中创建自定义结果集

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.

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