如何将语法结果转换为变量mysql

Question

我有一个表，其中order_buyer_id是交易id，createdby是买家id，createdAt是交易发生日期，quantity是每笔交易的权重。

在我的桌子上，我把买家分为三类：

- new buyer
- unique buyer
- existing buyer

这是找出新买家的语法，我称之为A(新买家(：

select 
count(distinct om.createdby) as count_buyer
from (select count(xx.count_) as count_
from (select count(createdby) as count_ from order_match
where order_status_Id in (4, 5, 6, 8)
group by createdby
having count(createdby) = 1) xx
) x1,
(select createdby
from order_match
group by createdby
having count(createdby) = 1) yy,
order_match om
where yy.createdby = om.createdby and
order_status_id in (4, 5, 6, 8)
and om.createdAt >= paramdatefrom
and om.createdAt <= paramdateto
and NOT EXISTS (select 1 from order_match om2
where om.createdby = om2.createdby
and order_status_id in (4, 5, 6, 8)
and om2.createdAt < paramdatefrom);

这是找出重复买家的语法，称为B(唯一买家(：

select
count(distinct om.createdby) as count
from (select count(xx.count_) as count_
from (select count(createdby) as count_ from order_match
where order_status_Id in (4, 5, 6, 8)
group by createdby
) xx
) x1,
(select createdby
from order_match
group by createdby
) yy,
order_match om
where yy.createdby = om.createdby and
order_status_id in (4, 5, 6, 8)
and om.createdAt >= paramdatefrom
and om.createdAt <= paramdateto;

这是找出现有买家的语法，称为C(现有买家(：

select
count(distinct om.createdby) as count
from
order_match om
where om.order_status_id in (4,5,6,8)
and om.createdAt <= paramdateto
and om.createdAt >= paramdatefrom
and EXISTS (select 1 from order_match om2
where om.createdby = om2.createdby
and om2.createdAt < paramdatefrom and
om2.order_status_id in (4, 5, 6, 8)) 
;

基本上，我希望所有这些语法都变成变量A、B、C，这样我就可以根据我的解释计算出我的需求的百分比，预期的结果就像这个一样

select (A (the result of syntax new Buyer) : B (the result of syntax unique buyer)) * 100 as percentage_1

和select (100 - percentage_1) as percentage_2

重点是如何使语法的每个结果都变为可变的，这样我就可以像预期的结果一样计算percentage_1和percentage_2

Answer 1

要测试更大的查询，必须提供一些数据，要正确测试查询，请参阅

我在你的描述中找不到为什么你需要result_c，但你现在可以使用它了。

顺便说一下，这是算法或查询，而不是语法。。

SELECT 
result_a / result_b * 100 AS percentage_1,
100 - (result_a / result_b * 100) AS percentage_2
FROM
(SELECT 
(SELECT 
COUNT(DISTINCT om.createdby) AS count_buyer
FROM
(SELECT 
COUNT(xx.count_) AS count_
FROM
(SELECT 
COUNT(createdby) AS count_
FROM
order_match
WHERE
order_status_Id IN (4 , 5, 6, 8)
GROUP BY createdby
HAVING COUNT(createdby) = 1) xx) x1, (SELECT 
createdby
FROM
order_match
GROUP BY createdby
HAVING COUNT(createdby) = 1) yy, order_match om
WHERE
yy.createdby = om.createdby
AND order_status_id IN (4 , 5, 6, 8)
AND om.createdAt >= paramdatefrom
AND om.createdAt <= paramdateto
AND NOT EXISTS( SELECT 
1
FROM
order_match om2
WHERE
om.createdby = om2.createdby
AND order_status_id IN (4 , 5, 6, 8)
AND om2.createdAt < paramdatefrom)) result_a,
(SELECT 
COUNT(DISTINCT om.createdby) AS count
FROM
(SELECT 
COUNT(xx.count_) AS count_
FROM
(SELECT 
COUNT(createdby) AS count_
FROM
order_match
WHERE
order_status_Id IN (4 , 5, 6, 8)
GROUP BY createdby) xx) x1, (SELECT 
createdby
FROM
order_match
GROUP BY createdby) yy, order_match om
WHERE
yy.createdby = om.createdby
AND order_status_id IN (4 , 5, 6, 8)
AND om.createdAt >= paramdatefrom
AND om.createdAt <= paramdateto) result_b,
(SELECT 
COUNT(DISTINCT om.createdby) AS count
FROM
order_match om
WHERE
om.order_status_id IN (4 , 5, 6, 8)
AND om.createdAt <= paramdateto
AND om.createdAt >= paramdatefrom
AND EXISTS( SELECT 
1
FROM
order_match om2
WHERE
om.createdby = om2.createdby
AND om2.createdAt < paramdatefrom
AND om2.order_status_id IN (4 , 5, 6, 8))) result_c
) a