如何在一次查询中选择两个带有多列的查询？

Question

我遇到了一个问题，我想在一个查询中选择两个不同的查询，但是它们需要放在不同的列中。 注意：不像union，因为union会将这两个查询放在一列中。

select count(id) as lead,SUM(ol.publisher_earned) as earning from offer_process as ol 
where ol.status='Approved'  and ol.publisher_id='1738' and ol.created_at>='2021-08-01' GROUP by date(ol.created_at)

select count(ol2.id) as clicks from offer_process as ol2  where   ol2.publisher_id='1738'  and ol2.created_at>='2021-08-01' GROUP by date(ol2.created_at)

- Habib Ur Rehman

---

对于联合数据类型，数据类型必须相同。IS SUM(ol.publisher_earned)和count(ol2.id)的数据类型是相同还是不同？如果不同，则UNION将无法工作。 - Rahul Biswas

是的，但我不想要联合，我需要两列。 - Habib Ur Rehman

Answer 1

请检查此处。如果在SELECT子句中不需要created_date，则将其丢弃。

SELECT date(created_at) created_at
     , COUNT(publisher_id) clicks
     , SUM(CASE WHEN status='Approved' THEN publisher_earned ELSE 0 END) earning
FROM offer_process
WHERE publisher_id='1738'
    AND created_at>='2021-08-01'
GROUP BY date(created_at)

- Rahul Biswas

---

哇，你让我的一天变得美好了，请解释一下 count(1)。 - Habib Ur Rehman

COUNT(1)或COUNT(id)是相同的。你可以使用任何一个。 - Rahul Biswas

欢迎 @HabibUrRehman 接受我的答案。如果可能的话，请给我的答案点赞并接受。在接受的答案中投反对票看起来很奇怪。 - Rahul Biswas

我不能投票，因为我的声望只有13。 - Habib Ur Rehman

Answer 2

SELECT earning,clicks FROM
(
    select ol.publisher_id,SUM(ol.publisher_earned) as earning from offer_process as ol 
    where ol.status='Approved'  and ol.publisher_id='1738' and ol.created_at>='2021-08-01' GROUP by date(ol.created_at),ol.publisher_id
)ol
JOIN
(
    select ol2.publisher_id, count(ol2.id) as clicks from offer_process as ol2  
    where ol2.publisher_id='1738'  and ol2.created_at>='2021-08-01' GROUP by date(ol2.created_at),ol2.publisher_id
)ol2 On ol.publisher_id=ol2.publisher_id

- Atique Rahman

---

第一个查询返回27行，第二个查询返回23行，但是你的查询返回了621行。 - Habib Ur Rehman