SELECT qr_cash_record.*,
CASE
WHEN cashrecord_type = 3 THEN
(SELECT product_title FROM qr_fundsupport LEFT JOIN qr_fundplan
ON fundplan_id = fundsupport_ref_fundplanid LEFT JOIN qr_product
ON product_id = fundplan_ref_productid WHERE fundsupport_id = cashrecord_ref_id )
WHEN cashrecord_type = 4 THEN
(SELECT product_title FROM qr_fundplan
LEFT JOIN qr_product
ON product_id = fundplan_ref_productid
WHERE fundplan_id = cashrecord_ref_id )
ELSE
(SELECT product_title FROM qr_touristorder
LEFT JOIN qr_product ON product_id = touristorder_ref_productid
WHERE touristorder_id = cashrecord_ref_id )
END AS product_title,
CASE
WHEN cashrecord_type = 3 THEN
(SELECT product_id FROM qr_fundsupport LEFT JOIN qr_fundplan
ON fundplan_id = fundsupport_ref_fundplanid LEFT JOIN qr_product
ON product_id = fundplan_ref_productid WHERE fundsupport_id = cashrecord_ref_id )
WHEN cashrecord_type = 4 THEN
(SELECT product_id FROM qr_fundplan
LEFT JOIN qr_product
ON product_id = fundplan_ref_productid
WHERE fundplan_id = cashrecord_ref_id )
ELSE
(SELECT product_id FROM qr_touristorder
LEFT JOIN qr_product ON product_id = touristorder_ref_productid
WHERE touristorder_id = cashrecord_ref_id )
END AS product_id,
CASE
WHEN cashrecord_type = 3 THEN
(SELECT product_type FROM qr_fundsupport LEFT JOIN qr_fundplan
ON fundplan_id = fundsupport_ref_fundplanid LEFT JOIN qr_product
ON product_id = fundplan_ref_productid WHERE fundsupport_id = cashrecord_ref_id )
WHEN cashrecord_type = 4 THEN
(SELECT product_type FROM qr_fundplan
LEFT JOIN qr_product
ON product_id = fundplan_ref_productid
WHERE fundplan_id = cashrecord_ref_id )
ELSE
(SELECT product_type FROM qr_touristorder
LEFT JOIN qr_product ON product_id = touristorder_ref_productid
WHERE touristorder_id = cashrecord_ref_id )
END AS product_type,
CASE
WHEN cashrecord_type = 3 THEN
(SELECT fundplan_ref_userid FROM qr_fundsupport LEFT JOIN qr_fundplan
ON fundplan_id = fundsupport_ref_fundplanid
WHERE fundsupport_id = cashrecord_ref_id )
WHEN cashrecord_type = 4 THEN
(SELECT fundplan_ref_userid FROM qr_fundplan
WHERE fundplan_id = cashrecord_ref_id )
ELSE
(SELECT touristorder_ref_grideorderid FROM qr_touristorder
WHERE touristorder_id = cashrecord_ref_id )
END AS ref_user_id,
CASE
WHEN cashrecord_type = 3 THEN
(SELECT auth_realname FROM qr_fundsupport LEFT JOIN qr_fundplan
ON fundplan_id = fundsupport_ref_fundplanid
LEFT JOIN qr_userauth ON auth_ref_userid = fundplan_ref_userid
WHERE fundsupport_id = cashrecord_ref_id AND auth_type=0 AND auth_limitstate=2 )
WHEN cashrecord_type = 4 THEN
(SELECT auth_realname FROM qr_fundplan
LEFT JOIN qr_userauth ON auth_ref_userid = fundplan_ref_userid
WHERE fundplan_id = cashrecord_ref_id AND auth_type=0 AND auth_limitstate=2 )
ELSE
(SELECT auth_realname FROM qr_touristorder
LEFT JOIN qr_userauth ON auth_ref_userid = touristorder_ref_grideorderid
WHERE touristorder_id = cashrecord_ref_id AND auth_type=0 AND auth_limitstate=2 )
END AS ref_user_realname
FROM qr_cash_record;