查询用户组每个人的最后一次动作

SELECT
	* 
FROM
	( SELECT * FROM user_store_value_action_log WHERE 
            cno IN ( '1000500080001021', '1138006402504960', '103755811034' ) 
            AND `STATUS` = '1' 
            ORDER BY `date` DESC ) AS t 
GROUP BY
	cno;

查询用户组每个人的最后一次储值动作,转载,自用

补充,查询最后一次为某个动作的sql

SELECT
   cno 
FROM
   ( SELECT * FROM 
        user_store_value_action_log 
    WHERE 
        cno IN ({cno_lts}) 
    AND STATUS = '1' 
    AND date <= '{date}' 
    ORDER BY date DESC ) AS t 
GROUP BY    
   cno 
HAVING
   t.new_grade = '储值'

ps: mysql 5.7版本这个方法失效了!

 

sql = """
SELECT
	cno,
	GROUP_CONCAT( date ),
	GROUP_CONCAT( sid ),
	GROUP_CONCAT( fee ) 
FROM
	acewill_store_value 
WHERE
	type = 1 
GROUP BY
	cno 
ORDER BY
	date DESC"""

result = ....  # 查询结果

lt = []
for i in result:
    lt.append([i[0], i[1].split(',')[-1], i[2].split(',')[-1], i[3].split(',')[-1]])

稍微麻烦点,python程序端处理了