[B! sql] xxxkurosukexxxã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

xxxkurosukexxx id:xxxkurosukexxx

sqlã«é–¢ã™ã‚‹xxxkurosukexxxã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (6)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

GROUP_CONCATé–¢æ•°ã®ä¾¿åˆ©ã•ã¯ç•°å¸¸ - é–‹ç™ºã®é¢¨æ™¯ã€€ã€œKKZã®SEæ—¥è¨˜ã€œ
http://dev.mysql.com/doc/refman/5.1/ja/group-by-functions.html GROUP_CONCAT(expr) ã“ã®é–¢æ•°ã¯ã€ã‚°ãƒ«ãƒ¼ãƒ—ã‹ã‚‰ã®é€£çµã•ã‚ŒãŸéž NULL å€¤ã‚’ä¼´ã†ã‚¹ãƒˆãƒªãƒ³ã‚°çµæžœã‚’æˆ»ã—ã¾ã™ã€‚éžNULL å€¤ãŒãªã„å ´åˆã¯ NULL ã‚’æˆ»ã—ã¾ã™ã€‚MySQL ã§ã¯ã€å¼ã®ã‚³ãƒ³ãƒ“ãƒãƒ¼ã‚·ãƒ§ãƒ³ã®é€£çµã•ã‚ŒãŸå€¤ã‚’å¾—ã‚‹ã“ã¨ãŒã§ãã¾ã™ã€‚DISTINCT ã‚’ä½¿ç”¨ã™ã‚‹ã“ã¨ã§ã€é‡è¤‡ã—ãŸå€¤ã‚’é™¤ãã“ã¨ãŒå¯èƒ½ã§ã™ã€‚çµæžœã®å€¤ã‚’ã‚½ãƒ¼ãƒˆã—ãŸã„å ´åˆã¯ã€ORDER BY å¥ã‚’ä½¿ç”¨ã—ã¦ãã ã•ã„ã€‚é€†é †ã§ã‚½ãƒ¼ãƒˆã™ã‚‹ã«ã¯ã€DESC ( é™é † ) ã‚ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã‚’ã€ORDER BY å¥ã®ã‚½ãƒ¼ãƒˆã™ã‚‹ã‚«ãƒ©ãƒ ã®åå‰ã«åŠ ãˆã¦ãã ã•ã„ã€‚ãƒ‡ãƒ•ã‚©ãƒ«ãƒˆã§ã¯æ˜‡é †ã«ãªã£ã¦ã„ã¾ã™ã€‚ã“ã‚Œã¯ã€ASC ã‚’ä½¿ã†ã“ã¨ã§æ˜Žç¤ºçš„ã«æŒ‡å®šã™ã‚‹ã“ã¨ãŒã§ãã¾ã™ã€‚SEPARATOR ã®å¾Œã«ã¯ã€çµæžœã®å€¤ã®é–“ã«æŒ¿å…¥ã•ã‚Œã‚‹ã¹ãã‚¹ãƒˆãƒªãƒ³ã‚°å€¤ãŒç¶š
xxxkurosukexxx 2016/05/30
ã‚ã‚‚ã‚ã‚‚ã€‚

sql

