SQL Server
Concatenate rows into a comma-separated list,we can do it like this.
[tsql]
SELECT DISTINCT
usr.id, usr.name,
(SELECT STUFF((SELECT ‘,’+title FROM hobby_main AS hm WHERE hm.uid=usr.id FOR XML PATH(”)),1,1,”)) AS hobbies
FROM user AS usr
WHERE name = ‘Pengcheng’
[/tsql]
MySQL
I help my little brother to rank the running data in CampusRun app,there is a release date for this app.It calculates total distance from the release date when user’s registration date before the app release date,then calculates total distance from user’s registration date.so i have to use IF statement inside WHERE clause in SQL like below.
[mysql]
SELECT b.id, b.cnname, SUM(distance)/1000 AS total_distance
FROM `xm__run_infos_by_day` AS a
INNER JOIN `xm__member` AS b
ON a.ydq_openid = b.ydq_openid
WHERE IF(b.ydq_join_time < '1467450000', a.time >= ‘1467450000’, a.time >= b.ydq_join_time)
GROUP BY a.ydq_openid, b.cnname, b.id
ORDER BY SUM(distance)/1000 DESC
[/mysql]