SQL查询 - 单个列中的多个不同总和

I want to create an SQL Query that will be saved as a stored procedure, which is then called by an XML script. It must then be in a certain format, and have only one value column.

我想创建一个SQL查询,它将保存为存储过程,然后由XML脚本调用。它必须是某种格式,并且只有一个值列。

I need to display the outcome of three different sums in a single column, in separate rows. I haven't worked out how this is possible, and currently the values are being output to three different 'Value' columns.

我需要在单个列中以单独的行显示三个不同总和的结果。我还没有弄清楚这是怎么可能的,目前这些值正在输出到三个不同的“值”列。

The purpose of the query is to display the size of databases on an SQL server with a certain name.

查询的目的是在具有特定名称的SQL Server上显示数据库的大小。

The query is currently querying an SQL Server, and targeting databases containing "@name" in their name. It is displaying the total size of those databases including their log files, and including offline databases. I had it displaying the total size, but I also want to show the total size of database files and log files separately, in different rows, in the same column.

该查询当前正在查询SQL Server,并且目标是在其名称中包含“@name”的数据库。它显示这些数据库的总大小,包括其日志文件,包括脱机数据库。我让它显示总大小,但我还想在同一列中分别显示不同行中的数据库文件和日志文件的总大小。

Here is my query:

这是我的查询:

declare
    @name NVARCHAR(20)
set
    @name = 'H3'

select
    @name as Channel,
    sum((a.size*8)/1024) Value,
    SUM(CASE WHEN type_desc like 'ROWS' THEN (a.size*8)/1024 ELSE 0 END) Value,
    SUM(CASE WHEN type_desc like 'LOG' THEN (a.size*8)/1024 ELSE 0 END) Value,
    '1' as IsInt,
    'MB' as Unit
from 
    sys.databases b
    left join sys.master_files a
        on b.database_id = a.database_id

where 
    b.name like '%'+@name+'%'

The output currently looks like:

输出目前看起来像:

Channel   Value    Value   Value  IsInt  Unit
--------- -------  ------- ------ ------ ------
H3        140091   134737  5354   1      MB

I want it to display:

我希望它显示:

Channel   Value   IsInt  Unit
--------- ------- ------ ------
H3        140091  1      MB
H3        134737  1      MB
H3        5354    1      MB

I would appreciate any help, as I am really stuck on this one. I am very new to SQL, so the answer may be obvious.

我很感激任何帮助,因为我真的被困在这一个。我是SQL的新手,所以答案可能很明显。

This is in SQL Server 2008.

这是在SQL Server 2008中。

4 个解决方案

#1

Maybe using a UNION ALL and split your query to 3 different queries will solve your problem?

也许使用UNION ALL并将您的查询拆分为3个不同的查询将解决您的问题?

declare @name NVARCHAR(20)
set   @name = 'H3'

select
@name as Channel,
sum((a.size*8)/1024) Value,
'1' as IsInt,
'MB' as Unit
from 
sys.databases b
left join sys.master_files a on b.database_id = a.database_id

where 
b.name like '%'+@name+'%'

UNION ALL

select
@name as Channel,
SUM(CASE WHEN type_desc like 'ROWS' THEN (a.size*8)/1024 ELSE 0 END) Value,
'1' as IsInt,
'MB' as Unit
from 
sys.databases b
left join sys.master_files a on b.database_id = a.database_id

where 
b.name like '%'+@name+'%'

UNION ALL

select
@name as Channel,
SUM(CASE WHEN type_desc like 'LOG' THEN (a.size*8)/1024 ELSE 0 END) Value,
'1' as IsInt,
'MB' as Unit
from 
sys.databases b
left join sys.master_files a on b.database_id = a.database_id

where 
b.name like '%'+@name+'%'

#2

Use CTE to make the result and combine using UNION ALL.

使用CTE生成结果并使用UNION ALL进行组合。

declare @name NVARCHAR(20)
set @name = 'H3'

;with cte_1
as
(select
    @name as Channel,
    sum((a.size*8)/1024) Value1,
    SUM(CASE WHEN type_desc like 'ROWS' THEN (a.size*8)/1024 ELSE 0 END) Value2,
    SUM(CASE WHEN type_desc like 'LOG' THEN (a.size*8)/1024 ELSE 0 END) Value3,
    '1' as IsInt,
    'MB' as Unit
from 
    sys.databases b
    left join sys.master_files a
        on b.database_id = a.database_id
where 
    b.name like '%'+@name+'%')