mysql
ãƒªãƒ³ã‚¯
ã‚¿ã‚°æ©Ÿèƒ½ã‚’å®Ÿç¾ã™ã‚‹ãŸã‚ã®ä¾¿åˆ©ãªãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹è¨è¨ˆã‚’ï¼“ã¤ç´¹ä»‹ | colori
ANDæ¤œç´¢ ã€ŒCSS+HTML+JavaScriptã€ã§æ¤œç´¢ã™ã‚‹å ´åˆã¯ä»¥ä¸‹ã®ã‚ˆã†ã«ã—ã¾ã™ã€‚ SELECT * FROM `delicious` WHERE tags LIKE "%CSS%" AND tags LIKE "%HTML%" AND tags LIKE "%JavaScript%" ORæ¤œç´¢ ã€ŒCSS|HTML|JavaScriptã€ã§æ¤œç´¢ã™ã‚‹å ´åˆã¯ä»¥ä¸‹ã®ã‚ˆã†ã«ã—ã¾ã™ã€‚ SELECT * FROM `delicious` WHERE tags LIKE "%CSS%" OR tags LIKE "%HTML%" OR tags LIKE "%JavaScript%" å¼•ãç®—æ¤œç´¢ ã€ŒCSS+HTML-JavaScriptã€ã§æ¤œç´¢ã™ã‚‹å ´åˆã¯ä»¥ä¸‹ã®ã‚ˆã†ã«ã—ã¾ã™ã€‚ SELECT * FROM `delicious` WHERE tags LIKE "%CSS%" AND tags LI
xxxkurosukexxx 2015/06/29
sql

ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹
ãƒªãƒ³ã‚¯
CASEå¼ã®ã‚¹ã‚¹ãƒ¡ï¼ˆå¾Œç·¨ï¼‰
ã¯ã˜ã‚ã« CASEå¼ã¯ã€SQL-92ã§æ¨™æº–ã«å–ã‚Šå…¥ã‚Œã‚‰ã‚Œã¾ã—ãŸã€‚æ¯”è¼ƒçš„æ–°ã—ã„é“å…·ã§ã‚ã‚‹ãŸã‚ã‹ã€ä¾¿åˆ©ãªã‚ã‚Šã«ãã®çœŸä¾¡ãŒã‚ã¾ã‚ŠçŸ¥ã‚‰ã‚Œã¦ãŠã‚‰ãšã€åˆ©ç”¨ã•ã‚Œã¦ã„ãªã‹ã£ãŸã‚Šã€CASEå¼ã®ç°¡ç•¥ç‰ˆã§ã‚ã‚‹DECODEï¼ˆOracleï¼‰ã€IFï¼ˆMySQLï¼‰ãªã©ã®é–¢æ•°ã§ä»£ç”¨ã•ã‚Œã¦ã„ãŸã‚Šã—ã¾ã™ã€‚ CASEå¼ã‚’æ´»ç”¨ã™ã‚‹ã¨ã€SQLã§ã§ãã‚‹ã“ã¨ã®å¹…ãŒãã£ã¨åºƒãŒã‚Šã€æ›¸ãæ–¹ã‚‚ã‚¹ãƒžãƒ¼ãƒˆã«ãªã‚Šã¾ã™ã€‚ã—ã‹ã‚‚ã€å®Ÿè£…éžä¾å˜ã®æŠ€è¡“ãªã®ã§ã‚³ãƒ¼ãƒ‰ã®æ±Žç”¨æ€§ã‚‚é«˜ã¾ã‚Šã¾ã™ã€‚ç‰¹ã«DECODEé–¢æ•°ã‚’ä½¿ã£ã¦ã„ã‚‹Oracleãƒ¦ãƒ¼ã‚¶ãƒ¼ã«ã¯ã€ãœã²CASEå¼ã¸ã®ä¹—ã‚Šæ›ãˆã‚’ãŠè–¦ã‚ã—ã¾ã™ã€‚ å‰å›žã€ŽCASEå¼ã®ã‚¹ã‚¹ãƒ¡ï¼ˆå‰ç·¨ï¼‰ã€ã§ã¯ã€CASEå¼ã®æ–‡æ³•ã¨ãã®å¿œç”¨æ–¹æ³•ã«ã¤ã„ã¦è§£èª¬ã—ã¾ã—ãŸã€‚ä»Šå›žã¯å¼•ãç¶šãã€CASEå¼ã®ã‚ˆã‚Šé«˜åº¦ãªå¿œç”¨æ–¹æ³•ã«ã¤ã„ã¦è§£èª¬ã—ã¾ã™ã€‚ å¿…è¦ãªç’°å¢ƒ æ¬¡ã®ã„ãšã‚Œã‹ã®ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ï¼ˆMS Accessã¯å¯¾è±¡å¤–ï¼‰ã€‚ Oracleï¼ˆ9iä»¥é™ï¼‰ SQL Server
xxxkurosukexxx 2014/11/26
IFã¯æ¨™æº–ã˜ã‚ƒãªã„ã‹ã‚‰CASEã€‚CASEã¯ELSEã‚’å¿˜ã‚Œã‚‹ã¨ELSE NULLã«ãªã‚‹ã‹ã‚‰å±é™ºã€‚è¦šãˆãŸï¼

