主页 / dba / 问题 / 130218
Accepted
obautista
Asked: 2016-02-24 09:59:42 +0800 CST

如何比较和聚合表中的数据

  • 772

有没有一种方法可以评估表中的数据以得出一组 ProductID 中的 PRODUCTID 是通过还是失败？这是我的数据示例：

DECLARE @example TABLE (ID INT NOT NULL, PRODUCTID INT, PASSED BIT, LEVEL INT, CREATEDATE DATETIME, PRIMARY KEY (ID))
INSERT INTO @example VALUES
 (  1, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  2, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  3, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  4, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  5, 101, 1, 1, '2016-01-10 07:35:02.123' )
,(  6, 102, 0, 1, '2016-01-10 07:35:02.123' )
,(  7, 102, 1, 2, '2016-01-10 07:35:02.123' )
,(  8, 103, 1, 1, '2016-01-10 10:35:02.123' )
,(  9, 103, 1, 2, '2016-01-10 09:35:02.123' )  
,( 10, 103, 1, 2, '2016-01-10 08:35:02.123' ) 
,( 11, 103, 1, 3, '2016-01-10 07:35:02.123' )

是否可以构造一个返回的查询：

PRODUCTID    ISPASSED
100          0
101          0
102          0
103          1

条件是对于给定的 PRODUCTID，需要通过 2 个不同级别的 2 个。

  • 100 是 0 因为所有 4 条记录都是针对级别 1
  • 101 是 0 因为只有 1 PASS 并且只有 1 LEVEL
  • 102 是 0 因为一条记录通过而另一条记录失败
  • 103 是 1，因为不同级别有 2 个 PASSED 记录。

非常感谢！

编辑：

我想出了这个查询，但它很短，因为我只能满足要求的第一部分：

WITH cte(PRODUCTID, ISPASSED) AS
(
  SELECT PRODUCTID, CASE WHEN SUM(CONVERT(INT, PASSED)) = 2 THEN 1 ELSE 0 END AS ISPASSED
  FROM (SELECT PRODUCTID, RowNumber = ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY CREATEDATE DESC), LEVEL, PASSED
    FROM @example
    GROUP BY PRODUCTID, LEVEL, CREATEDATE, PASSED) as agg
  WHERE RowNumber <=2
  GROUP BY PRODUCTID
)
select * from cte
sql-server sql-server-2014

4 个回答

  1. 由于您想计算可能包含相同值的集合中的不同值，我想说COUNT(DISTINCT ...)这是一个完美的工具。例如，以下将计算每个产品的不同级别：

    SELECT
  PRODUCTID,
  COUNT(DISTINCT LEVEL)
FROM
  @example
GROUP BY
  PRODUCTID
;

    这将是您的示例的输出：

    PRODUCTID
---------  ---
100        1
101        1
102        2
103        3

    现在，您如何才能只计算与特定条件匹配的不同值？答案是，通过使用条件聚合，即使用 CASE 表达式作为聚合函数的参数。因此，要仅计算不同的通过级别，您可以使用如下查询：

    SELECT
  PRODUCTID,
  COUNT(DISTINCT CASE PASSED WHEN 1 THEN LEVEL END)
FROM
  @example
GROUP BY
  PRODUCTID
;

    现在的结果是

    PRODUCTID
---------  ---
100        1
101        1
102        1
103        3

    这些数字现在看起来是正确的，那么如何将这些1,3变成0,1呢？为什么，当然是通过使用另一个 CASE 表达式：

    SELECT
  PRODUCTID,
  ISPASSED = CASE
                WHEN COUNT(DISTINCT CASE PASSED WHEN 1 THEN LEVEL END) >= 2
                THEN 1
                ELSE 0
              END
FROM
  @example
GROUP BY
  PRODUCTID
;

    现在您可以享受预期的输出

    PRODUCTID  ISPASSED
---------  --------
100        0
101        0
102        0
103        1
    • 2

  2. 这将根据您提供的示例数据为您提供所需的答案。你没有回答我关于级别数的问题，所以这假设不超过 3 个级别。

    select ProductID, case when [1]+ [2] + [3] >=2  then 1 else 0 end as ispassed
FROM 
(Select cast(PASSED as int) PASSED, Level, Productid
FROM @example) e 
PIVOT 
( 
Max(Passed) 
FOR [Level] in ([1], [2], [3]) )
AS pvt 
ORDER BY pvt.PRODUCTID
    • 1
  3. Best Answer

    这就是我的理解...不确定这是否涵盖您的所有情况...

    让我知道是否有任何未涵盖的特定案例。

    重新编辑：刚刚注意到

    条件是对于给定的 PRODUCTID，需要通过 2 个不同级别的 2 个。

        Select  productid, 
        Case when minpass = 1 and maxpass = 1 and nbOfLevels>1 and NbOfPass>1 then 1 else 0 end as passed
From (Select Productid, 
            min(convert(int,passed)) minPass,
            max(convert(int,passed)) maxpass, 
            -- max(Level) MaxLevel,
            sum(convert(int,passed)) NbOfPass,
            count(distinct Level) nbOfLevels
            from @example
        group by productid ) X
    • 0

  4. 这似乎有效：

    WITH cte(ProductID, IsPassed) AS
{
  SELECT PRODUCTID, CASE WHEN SUM(CONVERT(INT, PASSED)) >= 2 THEN 1 ELSE 0 END AS IsPassed
  FROM (SELECT PRODUCTID, RowNumber = ROW_NUMBER() OVER(PARTITION BY PRODUCTID, LEVEL ORDER BY CREATEDATE DESC), LEVEL, PASSED
        FROM @example
        GROUP BY PRODUCTID, LEVEL, CREATEDATE, PASSED) as agg
WHERE RowNumber = 1
GROUP BY PRODUCTID
)
select * from cte
    • 0

相关问题