子循环读取最外层参数
SELECT
*
FROM
(
SELECT
concat(
ifnull( employee_name, '无' ),
'/',
ifnull( c.real_name, '无' ),
'(',
ifnull( b.org_title, '无' ),
')'
) AS `姓名`,
(
SELECT
IFNULL( ROUND( count( DISTINCT baga.customer_order_id )*(( round( avg( baga.rate ), 2 ))/ 100 ), 2 ), 0 )
FROM
(
SELECT
IF
( @p = user_id, @r := @r + 1, @r := 1 ) AS rank,
@p := user_id,
x.*
FROM
( SELECT @p := NULL, @r := 0 ) r,
(
SELECT
coes.customer_order_id,
coes.user_id,
coes.employee_id,
coes.order_date,
coes.score,
coes.rate,
d.is_first_sales
FROM
customer_order_employee_score coes
LEFT JOIN customer_order co ON co.id = coes.customer_order_id
LEFT JOIN employee e ON e.id = coes.employee_id
LEFT JOIN department d ON d.id = e.org_id
WHERE
coes.deleted_time IS NULL
AND co.origin_customer_order_id IS NULL
AND coes.user_id
AND co.order_status IN ( 2, 3, 5, 6 )
AND coes.order_date >= '2010-03-31 00:00:00.000'
AND coes.order_date < '2022-05-01 23:59:59.000'
AND d.is_first_sales = 0
AND coes.employee_id = 94 --这里关联下面的a.id
GROUP BY
coes.customer_order_id,
coes.user_id,
coes.employee_id,
coes.order_date,
d.is_first_sales
ORDER BY
user_id,
coes.order_date
) x
) baga
WHEREa.id
baga.employee_id = a.id
AND
baga.rank = 1
) AS `一次单数量`,(
SELECT
ifnull( sum( sc.score ), 0 )
FROM
customer_order_employee_score sc
INNER JOIN customer_order co ON sc.customer_order_id = co.id
WHERE
employee_id = a.id
AND order_date >= '2010-03-31 00:00:00.000'
AND order_date < '2022-05-01 23:59:59.000'
AND sc.deleted_time IS NULL
AND co.order_status IN ( 2, 3, 5, 6 )) AS `订单总业绩`
FROM
employee a
LEFT JOIN department b ON a.org_id = b.id
LEFT JOIN employee_profile c ON c.employee_id = a.id
WHERE
1 = 1
AND (
a.employee_name IN ( '李江华', 'wuyanmei', 'xiaogu1', 'xiaogu3', 'yzc(二线)' )
OR a.org_id IN ( 0 ))
AND b.is_first_sales = 0 --下面的a.id
AND a.id IN ( SELECT DISTINCT sc.employee_id FROM customer_order_employee_score sc WHERE sc.order_date BETWEEN '2010-03-31 00:00:00.000' AND '2022-05-01 23:59:59.000' )
AND a.deleted_time IS NULL
ORDER BY
`订单总业绩` DESC
LIMIT 0,
10
) tb