SELECT Channel,Value1 Value,IsInt,Unit
FROM cte_1
UNION ALL
SELECT Channel,Value2,IsInt,Unit
FROM cte_1
UNION ALL
SELECT Channel,Value3,IsInt,Unit
FROM cte_1

#3

What you need is to unpivot your result. There are different ways to do that. You can use the most common ways: the UNION ALL method, or the UNPIVOT method. However, there is another method that is usually faster than these two, the CROSS APPLY method:

你需要的是取消你的结果。有不同的方法可以做到这一点。您可以使用最常用的方法:UNION ALL方法或UNPIVOT方法。但是,还有另一种方法通常比这两种方法更快,CROSS APPLY方法:

DECLARE @name nvarchar(20)
SET @name = 'H3';

WITH Cte(Channel, TotalValue, RowsValue, LogsValue, IsInt, Unit) AS(
    SELECT
        @name AS Channel,
        SUM((a.size * 8) / 1024) Value,
        SUM(CASE WHEN type_desc LIKE 'ROWS' THEN (a.size * 8) / 1024 ELSE 0 END) AS Value,
        SUM(CASE WHEN type_desc LIKE 'LOG' THEN (a.size * 8) / 1024 ELSE 0 END) AS Value,
        '1' AS IsInt,
        'MB' AS Unit
    FROM sys.databases b
    LEFT JOIN sys.master_files a
        ON b.database_id = a.database_id

    WHERE b.name LIKE '%' + @name + '%'
)
SELECT
    c.Channel,
    t.value,
    c.IsInt,
    c.Unit
FROM Cte c
CROSS APPLY (VALUES
    (c.TotalValue), (c.RowsValue), (c.LogsValue)
) t(value)

Here is Dwain Camp's article comparing the different ways to unpivot:

这是Dwain Camp的文章,比较了不同的方式:

• An Alternative (Better?) Method to UNPIVOT (SQL Spackle)

UNPIVOT的替代(更好?)方法(SQL Spackle)

---

You could easily add another column in the VALUES to allow for more descriptive Channel:

您可以在VALUES中轻松添加另一列以允许更具描述性的频道:

DECLARE @name nvarchar(20)
SET @name = 'H3';

WITH Cte(Channel, TotalValue, RowsValue, LogsValue, IsInt, Unit) AS(
    SELECT
        @name AS Channel,
        SUM((a.size * 8) / 1024) Value,
        SUM(CASE WHEN type_desc LIKE 'ROWS' THEN (a.size * 8) / 1024 ELSE 0 END) AS Value,
        SUM(CASE WHEN type_desc LIKE 'LOG' THEN (a.size * 8) / 1024 ELSE 0 END) AS Value,
        '1' AS IsInt,
        'MB' AS Unit
    FROM sys.databases b
    LEFT JOIN sys.master_files a
        ON b.database_id = a.database_id

    WHERE b.name LIKE '%' + @name + '%'
)
SELECT
    c.Channel + ' ' + name AS Channel,
    t.value,
    c.IsInt,
    c.Unit
FROM Cte c
CROSS APPLY (VALUES
    ('Total', c.TotalValue), 
    ('Rows', c.RowsValue), 
    ('Logs', c.LogsValue)
) t(name, value)

#4

Using Pivot / Unpivot :

使用Pivot / Unpivot:

            declare
                @name NVARCHAR(20)
            set
                @name = 'H3'

                select Channel , Value ,IsInt ,Unit from 
                            (
            select
                @name as Channel,
                sum((a.size*8)/1024) Value0,
                SUM(CASE WHEN type_desc like 'ROWS' THEN (a.size*8)/1024 ELSE 0 END) Value1,
                SUM(CASE WHEN type_desc like 'LOG' THEN (a.size*8)/1024 ELSE 0 END) Value2,
                '1' as IsInt,
                'MB' as Unit             
                from 
                sys.databases b
                left join sys.master_files a
                on b.database_id = a.database_id
                where b.name like '%'+@name+'%'

                            ) as xx
                               unpivot
                               (
                Value for col in (Value0,Value1,value2)
            )aaa

Please let us know if you have any concenrs

如果您有任何想法,请告诉我们