sql收藏

如下SQL：

UPDATE tb_project_message_board a SET fd_file_type_code =(SELECT fd_file_type_code FROM tb_project_file
WHERE ID = a.fd_file_id) 

需求：两张表：表一  tb_project_message_board  ，表二：tb_project_file 

两张表 表二为主表，主键ID ，副表为表一，附表与主表关联的字段为  fd_file_id，同时他们还有一个相同的字段 fd_file_type_code，主表中有code值 但是从表没有，这个时候想要将主表的code值存入从表就可以使用以上sql。俗称子查询。

 

 

 

set @rownum=0;
SELECT z.rownum,z.realName,z.greenCardCount FROM (SELECT  @rownum:=@rownum+1 as rownum ,count(1) AS greenCardCount,u.fd_user_real_name as realName,u.fd_user_phone AS phone FROM
tb_green_card g
LEFT JOIN tb_marathon_userinfo u ON g.fd_user_phone = u.fd_user_phone
GROUP BY g.fd_user_phone ORDER BY greenCardCount DESC)z WHERE z.phone = '13691425117'

（随机抽取十名用户，不包含前十名中奖用户）

select * from 
(SELECT fd_user_phone AS fdUserPhone, fd_user_real_name AS fdUserRealName ,fd_user_identity AS fdUserIdentity     FROM tb_marathon_userinfo us
WHERE us.fd_user_id NOT IN
(select ss.fd_user_id FROM (SELECT u.fd_user_id FROM
tb_green_card g
LEFT JOIN tb_marathon_userinfo u ON g.fd_user_phone = u.fd_user_phone
GROUP BY g.fd_user_phone ORDER BY count(1) DESC limit 0,10) as ss) 
ORDER BY RAND() LIMIT 10) tab INNER JOIN 
(select fd_user_phone,count(fd_green_card_num) AS fdGreenCardCount from tb_green_card group by fd_user_phone) g on tab.fdUserPhone=g.fd_user_phone

=============================通过手机号获取当前自身的排名sql加序号

SELECT * from 
(SELECT @row_number := @row_number + 1 as rownum,dd.*  FROM (select @row_number := 0)AS ss, 
(SELECT count(1) AS fdGreenCardCount,u.fd_user_real_name as fdUserRealName,u.fd_user_phone AS fdUserPhone FROM
tb_green_card g LEFT JOIN tb_marathon_userinfo u ON g.fd_user_phone = u.fd_user_phone
GROUP BY g.fd_user_phone 
ORDER BY fdGreenCardCount 
DESC )AS dd) as ff 
WHERE ff.fdUserPhone = '13691425131'

=============================在所有用户中查询出购买绿卡数最多的前十名用户 并加序号rownumber

SELECT @row_number := @row_number + 1 as rownum,dd.*  FROM (select @row_number := 0)AS ss, 
(SELECT count(1) AS fdGreenCardCount,u.fd_user_real_name as fdUserRealName,u.fd_user_identity AS fdUserIdentity FROM 
tb_green_card g LEFT JOIN tb_marathon_userinfo u ON g.fd_user_phone = u.fd_user_phone
GROUP BY g.fd_user_phone ORDER BY fdGreenCardCount DESC LIMIT 10) dd

8888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#####随机抽取绿卡十名用户 过滤掉前三十名中奖的用户
select * from 
(SELECT fd_user_phone AS fdUserPhone, fd_user_real_name AS fdUserRealName ,fd_user_identity AS fdUserIdentity     
  FROM tb_marathon_userinfo us
  WHERE us.fd_user_id NOT IN
(select ss.fd_user_id FROM (SELECT u.fd_user_id FROM
tb_green_card g
LEFT JOIN tb_marathon_userinfo u ON g.fd_user_phone = u.fd_user_phone
GROUP BY g.fd_user_phone ORDER BY count(1) DESC limit 0,10) as ss) 
and us.fd_user_phone not in ( select fd_user_phone from tb_answer_phone)
ORDER BY RAND() LIMIT 10) tab INNER JOIN 
(select fd_user_phone,count(fd_green_card_num) AS fdGreenCardCount from tb_green_card group by fd_user_phone) g on tab.fdUserPhone=g.fd_user_phone

#统计处所有购买绿卡个数最多的前十名用户
SELECT @row_number := @row_number + 1 as rownum,dd.*
FROM (select @row_number := 0) AS ss, 
(SELECT count(1) AS fdGreenCardCount,u.fd_user_real_name as fdUserRealName,u.fd_user_identity AS fdUserIdentity,u.fd_user_phone
 FROM tb_green_card g LEFT JOIN tb_marathon_userinfo u ON g.fd_user_phone = u.fd_user_phone
GROUP BY g.fd_user_phone ORDER BY fdGreenCardCount DESC LIMIT 10) dd

#过滤掉第一轮活动的二十名用户手机号抽取出剩余用户所拥有绿卡个数最多的十名用户
SELECT @row_number := @row_number + 1 as rownum,dd.*
FROM (select @row_number := 0) AS ss, 
(SELECT count(1) AS fdGreenCardCount,u.fd_user_real_name as fdUserRealName,u.fd_user_identity AS fdUserIdentity,u.fd_user_phone
 FROM tb_green_card g LEFT JOIN tb_marathon_userinfo u ON g.fd_user_phone = u.fd_user_phone
 where u.fd_user_phone not in ( select fd_user_phone from tb_answer_phone)
GROUP BY g.fd_user_phone ORDER BY fdGreenCardCount DESC LIMIT 10) dd