// 想要的字段值不能通过分组取最大的方式获取到,应该如何处理?
//1、分组取出最大的理想数据
//2、通过理想数据中,能够确定唯一数据的关键字段,再次关联join之前的表,
//3、关联出之前的表后,即可取到想要数据中的想要字段!
SELECT
s.id AS '主表 id',
p.id AS '子表 id'
FROM
(
SELECT
max( sp.start_date ) startDate,
max( sp.update_date ) updateDate,
spl.project_id AS customerId,
spl.varieties_id AS varietiesId
FROM
t_sales_price_list_去掉 spl
JOIN t_sales_price_去掉 sp ON spl.price_id = sp.id
WHERE
spl.STATUS = '0'
AND spl.enable_flag = '1'
AND sp.STATUS = '0'
AND sp.flow_type = '0'
AND sp.company_code = '0107'
AND sp.start_date <= '2020-02-26 00:00:00.0'
GROUP BY
customerId,
varietiesId
) m
JOIN t_sales_price_list_去掉 p ON m.customerId = p.project_id
AND m.varietiesId = p.varieties_id
JOIN t_sales_price_去掉 s ON p.price_id = s.id
AND m.startDate = s.START_DATE
AND m.updateDate = s.update_date
JOIN js_sys_user_去掉 u ON u.user_code = s.create_by
JOIN t_customer_list_去掉 c ON c.id = p.project_id
JOIN t_sales_area_set_去掉 sae ON c.area = sae.id
WHERE
1 =1