åè¨
åå 天å¨ææ¯è®ºå论åä¸åç°ä¸ä¸ªæ±å©å¸ï¼å¤§ä½ææå°±æ¯è¦æä¸ä¸ªè¡¨ä¸çæ°æ®ææ¡ä»¶åæ两类ï¼æ¯ä¸ç±»æåºæ¹å¼ä¸åï¼ç¶åæ´ä½ä½ä¸ºæ¥è¯¢çç»æéï¼ä¹ä¸çè¿é®é¢ä¸æ¯å¾é¾ï¼å¾å¤äººç»åºççæ¡æ¯åå«æ¥è¯¢æåºåå unionå并å°ä¸èµ·ï¼ä½æ¯åæ¥æ¥¼ä¸»æç¡®æåºä¸æ³ä½¿ç¨ union æä½ï¼è¿æ¶æä¸ä½é«äººå·§ç¨
case when
è¯å¥è§£å³äºé®é¢ï¼å ¶å®è¿æ¯æ第ä¸æ¬¡æ¥è§¦
case when
è¯å¥ï¼äºæ¯æ¥è¯¢äºä¸ä¸å ·ä½ç¨æ³ï¼å¨æ¤å个å°ç»ï¼æ¹ä¾¿æ¥åæ¥è¯¢ä½¿ç¨ã
å建示ä¾è¡¨æ ¼
æ°æ®åºè¡¨æ ¼ç»æå¾ç®åï¼é©¬ä¸è¦ææ«äºï¼å°±ä»¥å¦ä¹ æ绩为æ°æ®æ¥å»ºç«ä¸å¼ æ°æ®è¡¨ï¼è¡¨ä¸å å«å¯ä¸IDãå¦å·ãå§åãæ§å«ãåæ°çåï¼å ¶ä¸æ§å«è¿ä¸åç¨æ´æ°ä»£è¡¨ï¼0表示ç·ï¼1表示女ï¼å»ºç«è¡¨æ ¼çsqlè¯å¥å¦ä¸ï¼
CREATE TABLE `grade` (
`id` int() NOT NULL AUTO_INCREMENT,
`number` int() NOT NULL DEFAULT '0',
`name` varbinary() NOT NULL DEFAULT '',
`sex` int() NOT NULL DEFAULT '0',
`score` int() NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `find_index` (`number`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=DYNAMIC;
æå ¥æµè¯æ°æ®
è¡¨æ ¼å»ºç«å®æå¯ä»¥ä½¿ç¨å¯è§åå·¥å ·æè insertè¯å¥æå ¥æµè¯æ°æ®ï¼æå ¥æµè¯æ°æ®åæ¥è¯¢ç»æå¦ä¸ï¼
mysql> select number,name,sex,score from grade;
+----------+----------+-----+-------+
| number | name | sex | score |
+----------+----------+-----+-------+
| 20180001 | xiaoming | 0 | 68 |
| 20180002 | xiaohong | 1 | 98 |
| 20180003 | xiaobing | 0 | 78 |
| 20180004 | xiaoli | 0 | 88 |
| 20180005 | zhangsan | 0 | 32 |
| 20180006 | zhaosi | 0 | 58 |
| 20180007 | marry | 1 | 78 |
| 20180008 | tom | 0 | 100 |
| 20180009 | feifei | 1 | 90 |
| 20180010 | lili | 1 | 92 |
| 20180011 | xiaozhao | 0 | 52 |
| 20180012 | xiaowang | 0 | 62 |
+----------+----------+-----+-------+
12 rows in set ( sec)
è·åå¹³åæ绩
ç主任们åå¨ä¸èµ·åå欢åçäºå°±æ¯æ¯ä¸ä¸èªå·±çå¦çåå«äººççå·®è·ï¼è°è®©ä»ä»¬æ¯ä¸ªäººå¸¦çå¦çé½æ¯ä¸å±ä¸å¦ä¸å±å¢ï¼ï¼ä½ 们æ¯æ带è¿çå¦çä¸æå·®çä¸å±ï¼ï¼ï¼ï¼è¯´å°æ¯æ绩ä¸è¬é½æ¯æ¯è¾å¹¶ååï¼sqlè¯å¥å¯è½ä¼åæä¸é¢è¿æ ·ï¼
mysql> select avg(score) as å¹³åå from grade;
+-----------+
| å¹³åå |
+-----------+
| 74.6667 |
+-----------+
1 row in set ( sec)
æ¯çï¼å¾ç®åå°±è½è·å¾ç级çå¹³ååï¼å¦æè¦åç»å¢ï¼æ¯å¦åå«æ¥ä¸ä¸ç·çå女ççå¹³ååï¼å 为æ们ç¥é表ä¸ç
sex
表示æ§å«ï¼æ以ç´æ¥æç §
sex
åç»å°±å¯ä»¥å®ç°ï¼å¯ä»¥å°è¯å¥ç®ååæè¿æ ·ï¼
mysql> select sex as æ§å«, avg(score) as å¹³åå from grade group by sex;
+--------+-----------+
| æ§å« | å¹³åå |
+--------+-----------+
| 0 | 67.2500 |
| 1 | 89.5000 |
+--------+-----------+
2 rows in set ( sec)
æ¯ä¸æ¯å¾ç®åï¼å¯æ¯æ§å«æ¾ç¤ºæ0å1ç¡®å®ä¸å©äºé 读ï¼ä½æ¯è¡¨ä¸å没æä¿å0ã1ä¸ç·ã女ç对åºå ³ç³»ï¼åºè¯¥æä¹åå¢ï¼è¿å°±è¦ç¨å°æ们ä»å¤©æè¦ç¨å°ç
case when
è¯å¥äºï¼è¯æ³ä¸å ±æ两ç§åæ³ï¼ççå ·ä½ä¾åä½ä¼ä¸ä¸å§ã
case when è¯å¥ç使ç¨
- 第ä¸ç§ç¨æ³ï¼caseåé¢è·ååï¼whenåé¢è·å¯¹åºå¼
è¿ç§ç¨æ³æ£å¥½è§£å³æ们ååæåºçé®é¢ï¼å½sexå¼ä¸º0æ¶å½ååæ¾ç¤ºâç·âï¼å¦åæ¾ç¤ºâ女âï¼sqlåæ³å¦ä¸ï¼CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END
mysql> select (case sex when then 'ç·' else '女' end) as æ§å«, avg(score) as å¹³åå -> from grade group by sex; +--------+-----------+ | æ§å« | å¹³åå | +--------+-----------+ | ç· | 67.2500 | | 女 | 89.5000 | +--------+-----------+ 2 rows in set ( sec)
- 第äºç§ç¨æ³ï¼caseåé¢ç©ºç½ï¼whenåé¢è·çå¤ææ¡ä»¶
é对äºè¿ç§åæ³ï¼æ们èèè¿æ ·ä¸ç§éæ±ï¼å¦çæ绩æ¯æè¯åçï¼å¤§äºçäº90åçå¦çæ¯Aï¼å°äº90å大äºçäº60åçå¦çæ¯Bï¼ å ¶ä½çå¦çæ¯Cï¼ç°å¨è¦æ¥è¯¢è¯å为AãBãCçå¦çæ绩çå¹³åååå«æ¯å¤å°ï¼å 为æ绩è¯å并ä¸æ¯åç¬çä¸åï¼æ以ä¸è½ç®åç 使ç¨CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END
æ¥åç»å®ç°äºï¼ä½æ¯å¯ä»¥å©ç¨group by
è¯å¥å®ç°ï¼åèµ·æ¥ä¹å¾ç®åï¼ççä¸é¢çsqlè¯å¥å°±ç¥éäºï¼case when
mysql> select (case when score >= then 'A' when score < then 'C' else 'B' end) as ç级, -> avg(score) as å¹³åå from grade group by ç级; +--------+-----------+ | ç级 | å¹³åå | +--------+-----------+ | A | 95.0000 | | B | 74.8000 | | C | 47.3333 | +--------+-----------+ 3 rows in set ( sec)
æ»ç»
-
è¯å¥å ±æ两ç§åæ³ï¼ä½¿ç¨æ¶è¦åºå«ä¸¤ç§ç¨æ³çå·®å¼ãcase when
- 使ç¨
è¯å¥å¯ä»¥å®ç°ä¿®æ¹æ°å¼ç对åºå ³ç³»ï¼è¿å¯ä»¥æç §å¤æçæ¡ä»¶è¿è¡åç»ãcase when
- å
³äº
è¯å¥ç详ç»ç¨æ³ï¼æå ´è¶£çåå¦å¯ä»¥åèä¸ä¸å®æ¹ææ¡£ï¼13.6.5.1 CASE Syntaxcase when