mysql 链表查询 原生sql

 

三个sql语句 ：

第一条: 

SELECT
    count( * ) AS total,
    id,
    member_id 
FROM
    rf_member_buy_set_meal 
WHERE
    city_code = 440300 
    AND pay_status = 1 
    AND order_type = 1 
and status = 1
GROUP BY
    member_id 
HAVING
    total = 1

 

 

 

第二条:

SELECT
a.id,
 a.member_id,
 b.pid,
 b.realname,
 b.mobile,
 b.member_level,
 FROM_UNIXTIME( a.pay_time, '%Y-%m-%d %H:%i:%s' ) AS pay_time 
FROM
 rf_member_buy_set_meal AS a
 LEFT JOIN rf_member AS b ON a.member_id = b.id 
WHERE
 a.id in (id集合) and 
 a.city_code = 440300 and
 a.pay_status =1 and 
 a.order_status in (1,0) and 
 a.order_type =1 
 AND ( a.pay_time >= '1620230400' AND a.pay_time <= '1620316799' )

 

 

 

 

第三条SQL语句：

SELECT id,realname,mobile from rf_member WHERE id IN(pid集合) 

 

 

 

 

合并sql语句：

SELECT a.id,a.member_id,a.pid,a.realname,a.mobile,a.member_id,a.pay_time,b.id,b.realname,b.mobile from (

SELECT
a.id,
 a.member_id,
 b.pid,
 b.realname,
 b.mobile,
 b.member_level,
 FROM_UNIXTIME( a.pay_time, '%Y-%m-%d %H:%i:%s' ) AS pay_time 
FROM
 rf_member_buy_set_meal AS a
 LEFT JOIN rf_member AS b ON a.member_id = b.id 
WHERE
 a.id in (
 
 
 SELECT id from (

SELECT
    count( * ) AS total,
    id,
    member_id 
FROM
    rf_member_buy_set_meal 
WHERE
    city_code = 440300 
    AND pay_status = 1 
    AND order_type = 1 
and status = 1
GROUP BY
    member_id 
HAVING
    total = 1

) as dd
 
 ) and 
 a.city_code = 440300 and
 a.pay_status =1 and 
 a.order_status in (1,0) and 
 a.order_type =1 
 AND ( a.pay_time >= '1620662400' AND a.pay_time <= '1620748799' )
 ) as a LEFT JOIN rf_member as b ON b.id = a.pid