Home Database Mysql Tutorial db2的rollup和cube函数

db2的rollup和cube函数

Jun 07, 2016 pm 04:02 PM
db2 rollup function Effect yes

rollup的效果就是对group by后面的一个分组列名进行统计。 bankid为第一列 SELECT CASE WHEN grouping (branchid) = 0 THEN branchid ELSE 小计 END as branchid, CASE WHEN grouping (bankid) = 0 THEN bankid ELSE 总计 END as bankid, SUM (balance)FROM

rollup的效果就是对group by后面的一个分组列名进行统计。

bankid为第一列

SELECT
    CASE
        WHEN grouping (branchid) = 0
        THEN branchid
        ELSE '小计'
    END as branchid,
    CASE
        WHEN grouping (bankid) = 0
        THEN bankid
        ELSE '总计'
    END as bankid,
    SUM (balance)
FROM
    ebanksumm
WHERE
    workdate = '20140320'
GROUP BY
 bankid ,
        branchid
  with  rollup
Copy after login

branchid为第一列

SELECT
    CASE
        WHEN grouping (branchid) = 0
        THEN branchid
        ELSE '小计'
    END AS branchid ,
    CASE
        WHEN grouping (bankid) = 0
        THEN bankid
        ELSE '总计'
    END AS bankid ,
    SUM (balance)
FROM
    ebanksumm
WHERE
    workdate = '20140321'
GROUP BY
    branchid ,
    bankid WITH rollup
Copy after login

cube函数则是对groupby后面的所有列依次进行统计。和rollup的区别就在于要统计的列。rollup只统计一列,cube统计所有列

SELECT
    CASE
        WHEN grouping (branchid) = 0
        THEN branchid
        ELSE '小计'
    END as branchid,
    CASE
        WHEN grouping (bankid) = 0
        THEN bankid
        ELSE '总计'
    END as bankid,
    SUM (balance)
FROM
    ebanksumm
WHERE
    workdate = '20140321'
GROUP BY
 bankid ,
        branchid
  with  Cube
Copy after login
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Tips for dynamically creating new functions in golang functions Tips for dynamically creating new functions in golang functions Apr 25, 2024 pm 02:39 PM

Go language provides two dynamic function creation technologies: closure and reflection. closures allow access to variables within the closure scope, and reflection can create new functions using the FuncOf function. These technologies are useful in customizing HTTP routers, implementing highly customizable systems, and building pluggable components.

Considerations for parameter order in C++ function naming Considerations for parameter order in C++ function naming Apr 24, 2024 pm 04:21 PM

In C++ function naming, it is crucial to consider parameter order to improve readability, reduce errors, and facilitate refactoring. Common parameter order conventions include: action-object, object-action, semantic meaning, and standard library compliance. The optimal order depends on the purpose of the function, parameter types, potential confusion, and language conventions.

Easily understand 4K HD images! This large multi-modal model automatically analyzes the content of web posters, making it very convenient for workers. Easily understand 4K HD images! This large multi-modal model automatically analyzes the content of web posters, making it very convenient for workers. Apr 23, 2024 am 08:04 AM

A large model that can automatically analyze the content of PDFs, web pages, posters, and Excel charts is not too convenient for workers. The InternLM-XComposer2-4KHD (abbreviated as IXC2-4KHD) model proposed by Shanghai AILab, the Chinese University of Hong Kong and other research institutions makes this a reality. Compared with other multi-modal large models that have a resolution limit of no more than 1500x1500, this work increases the maximum input image of multi-modal large models to more than 4K (3840x1600) resolution, and supports any aspect ratio and 336 pixels to 4K Dynamic resolution changes. Three days after its release, the model topped the HuggingFace visual question answering model popularity list. Easy to handle

How to write efficient and maintainable functions in Java? How to write efficient and maintainable functions in Java? Apr 24, 2024 am 11:33 AM

The key to writing efficient and maintainable Java functions is: keep it simple. Use meaningful naming. Handle special situations. Use appropriate visibility.

Complete collection of excel function formulas Complete collection of excel function formulas May 07, 2024 pm 12:04 PM

1. The SUM function is used to sum the numbers in a column or a group of cells, for example: =SUM(A1:J10). 2. The AVERAGE function is used to calculate the average of the numbers in a column or a group of cells, for example: =AVERAGE(A1:A10). 3. COUNT function, used to count the number of numbers or text in a column or a group of cells, for example: =COUNT(A1:A10) 4. IF function, used to make logical judgments based on specified conditions and return the corresponding result.

The screen is good for playing games. Brief analysis of iQOO Neo9S Pro+ screen The screen is good for playing games. Brief analysis of iQOO Neo9S Pro+ screen Jul 19, 2024 pm 03:53 PM

In today's smartphone market, screen quality has become one of the key indicators to measure the overall performance of a mobile phone. iQOO's Neo series has always been committed to providing users with excellent gaming experience and visual enjoyment. The latest product iQOO Neo9SPro+ uses a "Three Good Eye Protection Gaming Screen". Next, let's take a look at the quality of this screen. How brilliant. iQOO Neo9S Pro+ is equipped with a 1.5 KOLED e-sports direct screen, which supports flagship LTPO adaptive refresh rate from 1Hz to 144Hz, which means that it can achieve ultra-low power standby state when displaying static content, and it can also be intelligent during gaming. Switch to dynamic high from 90Hz to 144Hz

OPPO Find X7 is a masterpiece! Capture your every moment with images OPPO Find X7 is a masterpiece! Capture your every moment with images Aug 07, 2024 pm 07:19 PM

In this fast-paced era, OPPO Find X7 can use its imaging power to let us savor every beautiful moment in life. Whether it's magnificent mountains, rivers, lakes, or seas, warm family gatherings, or encounters and surprises on the street, it can help you record them with "unparalleled" picture quality. From the outside, the camera Deco design of Find It looks very recognizable and has a high-end feel. The inside is also unique, starting with the basic hardware configuration. FindX7 maintains the previous

What is the difference between custom PHP functions and predefined functions? What is the difference between custom PHP functions and predefined functions? Apr 22, 2024 pm 02:21 PM

The difference between custom PHP functions and predefined functions is: Scope: Custom functions are limited to the scope of their definition, while predefined functions are accessible throughout the script. How to define: Custom functions are defined using the function keyword, while predefined functions are defined by the PHP kernel. Parameter passing: Custom functions receive parameters, while predefined functions may not require parameters. Extensibility: Custom functions can be created as needed, while predefined functions are built-in and cannot be modified.

See all articles