GitLab

点赞 TOP

select
	c, 
	address_from, 
	b.name 
from (
	select 
		count(1) as c, 
		address_from 
	from tb_pol_record 
	group by address_from
) a 
join tb_user b on a.address_from = b.address 
order by c desc;

获赞 TOP

select 
	address_to, 
	Counts, 
	b.name
from (
	select 
		count(1) as Counts, 
		address_to 
	from tb_pol_record 
	group by address_to
) a 
join tb_user b on a.address_to = b.address 
order by Counts desc;

登录 TOP

select 
	a.address, 
	Counts, 
	b.name 
from (
	select 
		count(1) as Counts, 
		address 
	from tb_poc_record 
	group by address
) a 
join tb_user b on a.address = b.address 
order by Counts desc;

所有点赞记录

select 
	reason, 
	tags, 
	b.name as 点赞人, 
	c.name as 被赞人 
from tb_pol_record a 
join tb_user b on a.address_from = b.address 
join tb_user c on a.address_to = c.address;

所有点赞记录（进阶版）

select 
	a.updated_at as 时间,
	b.name as 点赞人, 
	b.department_name as 点赞人部门, 
	c.name as 被赞人, 
	c.department_name as 被赞人部门,
	reason::json->'action' as 点赞理由_行为, 
	reason::json->'result' as 点赞理由_结果, 
	tags
from tb_pol_record a 
join tb_user b on a.address_from = b.address 
join tb_user c on a.address_to = c.address
order by a.updated_at desc

查询礼物兑换方式

select 
	b.name as 兑换人, 
	a.礼物, 
	a.created_at as 兑换时间 
from (
	select 
		address_from, 
		memo::jsonb ->> 'gift_name' as 礼物, 
		created_at 
	from tb_transaction 
	where memo like '{%'
) a 
join tb_user b on a.address_from = b.address;

查询百姓币

select 
	name, 
	balance 
from tb_user 
order by balance desc;