时间戳

首页 > 解决方案 > tSQL 聚合函数和分组依据

sql - tSQL 聚合函数和分组依据

问题描述

所以，我似乎无法找到一种方法来让它工作。但是，我需要的是如下。

我有一张可以说类型的表。

Type_ID, Type_Description

然后我有一个项目表。[Item Type是fk to type table]

Item_ID, Item_Type

然后我有一个结果表。[Item_ID 是对 Item 表的 fk]

Result_ID, Item_ID, Cost

所以我需要的输出是按 Type_ID 分组的 - 项目计数（可以是 0）， - 结果计数（可以是 0）， - 和成本总和（可以是 0）

我没有直接访问这些表的权限。我必须构建 sql 并将其发送到 api，所以我不知道错误，如果成功，则只知道结果，如果不知道错误 500。

似乎是较旧的 tSQL。作为列表，STRING_AGG似乎不可用。

编辑：根据要求 - 示例数据

 +---------+------------------+ 
 | Type_ID | Type_Description | 
 +---------+------------------+
 |    1    |    Example 1     |
 +---------+------------------+
 |    2    |    Example 2     |
 +---------+------------------+

 +---------+---------------+ 
 | ITEM_ID |   ITEM_TYPE   | 
 +---------+---------------+
 |    1    |       1       |
 +---------+---------------+
 |    2    |       1       |
 +---------+---------------+
 |    3    |       1       |
 +---------+---------------+
 |    4    |       2       |
 +---------+---------------+
 |    5    |       2       |
 +---------+---------------+

 +-----------+---------+------+ 
 | Result_ID | Item_ID | Cost | 
 +-----------+---------+------+ 
 |     1     |    1    |  10  |
 +-----------+---------+------+
 |     2     |    1    |  20  |
 +-----------+---------+------+
 |     3     |    2    |   5  |
 +-----------+---------+------+
 |     4     |    5    |  100 |
 +-----------+---------+------+

期望的输出

 +---------+------------+--------------+------+
 | Type_ID | Item_Count | Result_Count | Cost |
 +---------+------------+--------------+------+
 |    1    |      3     |       3      |  35  |
 +---------+------------+--------------+------+
 |    2    |      2     |       1      |  100 |
 +---------+------------+--------------+------+

标签： sqltsqljoingroup-bycount

解决方案

我认为 GMB 的回答非常好，但万一有没有项目的类型（您的要求中的某些内容），它将不会显示。所以首先让我们创建输入数据：

select 1 as Type_ID, 'Example 1' as Type_Description into #type
union all
select 2, 'Example 2'
union all
select 3, 'Example 3'

select 1 Result_ID, 1 Item_ID, 10 Cost into #item
union all
select 2, 1, 20 Cost
union all
select 3, 2, 5 Cost
union all
select 4, 5, 100 Cost

select 1 Item_ID, 1 Item_Type INTO #item_type
union all
select 2, 1
union all
select 3, 1
union all
select 4, 2
union all
select 5, 2

注意我还添加了一个没有项目的类型 3 来测试没有项目的情况。

然后是您需要的查询：

SELECT
    t.Type_ID, 
    COUNT(DISTINCT it.Item_ID) Item_Count,
    COUNT(DISTINCT i.Result_ID) Result_Count, 
    SUM(ISNULL(Cost, 0)) Cost
FROM #type t
LEFT JOIN #item_type it on it.Item_Type = t.Type_ID
LEFT JOIN #item i on i.Item_ID = it.Item_ID
GROUP BY t.Type_ID

我认为这很简单，不需要太多解释，但如有必要，请随时在评论中提问。

结果就像您要求的那样，还有一行用于类型 3：

 +---------+------------+--------------+------+
 | Type_ID | Item_Count | Result_Count | Cost |
 +---------+------------+--------------+------+
 |    1    |      3     |       3      |  35  |
 +---------+------------+--------------+------+
 |    2    |      2     |       1      |  100 |
 +---------+------------+--------------+------+
 |    3    |      0     |       0      |  0   |
 +---------+------------+--------------+------+

您提到项目数可能为 0，结果数也可能为 0。但是，两个值不是总是要么0，要么两者都有> 0吗？只有在您的类型项目多对多表没有 FK 的情况下，您才会遇到这种情况。例如，如果我添加：

insert into #item_type
select 6, 3

然后最后一行是：

 +---------+------------+--------------+------+
 |    3    |      1     |       0      |  0   |
 +---------+------------+--------------+------+

我不确定这在您的场景中是否有意义，但是由于您的帖子暗示项目和结果可以独立地为 0，这让我有点困惑。

推荐阅读