样例判断匹配
SELECT
t.NAME,
(
CASE t.sex
WHEN 1 THEN
‘男’
WHEN 0 THEN
‘女’
ELSE
‘未知’
END
) 性别
FROM
t_customer t
指定查看某个库的某个表的所有字段
SELECT DISTINCT column_Name FROM information_schema.COLUMNS WHERE TABLE_NAME = ‘tableName’ AND TABLE_SCHEMA=‘cona_web’ AND TABLE_NAME NOT LIKE ‘vw%’;
按某个值分组,如果并制定分组排名的位数,不足的前面补零
SELECT b.* , LPAD(b.rank,2,0) FROM
(SELECT a.*, IF(@p=floor_id,@r:=@r+1,@r:=1) AS rank ,
@p:=floor_id
FROM fhfq a ,(SELECT @p:=NULL,@r:=0)r
ORDER BY a.floor_id
) b
《《《mysql中相较于hive排名函数上使用比较复杂,有更好的方法希望各位大佬指导》》》
将list按着#进行切分,然后与另外一张表进行关联之后进行聚合
行转列 列转行
SELECT
c.,
GROUP_CONCAT(d.course_name) AS course_id_list
FROM
(SELECT
a.,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
a.co