php - About product sorting and pagination using multiple criteria

Question

Now there are two tables, one product table, with the following fields:
CREATE TABLE ft_products (
p_id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'product id',
p_name varchar(50) NOT NULL DEFAULT '' COMMENT 'Product name',
hot tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Is it popular: 1 yes 0 no',
sort tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Sort',
status tinyint(1) NOT NULL DEFAULT '1' COMMENT '1Add 2 Remove 3Delete',
PRIMARY KEY (p_id)
)

A product standard table with the following fields:
CREATE TABLE ft_products_standard (
p_s_id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Product rule table',
p_id int(11 ) unsigned NOT NULL DEFAULT '0' COMMENT 'Product id',
price decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'Unit price',
buyed_count int(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Already raised Number of copies',
total_count int(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Total number of copies required',
c_time int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Creation time',
PRIMARY KEY (p_s_id)
)

Now based on these two tables, one product corresponds to multiple prices. I want to sort by the number of shares raised. But the pagination must be based on products. How should I write the sql statement?

Answer 1

select p_name,hot,sort,status,sum(buyed_count) as total_buyed_count from ft_products_standard left join ft_products on ft_products_standard.p_id=ft_products.p_id group by ft_products_standard.p_id order by total_buyed_count desc limit 0,10