创建视图时用group by 以后再inner join出现分页数据重复问题

CREATE OR REPLACE VIEW v_shareholder_profit_detail AS
SELECT g.id farm_id,a.plan_id,f.name org_name,g.name farm_name,c.name category_name,d.name variety_name,
b.name plan_name,e.plant_batch,case e.status WHEN 1 then ‘进行中’ when 2 then ‘已结束’ END status,
IFNULL(l.name,‘无’) shareholder_name,IF(l.credit_code is NULL or l.credit_code = ‘’,‘无’,l.credit_code) credit_code,
IF(l.mobile is NULL or l.mobile = ‘’,‘无’,l.mobile) mobile,
IFNULL(h.total_sales,0) total_sales,#销售额
IFNULL(IFNULL(i.farm_work_total_cost,0)+IFNULL(j.apportion_money,0),0) as total_cost,#种植生产成本
IFNULL(IFNULL(h.total_sales,0)-IFNULL(i.farm_work_total_cost,0)-IFNULL(j.apportion_money,0),0) as total_profit,#利润总额
IFNULL(k.percent,0) percent,#股东占比
CAST(IFNULL((IFNULL(h.total_sales,0)-IFNULL(i.farm_work_total_cost,0)-IFNULL(j.apportion_money,0))*IFNULL(k.percent,0)*0.01,0) AS DECIMAL(16,2)) shareholder_profit#股东利润
FROM pm_plan_variety a
LEFT JOIN pm_plan b ON a.plan_id = b.id
LEFT JOIN pm_plant_batch e ON e.plan_variety_id = a.id
LEFT JOIN pm_variety_category c ON a.variety_category_id = c.id
INNER JOIN pm_variety d ON a.variety_id = d.id
LEFT JOIN pm_production_org f ON b.production_org_id = f.id
INNER JOIN pm_farm g ON b.farm_id = g.id AND g.delete_status=FALSE
LEFT JOIN(
SELECT h1.plant_batch,sum(h1.total_price) as total_sales
FROM pm_sale_settlement_record h1
WHERE h1.delete_status=FALSE GROUP BY h1.plant_batch
)h ON e.plant_batch = h.plant_batch
LEFT JOIN(
SELECT i1.plant_batch,sum(i1.mater_total_cost+i1.total_cost) as farm_work_total_cost
FROM pm_farm_work i1
WHERE i1.delete_status=FALSE GROUP BY i1.plant_batch
)i ON e.plant_batch = i.plant_batch
LEFT JOIN (
SELECT j1.plant_batch,sum(j1.apportion_money) apportion_money
FROM pm_sundry_apportion j1
WHERE j1.delete_status=FALSE GROUP BY j1.plant_batch
)j ON e.plant_batch = j.plant_batch
LEFT JOIN pm_plan_shareholder k ON a.plan_id = k.plan_id
LEFT JOIN pm_shareholder_info l ON k.shareholder_id= l.id
WHERE a.delete_status=FALSE AND k.delete_status=FALSE
GROUP BY e.plant_batch,k.shareholder_id
ORDER BY g.name ASC,e.plant_batch ASC,l.name DESC

SELECT v.* from v_shareholder_profit_summary v
INNER JOIN pm_farm pf ON farm_id = pf.id AND pf.delete_status = FALSE
LIMIT 1, 10;
SELECT v.* from v_shareholder_profit_summary v
INNER JOIN pm_farm pf ON farm_id = pf.id AND pf.delete_status = FALSE
LIMIT 11, 10;
SELECT v.* from v_shareholder_profit_summary v
INNER JOIN pm_farm pf ON farm_id = pf.id AND pf.delete_status = FALSE
LIMIT 21, 10;
SELECT v.* from v_shareholder_profit_summary v
INNER JOIN pm_farm pf ON farm_id = pf.id AND pf.delete_status = FALSE
LIMIT 31, 10;
SELECT v.* from v_shareholder_profit_summary v
INNER JOIN pm_farm pf ON farm_id = pf.id AND pf.delete_status = FALSE
LIMIT 41, 10;
SELECT v.* from v_shareholder_profit_summary v
INNER JOIN pm_farm pf ON farm_id = pf.id AND pf.delete_status = FALSE
LIMIT 51, 10;
SELECT v.* from v_shareholder_profit_summary v
INNER JOIN pm_farm pf ON farm_id = pf.id AND pf.delete_status = FALSE
LIMIT 61, 10;
SELECT v.* from v_shareholder_profit_summary v
INNER JOIN pm_farm pf ON farm_id = pf.id AND pf.delete_status = FALSE
LIMIT 71, 10;
SELECT v.* from v_shareholder_profit_summary v
INNER JOIN pm_farm pf ON farm_id = pf.id AND pf.delete_status = FALSE
LIMIT 81, 10;
SELECT v.* from v_shareholder_profit_summary v
INNER JOIN pm_farm pf ON farm_id = pf.id AND pf.delete_status = FALSE
LIMIT 91, 10;
分页查旬时会出现重复数据，这是因为inner join 用的是farm_id做关联，所以视图的创建语句需要用group by时 只能用group by 或者不用group by 否者分页查询时就会出现数据重复问题。
两天时间才得以解决。