sql
ãƒªãƒ³ã‚¯
SQLã§ã€è¤‡æ•°ãƒ†ãƒ¼ãƒ–ãƒ«ã‹ã‚‰ä»–æ–¹ã«ç„¡ã„(å˜åœ¨ã—ãªã„)ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’æŠ½å‡ºã™ã‚‹
ãŸã¾ã«å¿…è¦ã«é§†ã‚‰ã‚Œã‚‹ã®ãŒã€ï¼’ã¤ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’æ¯”è¼ƒã—ã¦ã€ï¼‘ã¤ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã«ã¯å˜åœ¨ã™ã‚‹ãŒã€ã‚‚ã†ä¸€æ–¹ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã«ã¯ãªã„ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’æŠ½å‡ºã™ã‚‹ã¨ã„ã†ã‚‚ã®ã€‚ ã„ãã¤ã‹æ–¹æ³•ãŒã‚ã‚Šã¾ã™ãŒã€ä»Šå›žã¯ä¸»ãªï¼’ã¤ã‚’ã”ç´¹ä»‹ã€‚ç‰¹ã«â‘¡ã®æ–¹æ³•ãŒæ±Žç”¨çš„ã§ã€åŽŸç†ã‚’è¦šãˆã¦ãŠãã¨ã€æ§˜ã€…ãªå ´é¢ã§å¿œç”¨ãŒããã¾ã™ã€‚ â‘ å·®é›†åˆæ¼”ç®—ã‚’ä½¿ã†ãƒ»ãƒ»ãƒ»ç°¡å˜ã§ã™ãŒã€æ¯”è¼ƒã™ã‚‹ãƒ†ãƒ¼ãƒ–ãƒ«ã®åˆ—æ•°ã€åž‹ã‚’åˆã‚ã›ã‚‹å¿…è¦ã‚ã‚Šã€‚ â‘¡SQLã®ã¿ã§æŠ½å‡ºãƒ»ãƒ»ãƒ»æ±Žç”¨çš„ã«ä½¿ãˆã¾ã™ã€‚ä¸€åº¦åŽŸç†ã‚’è¦šãˆã‚‹ã¨å¿œç”¨ãŒããã¾ã™ã€‚ ã¯ã˜ã‚ã«ãƒ‡ãƒ¼ã‚¿ã‚’ç”¨æ„ ã§ã¯ã€ã•ã£ããã‚„ã£ã¦ã¿ã¾ã™ã€‚æ¯”è¼ƒã™ã‚‹ãƒ†ãƒ¼ãƒ–ãƒ«ã¯ã€ã“ã®è¨˜äº‹ã§ç”¨æ„ã—ãŸã€å£²ä¸Šãƒ†ãƒ¼ãƒ–ãƒ«ã€‘ã¨ã€é¡§å®¢ãƒ†ãƒ¼ãƒ–ãƒ«ã€‘ã‚’ä½¿ã†ã“ã¨ã«ã—ã¾ã—ã‚‡ã†ã€‚ å„ã€…ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã«ã¯ä»¥ä¸‹ã®ã‚ˆã†ã«ãƒ‡ãƒ¼ã‚¿ãŒå…¥ã£ã¦ã„ã‚‹ã“ã¨ã«ã—ã¾ã™ã€‚ â‘ å·®é›†åˆæ¼”ç®—ã‚’ä½¿ã† å„DBã«ã¯æ¼”ç®—ã®ãŸã‚ã®çµ„ã¿è¾¼ã¿é–¢æ•°ãŒã‚ã‚Šã€å·®åˆ†ã‚’æ¼”ç®—ã™ã‚‹ã‚‚ã®ã‚‚ã‚ã‚Šã¾ã™ã€‚ PostgreSQLã€Oracleã€DB2ãƒ»ãƒ»ãƒ»minus MySQ
xxxkurosukexxx 2014/11/26
ãªã‚‹ã»ã©ãƒ¼ã€‚outer joinã€‚

