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;