0
Good morning guys, I have the following query, for MYSQL 8:
With
Empresa as (Select aurea.oi, aurea.razao, aurea.fantasia From aurea Where aurea.id in ( 1302, 791, 2496, 1681, 1203 )),
CapaPedido as (Select cpd.oi, cpd.cp_tipo, cpd.cp_codi, cpd.cp_datc, cpd.cp_fatu, cpd.cp_clie, Empresa.razao, Empresa.fantasia
from cpd
inner join Empresa on Empresa.oi = cpd.oi
where cpd.cp_fatu = 'S'
and cpd.cp_nfop in ('N', 'F')
and datediff(date(now()), date(cpd.cp_datc)) <= 180),
TabelaPedido as (Select CapaPedido.oi, CapaPedido.razao, CapaPedido.fantasia, CapaPedido.cp_clie, sum(tbp.tp_quan) as Quantidade, sum(tbp.tp_quan * tbp.tp_valo) as valorTotal
from tbp FORCE INDEX(inner_cpd)
inner join CapaPedido on CapaPedido.oi = tbp.oi and CapaPedido.cp_codi = tbp.tp_codi and CapaPedido.cp_tipo = tbp.tp_tipo
group by CapaPedido.oi, CapaPedido.cp_clie
order by CapaPedido.oi ASC, valorTotal DESC)
select Pedido.*, Tabela.valorTotal
from (select TabelaPedido.oi from TabelaPedido inner join Empresa on Empresa.oi = TabelaPedido.oi group by TabelaPedido.oi) as Pedido
left join (select TabelaPedido.oi, TabelaPedido.cp_clie, valorTotal from TabelaPedido group by TabelaPedido.oi, TabelaPedido.cp_clie limit 10) as Tabela on Tabela.oi = Pedido.oi
Who returns to me:
Where this Nul should appear a sequence of 10 results for each 'HI' that in the case is the store, I can only make appear for the first.
I need to group together 10 customers who bought the most per store, someone already had to do it?
I need to solve in MYSQL, which brings the result ready.
Thank you
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/
– Riggns