mysql join

http://www.cnblogs.com/renpei/p/5485730.html
http://database.51cto.com/art/201010/229082.htm
mysql> select *from lee;
+-------+----------+
| name  | birthday |
+-------+----------+
| test | 1984     |
| siyu  | 1988     |
+-------+----------+
2 rows in set (0.00 sec)

mysql> select name, birthday, case when birthday>'1984' then 'yong' when birthday<'1985' then 'old' else 'ok' end YORN from lee;
+-------+----------+------+
| name  | birthday | YORN |
+-------+----------+------+
| test | 1984     | old  |
| siyu  | 1988     | yong |
+-------+----------+------+
2 rows in set (0.00 sec)

SELECT            
    case                     -------------如果
    when sex='1' then '男' -------------sex='1',则返回值'男'
    when sex='2' then '女' -------------sex='2',则返回值'女'  
    else 
0                      -------------其他的返回'其他’
    end                        -------------结束
from   
sys_user                       --------整体理解: 在sys_user表中如果sex='1',则返回值'男'如果sex='2',则返回值'女' 否则返回'其他’
http://w3resource.com/mysql/advance-query-in-mysql/join-using-group-by.php
SELECT bk1.book_name,bk1.pub_lang,bk1.book_price   
FROM  book_mast bk1  
JOIN(SELECT pub_lang,MAX(book_price) AS  price            
FROM book_mast             
GROUP BY pub_lang) AS bk2            
ON bk1.pub_lang=bk2.pub_lang AND bk1.book_price=bk2.price;
mysql join group
http://w3resource.com/mysql/advance-query-in-mysql/mysql-cross-join.php
http://kccoder.com/mysql/join-group-by-performance/
SELECT 20170613 as ymd, b.uid,b.ip FROM (SELECT uid FROM `stat_online_uid_qid_temp` ORDER BY uid ASC LIMIT 0,10) a LEFT JOIN `stat_online_uid_qid_temp` b ON a.uid=b.uid

mysql> select *from Store_Information;
+-------------+-------+-------------+
| Store_Name  | Sales | Txn_Date    |
+-------------+-------+-------------+
| Los Angeles | 1500  | Jan-05-1999 |
| San Diego   | 250   | Jan-07-1999 |
| Los Angeles | 300   | Jan-08-1999 |
| Boston      | 700   | Jan-08-1999 |
+-------------+-------+-------------+
4 rows in set (0.00 sec)

mysql> select count(distinct Store_Name) as online from Store_Information;
+--------+
| online |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT count(*) as `online` from (SELECT Store_Name FROM `Store_Information` group by Store_Name) as a;
+--------+
| online |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> 

http://kccoder.com/mysql/join-group-by-performance/

http://www.w3school.com.cn/sql/sql_join.asp

http://www.1keydata.com/sql/inner-join.html

http://www.cnblogs.com/pcjim/articles/799302.html

http://www.1keydata.com/sql/sqljoins.html

http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Updateleftjoins.htm

    A+
发布日期:2017年06月15日  所属分类:未分类

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: