mysql 常用查询（一）

-- 1:case when --
SELECT
    CASE
    WHEN (admin_id IS NULL) THEN '无创建人'
    WHEN (admin_id = 0) THEN '管理员'
    ELSE admin_id
    END AS admin_id
FROM hsb_investor
-- 2:if else 是不能在select 语句中使用的。只能用来控制sql程序的流程。像你这种情况只能用case的。或者采用以下的方式
SELECT IF(admin_id=0,'管理员','非管理员') AS admin_id FROM hsb_investor

SELECT IFNULL(admin_id,'无数据') AS admin_id FROM hsb_investor

SELECT a.id AS investorJobId,
IF (a.end_date IS NULL,'至今',DATE_FORMAT(a.end_date,'%Y-%m')) AS endDate
FROM hsb_investor_job a
WHERE a.enable_flag = 1
AND a.investor_id = 67
ORDER BY a.create_time DESC


-- 3:decode方法（mysql中是加密和oracle中是不一样的） -------------

-- 4:mysql常用函数 --
    -- 字符串合并
    SELECT CONCAT(id,'-',NAME) AS id_name FROM hsb_investor
    -- 子表所有的ID拼接

　　SELECT *,
    (SELECT GROUP_CONCAT(id) FROM hsb_investor_job WHERE investor_id = investor.id) AS investorjob_ids
    FROM hsb_investor AS investor

　　SELECT a.*,
    GROUP_CONCAT(b.id)
    FROM hsb_investor AS a
    LEFT JOIN hsb_investor_job AS b
    ON a.id = b.investor_id
    GROUP BY a.id

转载于:https://www.cnblogs.com/goldberg/p/5477306.html