eg:
SELECT * FROM loan_info a INNER JOIN loan_over_info b ON a.repayment_id=b.repayment_id
用INNER JOIN取交集,LEFT JOIN加where条件可以达到取差集的效果
创建两个表
-
CREATE TABLE `object_a` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `oname` varchar(50) DEFAULT NULL, `odesc` varchar(50) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
添加数据
-
CREATE TABLE `object_b` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `oname` varchar(50) DEFAULT NULL, `odesc` varchar(50) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
添加数据
查询并集(union all)
- SELECT oname,odesc FROM object_a
- UNION ALL
- SELECT oname,odesc FROM object_b
结果如下
查询并集(union)
- SELECT oname,odesc FROM object_a
- UNION
- SELECT oname,odesc FROM object_b
结果如下
PS:union自带去重
查询交集
- SELECT a.oname,a.odesc FROM object_a a INNER JOIN object_b b ON a.oname=b.oname AND a.odesc=b.odesc
等价于
- SELECT a.oname,a.odesc FROM object_a a INNER JOIN object_b b USING(oname,odesc)
结果如下
PS:别的数据库可以试试这种写法
- SELECT oname,odesc FROM object_a
- INTERSECT
- SELECT oname,odesc FROM object_b
查询差集
-
SELECT a.oname, a.odesc FROM object_a a LEFT JOIN object_b b ON a.oname = b.oname AND a.odesc = b.odesc WHERE b.id IS NULL
结果如下
case..when..then..else..end
Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
判断某个字段是不是空,IS NULL 和IS NOT NULL
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
SELECT
CASE
WHEN a. STATUS IN ('2','4') THEN
3
WHEN a. STATUS NOT IN ('2','4') THEN
5
END
FROM
TABLE a
-- 下边的等效
SELECT
a.real_name,
(CASE WHEN a.status in ('2','4') THEN (a.recover_amount - a.service_amount- a._amount) ELSE a.overdue_amount END),
a.case_date
FROM
table a
SELECT
a.real_name,
(case when a.status not in (2,4) then a.amount
when a.status in (2,4) then (a.recover_amount - a.service_amount- a._amount) end) ,
a.case_date
FROM
table a