rollupÔÚsqlÖеÄÓ÷¨
rollup ÊÇ sql ÖеľۺϺ¯Êý£¬ÓÃÓÚ¶ÔÌõÀí½á¹¹ÖеÄÊý¾Ý¾ÙÐж༶·Ö×éºÍ»ã×Ü¡£ËüµÄÓ﷨Ϊ£ºrollup(expression)¡£Í¨¹ý¶ÔÊý¾Ý°´²î±ðÁ£¶È¾ÙÐоۺϣ¬rollup º¯Êý¿ÉÒÔÇáËɽ¨Éè¶à¼¶¾ÛºÏ£¬Ìá¸ßÅÌÎÊÐÔÄÜ£¬²¢ÔÊÐíÓû§ÔÚ²î±ðÁ£¶ÈÉÏ̽Ë÷Êý¾Ý¡£
rollup ÔÚ SQL ÖеÄÓ÷¨
ʲôÊÇ rollup£¿
rollup ÊÇ SQL ÖеľۺϺ¯Êý£¬ÓÃÓÚÔÚÌõÀí½á¹¹ÖзÖ×éºÍ»ã×ÜÊý¾Ý¡£ËüÔÊÐíÓû§¶ÔÊý¾Ý¾ÙÐж༶¾ÛºÏ£¬´Ó×îÏêϸµÄ¼¶±ðµ½×î¹éÄÉ×ۺϵļ¶±ð¡£
ÔõÑùʹÓà rollup£¿
rollup º¯ÊýµÄÓï·¨ÈçÏ£º
rollup(expression)
µÇ¼ºó¸´ÖÆ
ÆäÖУº
expression£ºÒª·Ö×éºÍ¾ÛºÏµÄ±í´ïʽ£¬¿ÉÒÔÊÇÁÐÃû¡¢¾ÛºÏº¯Êý»òÆäËûÅÌËã¡£
rollup º¯ÊýµÄÓ÷¨Ê¾Àý£º
ʾÀý 1£º°´”region”ºÍ”product”·Ö×é»ã×ÜÏúÊÛ¶î
SELECT region, product, SUM(sales) FROM sales_table GROUP BY ROLLUP(region, product);
µÇ¼ºó¸´ÖÆ
´ËÅÌÎʽ«ÌìÉúÒÔÏÂÊä³ö£º
region | product | sum(sales) |
---|---|---|
Central | Product A | 1000 |
Central | Product B | 1500 |
Central | Total | 2500 |
East | Product A | 500 |
East | Product B | 750 |
East | Total | 1250 |
West | Product A | 700 |
West | Product B | 900 |
West | Total | 1600 |
Grand Total | 5350 |
ʾÀý 2£º×¼Ê±¼äÌõÀí½á¹¹·Ö×é»ã×ܶ©µ¥ÊýÄ¿
SELECT year, quarter, month, COUNT(order_id) FROM orders_table GROUP BY ROLLUP(year, quarter, month);
µÇ¼ºó¸´ÖÆ
´ËÅÌÎʽ«ÌìÉúÒÔÏÂÊä³ö£º
year | quarter | month | count(order_id) |
---|---|---|---|
2021 | 1 | 1 | 100 |
2021 | 1 | 2 | 150 |
2021 | 1 | Total | 250 |
2021 | 2 | 3 | 120 |
2021 | 2 | 4 | 130 |
2021 | 2 | Total | 250 |
2022 | 1 | 1 | 90 |
2022 | 1 | Total | 90 |
Grand Total | 630 |
rollup µÄÓŵ㣺
ÇáËɽ¨Éè¶à¼¶¾ÛºÏ¡£
ÔÊÐíÓû§ÔÚ²î±ðÁ£¶ÈÉÏ̽Ë÷Êý¾Ý¡£
Ìá¸ßÅÌÎÊÐÔÄÜ£¬ÓÉÓÚ¾ÛºÏÊÇÌáÇ°ÅÌËãµÄ¡£
ÒÔÉϾÍÊÇrollupÔÚsqlÖеÄÓ÷¨µÄÏêϸÄÚÈÝ£¬¸ü¶àÇë¹Ø×¢±¾ÍøÄÚÆäËüÏà¹ØÎÄÕ£¡