在SQL的结果底部添加sum [英] add sum at the bottom of the result in SQL

问题描述

hi


i有4张桌子



## table 1''员工''

 ID |姓名
 ---- | ---- 
 01 | A1 
 02 | B1 
 03 | C1 
 04 | D1 
 

## table 2''位置''

 ID |位置
 ---- | ---------- 
 01 | L1 
 02 | L1 
 03 | L3 
 04 | L2 
 

## table 3''部门''

<前lang =text> ID |部门
---- | ------------
01 | D1
02 | D1
03 | D2
04 | D3

## table 4''薪水''

<前lang =text> ID |薪水
---- | --------
01 | 1000
02 | 2000
03 | 1500
04 | 2000

输出应该是



< pre lang =text>位置|部门|薪水
---------- | ---------- | -------
L1 | D1 | 1000
L1 | D2 | 2000
---------- | ---------- | ------
|薪水| 3000
---------- | ---------- | -------
L2 | D3 | 1500
L2 | D5 | 2000
---------- | ---------- | ------
|薪水| 3500
---------- | -------- | ------
|总薪资| 6500

< br $>




必须用sql创建它



是否有可能在sql中获取以下格式

位置|部门|工资
 ---------- |  - --------- | ------- 
 L1 | D1 | 1000 
 | D2 | 2000 
 ---------- | ---------- | ------ 
 |薪水| 3000 
 ---------- | ---------- | ------- 
 L2 | D3 | 1500 
 | D5 | 2000 
 ---------- | ---------- | ------ 
 |薪水| 3500 
 ---------- | -------- | ------ 
 |总薪资| 6500 

谢谢

问候

varun vimal

解决方案

假设数据中的错误只是问题中的拼写错误，分组集 [ ^ ]会做你想做的事。

  WITH  cteData  As  
（
  SELECT  
 L.Location，
 D.Department，
 S.Salary 
  FROM  
位置 As  L 
  INNER   JOIN 部门作为 D 
  ON  D.ID = L.ID 
  INNER   JOIN 薪资为 S 
  ON  S.ID = L.ID 
）
  SELECT  
位置， 
  CASE  
  WHEN 位置是  Null  那么 ' < span class =code-string> Total Salary' 
  WHEN 部门   Null  那么 ' 薪水' 
  ELSE 出发
  END  作为部门，
总和（薪水） As 薪资
  FROM  
 cteData 
  GROUP   BY  GROUPING SETS 
（
（位置，部门），
（位置），
（） 
）; 

输出：

位置部门工资
 L1 D1 1000 
 L1 D2 2000 
 L1工资3000 
 L2 D3 1500 
 L2 D4 2000 
 L2工资3500 
 NULL总薪资6500 

需要在UI中进行任何进一步的格式化。

hi
i have 4 tables

## table 1 ''Employee''

 ID | Name
----|----
 01 | A1
 02 | B1
 03 | C1
 04 | D1

## table 2 ''Location''

 ID | Location
----|----------
 01 | L1
 02 | L1
 03 | L3
 04 | L2

## table 3 ''Department''

 ID | Department
----|------------
 01 | D1
 02 | D1
 03 | D2
 04 | D3

## table 4 ''Salary''

 ID | Salary
----|--------
 01 | 1000
 02 | 2000
 03 | 1500
 04 | 2000

The output should come as

Location  |Department|Salary
----------|----------|-------
   L1     |   D1     |1000
   L1     |   D2     |2000
----------|----------|------
          |  Salary  |3000
----------|----------|-------
   L2     |   D3     |1500
   L2     |   D5     |2000
----------|----------|------
          |  Salary  |3500
----------|--------  |------
        |Total Salary|6500

have to create it in sql

and is it possible to get following format in sql

Location  |Department|Salary
----------|----------|-------
   L1     |   D1     |1000
          |   D2     |2000
----------|----------|------
          |  Salary  |3000
----------|----------|-------
   L2     |   D3     |1500
          |   D5     |2000
----------|----------|------
          |  Salary  |3500
----------|--------  |------
        |Total Salary|6500

Thank you
regards
varun vimal

解决方案

Assuming the mistakes in your data are just typos in the question, Grouping Sets[^] will do what you want.

WITH cteData As
(
    SELECT
        L.Location,
        D.Department,
        S.Salary
    FROM
        Location As L
        INNER JOIN Department As D
        ON D.ID = L.ID
        INNER JOIN Salary As S
        ON S.ID = L.ID
)
SELECT
    Location,
    CASE
        WHEN Location Is Null THEN 'Total Salary'
        WHEN Department Is Null THEN 'Salary'
        ELSE Department
    END As Department,
    Sum(Salary) As Salary
FROM
    cteData
GROUP BY GROUPING SETS
(
    (Location, Department),
    (Location),
    ()
);

Output:

Location    Department    Salary
L1          D1            1000
L1          D2            2000
L1          Salary        3000
L2          D3            1500
L2          D4            2000
L2          Salary        3500
NULL        Total Salary  6500

Any further formatting needs to be done in the UI.

这篇关于在SQL的结果底部添加sum的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！