sqlÖÐÅÌÎÊÖظ´Êý¾ÝµÄÏÂÁî
sql ÖÐÅÌÎÊÖظ´Êý¾ÝµÄÒªÁìÊÇ£ºÊ¹Óà group by ×Ó¾ä¶ÔËùÐèö¾ÙÐзÖ×顣ʹÓà having ×Ó¾äÖ¸¶¨Öظ´Êý¾ÝµÄ¹ýÂËÌõ¼þ¡£
SQL ÅÌÎÊÖظ´Êý¾ÝµÄÏÂÁî
ÔõÑùÅÌÎÊÖظ´Êý¾Ý
ÔÚ SQL ÖУ¬ÅÌÎÊÖظ´Êý¾Ý¿ÉÒÔͨ¹ýʹÓà GROUP BY ºÍ HAVING ×Ó¾äÀ´ÊµÏÖ¡£
°ì·¨
ʹÓà GROUP BY ×Ó¾ä¶ÔËùÐèö¾ÙÐзÖ×é¡£
ʹÓà HAVING ×Ó¾äÖ¸¶¨Öظ´Êý¾ÝµÄ¹ýÂËÌõ¼þ¡£
Óï·¨
SELECT column_name(s) FROM table_name GROUP BY column_name(s) HAVING COUNT(*) > 1;
µÇ¼ºó¸´ÖÆ
ʾÀý
¼ÙÉèÓÐÒ»¸öÃûΪ students µÄ±í£¬°üÀ¨ÈçÏÂÊý¾Ý£º
student_id | name |
---|---|
1 | John Doe |
2 | Jane Smith |
3 | John Doe |
4 | Mary Johnson |
ÒªÅÌÎÊ students ±íÖÐÖظ´µÄ name ×ֶΣ¬¿ÉÒÔʹÓÃÒÔÏÂÅÌÎÊ£º
SELECT name FROM students GROUP BY name HAVING COUNT(*) > 1;
µÇ¼ºó¸´ÖÆ
Êä³ö
John Doe
µÇ¼ºó¸´ÖÆ
ÆäËûʾÀý
ÅÌÎÊ orders ±íÖÐÖظ´µÄ product_id ×ֶΣº
SELECT product_id FROM orders GROUP BY product_id HAVING COUNT(*) > 1;
µÇ¼ºó¸´ÖÆ
ÅÌÎÊ employees ±íÖÐÖظ´µÄ email ×ֶβ¢ÏÔʾÖظ´´ÎÊý£º
SELECT email, COUNT(*) AS count FROM employees GROUP BY email HAVING COUNT(*) > 1;
µÇ¼ºó¸´ÖÆ
×¢ÖØ
COUNT(*) º¯ÊýÅÌËãÿ¸ö×éÖеÄÐÐÊý¡£
HAVING ×Ó¾äÖеÄÌõ¼þ¿ÉÒÔ»ùÓھۺϺ¯Êý£¨Èç COUNT¡¢SUM ºÍ AVG£©¡£
ÒÔÉϾÍÊÇsqlÖÐÅÌÎÊÖظ´Êý¾ÝµÄÏÂÁîµÄÏêϸÄÚÈÝ£¬¸ü¶àÇë¹Ø×¢±¾ÍøÄÚÆäËüÏà¹ØÎÄÕ£¡