MySQL查询是否存在指定的记录

有时候须要查询的数据在另外一张表中，这时候首先想到的是链接查询，但若是要查询的数据并非直接是另外一张表的字段，而是还要对另外一张表记录进行分析时呢？接下来以查询玩家是否充值过某档充值和充值总额作记录。sql

准备一下要用的两张表：ui

#t_player 玩家表
CREATE TABLE `t_player` (
  `uid` varchar(45) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_player` (`uid`, `name`) VALUES ('1', 'a');
INSERT INTO `t_player` (`uid`, `name`) VALUES ('2', 'b');
INSERT INTO `t_player` (`uid`, `name`) VALUES ('3', 'c');


#t_recharge 玩家充值表
CREATE TABLE `t_recharge` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(45) DEFAULT NULL,
  `orderId` varchar(45) DEFAULT NULL,
  `customId` varchar(45) DEFAULT NULL,
  `pay` int(11) DEFAULT NULL,
  `cfgId` int(11) DEFAULT NULL,
  `time` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
INSERT INTO `t_recharge` (`id`, `uid`, `orderId`, `customId`, `pay`, `cfgId`, `time`) VALUES ('1', '1', '1', '1', '1', '1', '1538231381');
INSERT INTO `t_recharge` (`id`, `uid`, `orderId`, `customId`, `pay`, `cfgId`, `time`) VALUES ('2', '1', '2', '2', '2', '2', '1538231761');
INSERT INTO `t_recharge` (`id`, `uid`, `orderId`, `customId`, `pay`, `cfgId`, `time`) VALUES ('3', '2', '3', '3', '1', '1', '1538231761');
INSERT INTO `t_recharge` (`id`, `uid`, `orderId`, `customId`, `pay`, `cfgId`, `time`) VALUES ('4', '3', '4', '4', '2', '2', '1538231761');

接下来是查询基本信息+是否充值过cfgId为1档的充值+是否充值过cfgId为1档的充值+充值总额code

select t_player.uid, name, 
	ifnull((select 1 from t_recharge where uid = t_player.uid and cfgId = 1 limit 1), 0) as cfgId1, 
    ifnull((select 1 from t_recharge where uid = t_player.uid and cfgId = 2 limit 1), 0) as cfgId2, 
    ifnull(sum(pay), 0) as total
from t_player left join t_recharge on t_player.uid = t_recharge.uid 
group by t_player.uid;

固然，还能够查询进行某项操做的次数，好比充值cfgId为1档的充值次数it

select t_player.uid, name, 
	ifnull((select count(*) from t_recharge where uid = t_player.uid and cfgId = 1), 0) as cfgId1Count, 
    ifnull((select count(*) from t_recharge where uid = t_player.uid and cfgId = 2), 0) as cfgId2Count, 
    ifnull(sum(pay), 0) as total
from t_player left join t_recharge on t_player.uid = t_recharge.uid 
group by t_player.uid;