[TOC]
1 where 語(yǔ)句中實(shí)現(xiàn) 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() 函數(shù)
此函數(shù)返回參數(shù)中第一個(gè)不為 null 參數(shù)的值
- case
goals：取出系統(tǒng)分绊汹、專家評(píng)分，若專家分不為 null 則以專家評(píng)分為準(zhǔn)
- solution
coalesce(sme_score, sys_score, 0)
4 load 命令
load 在大批量的導(dǎo)入和導(dǎo)出數(shù)據(jù)時(shí)效率非常高
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;
