多年不刷题,然后把sql的基本知识忘得差不多了,现在来简单记录一下。
先看sql
用户表 这里都是基于新建一个world数据库先
create table world.user_info(
`id` int(11) not NULL AUTO_INCREMENT,
`phone` VARCHAR(20) DEFAULT NULL,
`company_name` VARCHAR(250) DEFAULT null,
PRIMARY key(`id`)
)
INSERT into world.user_info(phone,company_name)values("13745677654","qwe");
申请表
create table world.apply_info(
`id` int(11) not NULL AUTO_INCREMENT,
`phone` VARCHAR(20) DEFAULT NULL,
`amount` DECIMAL(22,6) DEFAULT null,
PRIMARY key(`id`)
)
INSERT INTO `world`.`apply_info` (`id`, `phone`, `amount`, `source`) VALUES ('1', '13745677654', '100.000000', 'ali');
INSERT INTO `world`.`apply_info` (`id`, `phone`, `amount`, `source`) VALUES ('2', '13745677654', '200.000000', 'baidu');
INSERT INTO `world`.`apply_info` (`id`, `phone`, `amount`, `source`) VALUES ('3', '13745677654', '300.000000', 'tengxun');
INSERT into world.apply_info(phone,amount)values("13745676666",300);
目标,用户表和申请表中amount数值最大的记录根据phone进行匹配,然后进行其他判断。
嗯,首先,我们写查询都是从子查询写起的,像这里,就是先找到同一个phone的最大amount,然后取出这条记录先。
SELECT max(amount) from world.apply_info GROUP BY phone;
这样就得到同一个phone的最大的amount了。
然后取出这条记录
SELECT ai.* from world.apply_info ai ,(SELECT max(amount) amount from world.apply_info GROUP BY phone) maxai where ai.amount=maxai.amount;
也就是把和原有的表中有相同最大值的先取出来,但是这里有问题,就是这里的最大值并不是唯一的,可能有的手机号的非最大值和另外的手机号的最大值可以匹配,所以这里要加上手机号,即
SELECT ai.* from world.apply_info ai ,(SELECT max(amount) amount,phone from world.apply_info GROUP BY phone) maxai where ai.amount=maxai.amount and ai.phone=maxai.phone;
这样就取出了每个手机号的最大值的记录了。如果这里的不是amount,而是一个唯一索引或者主键的话,那就不需要后面的手机号相等的条件了。此时结果如下
然后再往外和用户表关联
SELECT * from world.user_info ui,(SELECT ai.* from world.apply_info ai ,(SELECT max(amount) amount,phone from world.apply_info GROUP BY phone) maxai where ai.amount=maxai.amount and ai.phone=maxai.phone) wai where ui.phone=wai.phone;
这样就得到了两个表的关联记录了。此时就可以基于此记录再进行其它判断了,比如用户表的某个字段和申请表的某个字段不相等之类的。
然后就ok了。
这里遇到一个坑就是
SELECT ai.* from world.apply_info ai ,(SELECT max(amount) amount,phone from world.apply_info GROUP BY phone) maxai where ai.amount=maxai.amount and ai.phone=maxai.phone;
这里,select max(amount)这里需要给select出来的最大值取别名amount,它默认不是amount的。。我刚开始没取别名,但是又一直使用maxai.amount去标识这个字段,然后一直报没有这个字段,然后就很尴尬。。。
嗯,这种使用一个缓存表先连接取得数据,然后再和原来的表关联取出所有数据的方法是一种通用方法,简称缓存表法吧。 太久没写复杂sql了,感觉是时候刷一刷基础了。。
如果有简单方法欢迎提出指正~