我一个表里有很多数据 订单表<订单金额,订单类型,订单状态> 我需要定时将订单根据订单类型和订单状态 来求订单金额的和。 然后我需要将这条求和数据<主要包含 订单状态 订单金额 订单类型 订单总数> 插入另外一个表中, 我需要定时添加 所有我需要先去订单表中 查询出所有的订单类型和订单状态 然后根据订单类型和订单状态的不同 获得缓存的数据。 然后插入另外一个表中, 但是我订单类型和订单状态都不是单条 我应该怎么解决呢
insert into
order_cache(time,amount,count,type,status,channel)
select now(),COALESCE(SUM(o.amounts),0),count(o.status),o.status,o.service_type,p.channel
from order_info o LEFT JOIN pay_info p on o._id = p.order_id
WHERE o.status=xx and o.service_type=xx and p.channel='xx'这是我插入一条的sql,但是type status,以及channel 都有多个值 而且需要select o.status,count(*) from order_info o group by o.status进行查询出来
请各位帮忙下
insert into
order_cache(time,amount,count,type,status,channel)
select now(),COALESCE(SUM(o.amounts),0),count(o.status),o.status,o.service_type,p.channel
from order_info o LEFT JOIN pay_info p on o._id = p.order_id
WHERE o.status=xx and o.service_type=xx and p.channel='xx'这是我插入一条的sql,但是type status,以及channel 都有多个值 而且需要select o.status,count(*) from order_info o group by o.status进行查询出来
请各位帮忙下
INSERT INTO
ORDER_CACHE(time,amount,count,type,status,channel)
SELECT NOW(),SUM(AMOUNT) AS TOTAL_AMOUNT,COUNT(1) AS TOTAL_QTY,TYPE,STATUS,CHANNEL
FROM TABLE A
JOIN
(SELECT STATUS,TYPE,CHANNEL
FROM
(SELECT DISTINCT STATUS FROM TABLE) AS A
JOIN
(SELECT DISTINCT TYPE FROM TABLE) AS B ON 1=1
JOIN
(SELECT DISTINCT CHANNEL FROM TABLE) AS C ON 1=1) AS B
ON A.STATUS=B.STATUS AND A.TYPE=B.TYPE AND A.CHANNEL=B.CHANNEL
GROUP BY STATUS,TYPE,CHANNEL