我想编写一个摘要查询并将结果显示在一个表中

I need to write a summary report on 3 different status values, with a count and an amount column for each status, with the results presented in a single table. For example, the output would look like this:

我需要针对3个不同的状态值编写一个摘要报告，每个状态有一个计数和一个数量列，结果显示在一个表中。例如，输出如下所示:

The query to produce each line of code (in an individual output) is:

生成每一行代码(在单个输出中)的查询是:

select case when status_key = '2' then 'Paid' else '' end as 'Status'
, COUNT(BillNo) as [Count]
, SUM(amtpd) as [Amount Paid]
from billtable 
where client = 101
and status_key = '2'
group by status_key

select case when status_key = '1' then 'Queued' else '' end as 'Status'
, COUNT(BillNo) as [Count]
, SUM(amtpd) as [Amount Paid]
from billtable 
where client = 101
and status_key = '1'
group by status_key

select case when status_key = '4' then 'Hold' else '' end as 'Status'
, COUNT(BillNo) as [Count]
, SUM(amtpd) as [Amount Paid]
from billtable 
where client = 101
and status_key = '4'
group by status_key

This produces three results like:

这将产生如下三个结果:

I am using SQL Server database and SSMS to develop the query.

我正在使用SQL Server数据库和SSMS来开发查询。

3 个解决方案

#1

No need for union.

不需要联盟。

Use WHERE to filter to only the status_keys that you want, then expand you CASE statement to re-code from a number to a word.

使用WHERE过滤到您想要的status_keys，然后展开CASE语句，将数字重新编码为一个单词。

select
  case when status_key = '2' then 'Paid'
       when status_key = '1' then 'Queued'
       when status_key = '4' then 'Hold'
                             else 'Error!' end     AS [Status],
  COUNT(BillNo)                                    AS [Count],
  SUM(amtpd)                                       AS [Amount Paid]
from
  billtable
where
  client = 101
  AND status_key IN ('1','2','4')
group by
  status_key

EDIT Modified example using a dimension table

使用维度表编辑修改后的示例

select
  status.description                                    AS [Status],
  COUNT(bill_table.BillNo)                              AS [Count],
  SUM(bill_table.amtpd)                                 AS [Amount Paid]
from
  billtable
inner join
  status
    on billtable.status_key = status.key
where
      bill_table.client      = 101
  AND bill_table.status_key IN ('1','2','4')
group by
  status.description

You can then have a foreign key constraint from status to billtable. This will ensure that data can not be inserted into billtable unless there is a corresponding key in status.

然后可以从状态到billtable有一个外键约束。这将确保数据不能插入到billtable中，除非状态中有相应的键。

Your lookups will then always work. But at the 'cost' of inserts failing if the status table has not been correctly populated.

您的查找将始终有效。但如果状态表没有被正确填充，则以插入失败为代价。

This fact-table and dimension-table construction is the underpinning of relational database design.

这种事实表和维度表结构是关系数据库设计的基础。

#2

you just have to union all

你只需要联合所有的人

your first query
Union all
your Second query
union all
your third query

#3

Just add UNION between your queries.

只需在查询之间添加UNION。