带有NULL的单个表结果的SQL子查询

Question

我尝试过只在一个表中选择with查询。但出于某种原因我得到了一个无效的结果。

我的桌子看起来像这样。

| id | ItemCode | ItemAmount | Counter  |
-----------------------------------------
| 1  | 001      | 1          | Counter-1 |
| 2  | 001      | 1          | Counter-2 |
| 3  | 002      | 2          | Counter-1 |
| 4  | 002      | 2          | Counter-2 |
| 5  | 002      | 1          | Counter-2 |

我尝试过这样的SQL：

select 
    id,
    itemCode, 
    (select ItemAmount where Counter = 'Counter-1') as 'Count 1 Result',
    (select Counter where Counter = 'COUNTER-1') as 'Count Is 1',
    (select ItemAmount where Counter = 'Counter-2') as 'Count 2 Result',
    (select Counter where Counter = 'COUNTER-2') as 'Count Is 2',
from 
    My_Table

我得到的结果是：

| id | ItemCode | Count 1 Result | Count Is 1 | Count 2 Result | Count Is 2 |
----------------------------------------------------------------------
| 1  | 001      | 1               | Counter-1 | NULL           | NULL |
| 2  | 001      | NULL            | NULL      | 1              | Counter-2    |
| 3  | 002      | 2               | Counter-1 | NULL           | NULL |
| 4  | 002      | NULL            | NULL      | 2              | Counter-2    |
| 5  | 002      | NULL            | NULL      | 1              | Counter-2    |

如您所见，我得到了空值为空的结果。我怎么能这样做呢？

| id | ItemCode | Count 1 Result | Count Is 1 | Count 2 Result | Count Is 2 |
-------------------------------------------------------------------------
| 1  | 001      | 1               | Counter-1 | 2              | Counter-2 |
--------------------------------------------------------------------------
| 2  | 002      | 2               | Counter-1 | 3              | Counter-2 |
--------------------------------------------------------------------------

我想使它不再为空值，如果计数器和项目代码具有相同的值，则不存在带有和项数量的双项代码。

一张桌子就能做到这一点吗？如果是的话我该怎么做。提前感谢

Answer 1

您可以尝试使用聚合函数条件来实现它。

下面是SQL-server示例：

CREATE TABLE My_Table(
  id INT,
  ItemCode VARCHAR(50),
  ItemAmount INT,
  Counter VARCHAR(50)
);


 INSERT INTO My_Table VALUES (1, '001', 1 ,'Counter-1');
 INSERT INTO My_Table VALUES (2, '001', 1 ,'Counter-2');
 INSERT INTO My_Table VALUES (3, '002', 2 ,'Counter-1');
 INSERT INTO My_Table VALUES (4, '002', 2 ,'Counter-2');
 INSERT INTO My_Table VALUES (5, '002', 1 ,'Counter-2');

查询1

select 
    ROW_NUMBER() OVER(ORDER BY itemCode) id,
    itemCode, 
    SUM(CASE WHEN Counter = 'Counter-1' THEN ItemAmount ELSE 0 END) as 'Count 1 Result',
    MAX(CASE WHEN Counter = 'COUNTER-1' THEN Counter END) as 'Count Is 1',
    SUM(CASE WHEN Counter = 'Counter-2' THEN ItemAmount ELSE 0 END) as 'Count 2 Result',
    MAX(CASE WHEN Counter = 'COUNTER-2' THEN Counter  END) as 'Count Is 2'
from 
    My_Table
GROUP BY 
    itemCode

结果

| id | itemCode | Count 1 Result | Count Is 1 | Count 2 Result | Count Is 2 |
|----|----------|----------------|------------|----------------|------------|
|  1 |      001 |              1 |  Counter-1 |              1 |  Counter-2 |
|  2 |      002 |              2 |  Counter-1 |              3 |  Counter-2 |

Answer 2

尝试条件聚合，类似于：

SELECT min(id) id,
       itemcode,
       sum(CASE
             WHEN counter = 'Counter-1' THEN
               itemamount
             ELSE
               0
           END) count1result,
       'Counter-1' countis1,
       sum(CASE
             WHEN counter = 'Counter-2' THEN
               itemamount
             ELSE
               0
           END) count2result,
       'Counter-2' countis2
       FROM my_table
       GROUP BY itemcode;

Answer 3

尽管我不确定您期望的是什么，而不干扰您的代码，但我已经给出了查询。在您的需求中实现

;WITH CTE AS (select 

    itemCode, 
    (select SUM(ItemAmount) where Counter = 'Counter-1') as 'Count 1 Result',
    (select MAX(Counter) where Counter = 'COUNTER-1') as 'Count Is 1',
    (select SUM(ItemAmount) where Counter = 'Counter-2') as 'Count 2 Result',
    (select MAX(Counter) where Counter = 'COUNTER-2') as 'Count Is 2'
from 
    My_table
    GROUP BY 
    itemCode,Counter )

    Select RANK()OVER ( ORDER BY itemcode)Id,
    itemCode,
    MAX([Count 1 Result])[Count 1 Result],
    MAX([Count Is 1])[Count Is 1],
    MAX([Count 2 Result])[Count 2 Result],
    MAX([Count Is 2])[Count Is 2] 
        from  CTE 
    GROUP BY itemCode