MySQL Common query (i)

--1:case when--
SELECT
Case
When (admin_id are NULL) Then ' no creator '
When (admin_id = 0) Then ' admin '
ELSE admin_id
END as admin_id
From Hsb_investor
--2:if else is not available in the SELECT statement. A process that can only be used to control SQL programs. You can only use cases like this. Or use the following method
SELECT IF (admin_id=0, ' admin ', ' Non-Administrator ') as admin_id from Hsb_investor

SELECT ifnull (admin_id, ' no data ') as admin_id from Hsb_investor

SELECT a.ID as Investorjobid,
IF (A.end_date is NULL, ' to date ', 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


--The 3:decode method (MySQL is not the same as encryption in Oracle)-------------

--4:mysql common function--
--String Merging
SELECT CONCAT (ID, '-', NAME) as Id_name from Hsb_investor
--All ID stitching of the child table

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

MySQL Common query (i)