我想知道是否有办法将两个或多个结果集连接成一个。我有以下两个问题第一个查询：
SELECT
CONCAT(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on)) as day_month_year,
db.country.country ,
count(concat(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on))) as count ,
COUNT(DISTINCT db.prod_id.email) AS MAIL
from db.prod_id
left join db.country on db.prod_id.branch_id = db.country.id
where db.prod_id.created_on > '2020-11-17' and (db.country.type = 1 or db.country.type = 2)
group by
concat(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on)),
db.country.country
order by db.prod_id.created_on
第二个查询：
select
CONCAT(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on)) as day_month_year,
db.country.country,
count(CONCAT(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on))) as count_BUY
from db.prod_id
left join db.prod_evaluations on db.prod_id.id = db.prod_evaluations.id
left join db.country on db.prod_id.branch_id = db.country.id
left join (Select prod_properties.prod_id, prod_properties.value From prod_properties Where prod_properties.property_id = 5) as db3 on db3.prod_id = db.prod_id.id
where db.prod_id.created_on > '2020-11-17'
and db3.value = 'online-buy' and db.prod_id.status_id <> 25
group by
concat(day(db.prod_id.created_on),"-",month(db.prod_id.created_on),"-",year(db.prod_id.created_on)),
db.country.country
order by db.prod_id.created_on
第一个查询给出以下结果：
+------------+---------+-------+------+|天|国家/地区|计数|邮件|+------------+---------+-------+------+|2020年11月17日|它|200|100||2020年11月17日|我们| 250 | 100||2020年11月18日|它|350|300||2020年11月18日|我们|200|100|+------------+---------+-------+------+
第二个查询给出：
+------------+---------+-----------+|天|国家|县_BUY|+------------+---------+-----------+|2020年11月17日|它|50||2020年11月17日|我们|70||2020年11月18日|它|200||2020年11月18日|我们|50|+------------+---------+-----------+
现在我想将这两个结果合并为一个：
+------------+---------+-------+------+-----------+|天|国家/地区|计数|邮件|计数_BUY|+------------+---------+-------+------+-----------+|2020年11月17日|它|200|100|50||2020年11月17日|我们| 250 | 100 | 70||2020年11月18日| IT | 350 | 300 | 200||2020年11月18日|我们| 200 | 100 | 50|+------------+---------+-------+------+-----------+
如何执行此查询？我正在使用mysql感谢
简单的方法是：您可以加入查询
select *
from ( <your first query here> ) first_query
join ( <your second query here> ) second_query using (day_month_year, country)
order by day_month_year, country;
这是一个内部连接。当然你也可以外接。MySQL不支持完全外部联接。如果你想要的话，你必须了解如何在MySQL中模拟一个完整的外部联接。
艰难的道路；-(合并查询
如果我没有错的话，你的两个查询可以简化为
select
date(created_on),
branch_id as country,
count(*) as count_products,
count(distinct p.email) as count_emails
from db.prod_id
where created_on >= date '2020-11-17'
and branch_id in (select country from db.country where type in (1, 2))
group by date(created_on), branch_id
order by date(created_on), branch_id;
和
select
date(created_on),
branch_id as country,
count(*) as count_buy
from db.prod_id
where created_on >= date '2020-11-17'
and status_id <> 25
and prod_id in (select prod_id from prod_properties where property_id = 5 and status_id <> 25)
group by date(created_on), branch_id
order by date(created_on), branch_id;
两者结合起来应该是
select
date(created_on),
branch_id as country,
sum(branch_id in (select country from db.country where type in (1, 2)) as count_products,
count(distinct case when branch_id in (select country from db.country where type in (1, 2) then p.email end) as count_emails,
sum(status_id <> 25 and prod_id in (select prod_id from prod_properties where property_id = 5 and status_id <> 25)) as count_buy
from db.prod_id
where created_on >= date '2020-11-17'
group by date(created_on), branch_id
order by date(created_on), branch_id;
您可以看到，查询的共同条件保留在where子句中，其他条件进入聚合函数中。
sum(boolean)是sum(case when boolean then 1 else 0 end)的缩写，即计算MySQL中满足条件的行。