合并两个查询在一个
问题描述:
我应该做这些DATAS报告:合并两个查询在一个
|--------|---------------|------------|----------|--------------|
| BIL_Id | BIL_BookingId | BIL_Date | BIL_Rate | BIL_Quantity |
|--------|---------------|------------|----------|--------------|
| 1 | 90 | 2017-04-01 | 85.00 | 1 |
| 2 | 90 | 2017-04-02 | 85.00 | 1 |
| 3 | 90 | 2017-04-03 | 85.00 | 1 |
| 4 | 92 | 2017-04-07 | 95.00 | 1 |
| 5 | 93 | 2017-04-10 | 85.00 | 1 |
| 6 | 93 | 2017-04-11 | 85.00 | 1 |
| 7 | 93 | 2017-04-12 | 85.00 | 1 |
| 8 | 94 | 2017-04-07 | 95.00 | 1 |
| 9 | 95 | 2017-04-12 | 95.00 | 1 |
| 10 | 95 | 2017-04-13 | 95.00 | 1 |
|--------|---------------|------------|----------|--------------|
我需要:
- 每天晚上预订数。 例如,从数据表中,对于
2017-04-12
和1
其他日期,我应该有2
。
该工作查询:
SELECT BIL_Date,
COUNT(*) AS total_nights
FROM ___BillableDatas
WHERE BIL_Date BETWEEN "2017-04-01" AND "2017-04-30"
GROUP BY BIL_Date ASC
- 获取收入同期每个日期。
该工作查询:
SELECT BIL_Date,
SUM(BIL_Rate * BIL_Quantity) AS sumRevAccomodation
FROM `___BillableDatas`
WHERE BIL_Date BETWEEN "2017-04-01" AND "2017-04-30"
GROUP BY BIL_Date ASC
我的问题:如何 我可以在一个这两个查询结合?
答
你只需要选择两个条款】
SELECT COUNT(*) AS total_nights, BIL_Date,SUM(BIL_Rate * BIL_Quantity) AS sumRevAccomodation
FROM ___BillableDatas
WHERE BIL_Date BETWEEN "2017-04-01" AND "2017-04-30" GROUP BY BIL_Date ASC
混合
答
SELECT COUNT(*) AS total_nights, SUM(BIL_Rate * BIL_Quantity) AS sumRevAccomodation, BIL_Date FROM ___BillableDatas WHERE BIL_Date BETWEEN "2017-04-01" AND "2017-04-30" GROUP BY BIL_Date ASC