case
when t1.update_date > t2.update_date then t1.update_by
when t1.update_date < t2.update_date then t2.update_by
when t1.update_date is null then t2.update_by
when t2.update_date is null then t1.update_by
else t2.update_by
end
as update_by
SELECT
CASE
WHEN
salary <= 500
THEN
'1'
WHEN
salary > 500
AND
salary <= 600
THEN
'2'
WHEN
salary > 600
AND
salary <= 800
THEN
'3'
WHEN
salary > 800
AND
salary <= 1000
THEN
'4'
ELSE
NULL
END
salary_class,
COUNT
(*)
FROM
Table_A
sql中in的用法:
update order set status=1 where order_id in ('110','113','112','111');
mabatis从多个表查数据时有相同字段名处理:
sql语句的select部分定义别名,然后result标签的column属性值写别名就好。没有别名时column的值写列名就好不用关心/写哪个表
连接查询
基础表:
内连接(遍历两个表中的任意一个表A的记录,去另一表B中用条件遍历匹配B中的每一条记录,返回条件匹配成功的记录)
第一种写法:(只使用where)
select t.teacher_name, s.student_name from teacher t,student s where t.id = s.teacher_id;
第二种写法:(join .. on.. )
select t.teacher_name, s.student_name from teacher t join student s on t.id = s.teacher_id;
第三种写法:(inner join .. on.. )
select t.teacher_name, s.student_name from teacher t inner join student s on t.id = s.teacher_id;
外连接(左连接,右连接,全连接)
(在内连接基础上返回要求全的那张表(左连接就是左表全,右连接就是右表全)的剩余所有记录(对应的另一侧的记录自然是空))
左连接右链接:
左连接左边的表会全返回(右侧表只返回符合条件的),右连接右边的表的记录全返回,左侧只返回符合条件的记录。
左连接示例:
第一种写法:(left join .. on ..)
select t.teacher_name, s.student_name from teacher t left join student s on t.id = s.teacher_id;
第二种写法:(left outer join .. on ..)
select t.teacher_name, s.student_name from teacher t left outer join student s on t.id = s.teacher_id;
第三种写法:"(+)" 所在位置的另一侧为连接的方向
select t.teacher_name, s.student_name from teacher t, student s where t.id = s.teacher_id(+);
全连接:
musql不支持全连接,可使用UNION联合左连接和右连接来实现,其实就是将左连接和右连接的结果取并集(集合{1, 2, 3} 和 {2, 3, 4} 的并集是 {1, 2, 3, 4}不是{1,2,3,2,3,4})。
自连接:
不过是因为两张表是同一张表而已,跟内外连接没关系,或者说既可以内连接也可以外连接。
查表A的hotel_id有无重复:
select count(hotel_id) from 表A
select count(distinct(hotel_id)) from 表A
select * from ih_order where cancel_time like '%2018-10-%' and erp_order_id is not null and pay_status=3 and order_status=14 group by SUBSTRING(cancel_time,1,10)
HAVING 子句必须放在 GROUP BY 子句之后,必须放在 ORDER BY 子句之前
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
O_Id OrderDate OrderPrice Customer
1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter
现在,我们希望查找订单总金额少于 2000 的客户。
我们使用如下 SQL 语句:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000