[TOC]
1 where 语句中实现 case when
- case
WHERE
pw='correct'
AND CASE WHEN id<800 THEN success=1 ELSE TRUE END
AND YEAR(timestamp)=2011
goals：return success=1 (which can be TRUE or FALSE) in case id < 800, or always return TRUE otherwise.
- solution
where
pw = 'correct'
and (id > 800 or success = 1)
and year(timestamp)=2011
2 order by before group by
- case
SELECT wp_posts.* FROM wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author
ORDER BY wp_posts.post_date DESC
goals：The example query produces unusable results as its not always the latest post that is returned.
- solution
subquery：
SELECT wp_posts.* FROM
(
SELECT *
FROM wp_posts
ORDER BY wp_posts.post_date DESC
) AS wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author
join（the best solution!）：
select * from `wp_posts`
inner join
(
select max(post_date) as `max_post_date`, post_author
from `wp_posts`
where wp_posts.post_status='publish'
AND wp_posts.post_type='post'
group by wp_post.post_author
) as p
on wp_posts.post_author = p.post_author
and wp_posts.post_date = p.max_post_date
where wp_posts.post_status='publish'
AND wp_posts.post_type='post'
order by wp_posts.post_date desc
3 COALESCE() 函数
此函数返回参数中第一个不为 null 参数的值
- case
goals：取出系统分、专家评分，若专家分不为 null 则以专家评分为准
- solution
coalesce(sme_score, sys_score, 0)
4 load 命令
load 在大批量的导入和导出数据时效率非常高
LOAD DATA LOCAL INFILE 'C:/Users/hkf/Desktop/task_ningxia.txt' REPLACE INTO TABLE `task_ningxia` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
