$sql1 = "select distinct date, count(id) from windows where start_date between '2017-01-01' and '2017-10-31' and (start_date!='0000-00-00') group by date";
$sql2 = "select distinct date, count(id) from AIX where start_date between '2017-01-01' and '2017-10-31' and (start_date!='0000-00-00') group by date";
I have these two queries which return output in the form of (date,ID). What i want is to merge these two tables so that i get result in one array as (date,ID1,ID2) where ID1 is of 'windows' table and ID2 is of 'AIX' table and according to date they must print the ID's. I tried using "join" but that didn't work. I used array_merge() in php to combine the results to one json array but that didn't work either. Is there a way to solve this problem?
我有这两个查询，它们以(date,ID)的形式返回输出。我想要的是合并这两个表，以便得到一个数组(date,ID1,ID2)，其中ID1属于“windows”表，ID2属于“AIX”表，根据日期，它们必须打印ID。我尝试过使用“join”，但是没用。我在php中使用array_merge()将结果合并到一个json数组中，但这也不起作用。有办法解决这个问题吗?
1 个解决方案
#1
0
Because you want to combine aggregations from two different tables, I think you will have to aggregate separately first and then somehow combine the results. In the query below, I aggregate over your two tables in subqueries and then join them on the date. This should work assuming that both tables have all dates present and have the same dates.
因为您想要合并来自两个不同表的聚合，我认为您必须首先分别聚合，然后以某种方式合并结果。在下面的查询中，我将在子查询中聚合两个表，然后在日期上连接它们。假设这两个表都有所有的日期，并且有相同的日期，这应该是可行的。
SELECT
t1.date,
t2.cnt1,
t3.cnt2
FROM calendar t1
LEFT JOIN
(
SELECT date, COUNT(id) AS cnt1
FROM windows
WHERE start_date BETWEEN '2017-01-01' AND '2017-10-31'
GROUP BY date
) t2
ON t1.date = t2.date
LEFT JOIN
(
SELECT date, COUNT(id) AS cnt2
FROM AIX
WHERE start_date BETWEEN '2017-01-01' AND '2017-10-31'
GROUP BY date
) t3
ON t1.date = t3.date;
As I mentioned above, if either table be completely missing a date, then you will have to do more work. In this case, you could resort to joining with a calendar table to include the missing dates.
正如我前面提到的，如果任何一个表都完全错过了一个日期，那么您将不得不做更多的工作。在这种情况下，您可以使用一个日历表来包含丢失的日期。
Edit:
编辑:
I have updated the query slightly to use a calendar table. To make this query work, just define a table calendar with a date column containing the full range of dates to cover both of your tables.
我稍微更新了查询以使用一个日历表。要使此查询有效，只需定义一个表日历，其中包含涵盖两个表的完整日期范围的日期列。
#1
0
Because you want to combine aggregations from two different tables, I think you will have to aggregate separately first and then somehow combine the results. In the query below, I aggregate over your two tables in subqueries and then join them on the date. This should work assuming that both tables have all dates present and have the same dates.
因为您想要合并来自两个不同表的聚合，我认为您必须首先分别聚合，然后以某种方式合并结果。在下面的查询中，我将在子查询中聚合两个表，然后在日期上连接它们。假设这两个表都有所有的日期，并且有相同的日期，这应该是可行的。
SELECT
t1.date,
t2.cnt1,
t3.cnt2
FROM calendar t1
LEFT JOIN
(
SELECT date, COUNT(id) AS cnt1
FROM windows
WHERE start_date BETWEEN '2017-01-01' AND '2017-10-31'
GROUP BY date
) t2
ON t1.date = t2.date
LEFT JOIN
(
SELECT date, COUNT(id) AS cnt2
FROM AIX
WHERE start_date BETWEEN '2017-01-01' AND '2017-10-31'
GROUP BY date
) t3
ON t1.date = t3.date;
As I mentioned above, if either table be completely missing a date, then you will have to do more work. In this case, you could resort to joining with a calendar table to include the missing dates.
正如我前面提到的，如果任何一个表都完全错过了一个日期，那么您将不得不做更多的工作。在这种情况下，您可以使用一个日历表来包含丢失的日期。
Edit:
编辑:
I have updated the query slightly to use a calendar table. To make this query work, just define a table calendar with a date column containing the full range of dates to cover both of your tables.
我稍微更新了查询以使用一个日历表。要使此查询有效，只需定义一个表日历，其中包含涵盖两个表的完整日期范围的日期列。