sql
ãƒªãƒ³ã‚¯
ã‚ãƒ¼ãŒåŒã˜ã‚’è¤‡æ•°è¡Œã‚’ï¼‘è¡Œã«ã¾ã¨ã‚ã‚‹ã«ã¯ï¼Ÿ - OKWAVE
ã“ã‚“ã°ã‚“ã¯ã€‚è³ªå•ã§ã™ï¼ ä¾‹ãˆã°ã€ ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’çµåˆã—ã¦ã€ä»¥ä¸‹ã®æ§˜ãªçµæžœãŒè¿”ã£ã¦ãã‚‹ã¨ã—ã¦ã€ è£½å“ã‚³ãƒ¼ãƒ‰ã€€ã€€å–æ‰±åº— ------------------------- 001ã€€ã€€ã€€ã€€ã€€ã€€ã‚ã‚ã‚ 001ã€€ã€€ã€€ã€€ã€€ã€€ã„ã„ã„ 001ã€€ã€€ã€€ã€€ã€€ã€€ã†ã†ã† ã“ã‚Œã‚’ è£½å“ã‚³ãƒ¼ãƒ‰ã€€ã€€åº—ï¼‘ã€€ã€€ã€€ã€€åº—ï¼’ã€€ã€€ã€€ã€€ã€€åº—ï¼“ ---------------------------------------------- 001ã€€ã€€ã€€ã€€ã€€ã€€ã‚ã‚ã‚ã€€ã€€ã€€ã€€ã„ã„ã„ã€€ã€€ã€€ã†ã†ã† ã¨ï¼‘ãƒ¬ã‚³ãƒ¼ãƒ‰ã«ã™ã‚‹ã«ã¯ã©ã†ã—ãŸã‚‰ã€ã„ã„ã§ã—ã‚‡ã†ã‹ï¼Ÿï¼Ÿ å›°ã£ã¦ã„ã¾ã™ã€‚ãŠé¡˜ã„è‡´ã—ã¾ã™ï¼
xxxkurosukexxx 2014/03/06
ã“ã‚Œã ï¼ã©ã†ã„ã†åŽŸç†ã‹ã‚ã‹ã‚‰ãªã„ã‘ã©ã“ã‚Œã®ãƒ™ã‚¹ãƒˆã‚¢ãƒ³ã‚µãƒ¼ã¨concatã¨rtrimã§ã‚„ã‚ŠãŸã‹ã£ãŸã“ã¨è§£æ±ºã—ãŸï¼

sql
ãƒªãƒ³ã‚¯
åˆç´šã€œä¸ç´šè€…ã®ãŸã‚ã®SQLç‰¹è¨“ - å¿˜ã‚Œãªã„ã‚ˆã†ã«ãƒ¡ãƒ¢ã£ã¨ã
ã»ã¼ã»ã¼åˆå¿ƒè€…ã§SQLã®ç·´ç¿’ãŒã—ãŸã„ã£ã¦ã„ã†äººã€çŸ¥è˜ã®å†ç¢ºèªã‚’ã—ãŸã„äººã«ã‚ªã‚¹ã‚¹ãƒ¡ã®ã‚µã‚¤ãƒˆã€‚ SQLZOO ãƒ–ãƒ©ã‚¦ã‚¶ä¸Šã§å‹•ä½œã™ã‚‹ã®ã§ã€ã‚ã‚“ã©ãã•ã„ç’°å¢ƒè¨å®šã‚‚ãªã—ã€‚ ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã‚‚ã€MySQLã€Oracleã€SQL Serverã€Postgreã€Ingresã€DB2ã‹ã‚‰é¸ã¹ã‚‹ã€‚ ç°¡å˜ãªSELECTæ–‡ã‹ã‚‰å§‹ã¾ã£ã¦ã€SUMã‚„COUNTã¿ãŸã„ãªåŸºæœ¬çš„ãªé–¢æ•°ã®å•é¡Œã‚„ã€JOINã‚’ä½¿ã£ãŸã¡ã‚‡ã£ã¨é›£ã—ã‚ã®æ§‹æ–‡ã‚‚ã‚ã‚‹ã€‚ å®Ÿè¡Œç’°å¢ƒä»˜ãã®ãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ã‚‚ã‚ã‚‹ã®ãŒã‚ˆã„ï¼(è¶…ä¾¿åˆ©ï¼)
xxxkurosukexxx 2014/02/26
sql
ãƒªãƒ³ã‚¯
1