mysql操作笔记

阅读次数: 1,075

  • A+
所属分类:MYSQL

批量插入


DELIMITER $$

USE `diaoyoutianxia`$$

DROP PROCEDURE IF EXISTS `p_make_data`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_make_data`()
BEGIN
    DECLARE i INT ;
		set i=0;
    WHILE i<l000 DO
        insert into `33hao_member_common` (`member_id`) values('');
        SET i = i + 1;
    END WHILE;
    END$$

DELIMITER ;

 

两个表连查 表A 表B  a.member_id  =  b.member_id  但是有可能 B表的数据有多个,但只取其中一条

SELECT DISTINCT 33hao_bbs_child.id as new_id,33hao_bbs_child.*,33hao_bbs_parents.parents_name,33hao_bbs_parents.parents_phone FROM 33hao_bbs_child LEFT JOIN 33hao_bbs_parents ON 33hao_bbs_child.member_id = 33hao_bbs_parents.member_id  GROUP BY new_id HAVING count(*)>0

统计相应的个数

SELECT count(*) FROM (SELECT DISTINCT 33hao_bbs_child.id as new_id,33hao_bbs_child.*,33hao_bbs_parents.parents_name,33hao_bbs_parents.parents_phone FROM 33hao_bbs_child LEFT JOIN 33hao_bbs_parents ON 33hao_bbs_child.member_id = 33hao_bbs_parents.member_id  GROUP BY new_id HAVING count(*)>0) aa

 


发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: