count（）中的错误数 [英] wrong number in count()

问题描述

我已经发布了两个有关我的问题的问题，我几乎在那里。

I've already posted two questions about my problem and I'm almost there I think.

这些是我的旧帖：

1. 使用数组/变量sql-query


2. extend SQL result - 如果不存在则添加行

1. use array/variable in sql-query
2. extend SQL result - add row if not exist

更容易解释一个例子，具有虚构示例的情况：

It easier to explain it by an example, so this is the situation with a fictive example:

我的表看起来像这样（我只显示我需要的行，此表包含+ 100k行）

My table looks like this (I only show the rows I need, this table contains +100k rows)

status  PGID    nvarchar5               nvarchar10  CatId   tp_ID   isActive
IT      NULL    Information technology  NULL        1       1       1
HR      NULL    Human Recource          NULL        1       2       1
FIN     NULL    Finance                 NULL        1       3       1
New        1    NULL                    1354        2       10001   1
New        1    NULL                    464         2       10002   1
New        1    NULL                    13465       2       10003   1
Active     1    NULL                    79846       2       10004   1
Deleted    1    NULL                    132465      2       10005   1
New        2    NULL                    79847       2       10006   1
New        2    NULL                    341         2       10007   1
Deleted    2    NULL                    465         2       10008   1
Deleted    2    NULL                    132         2       10009   1
Deleted    2    NULL                    465         2       10010   1
Deleted    2    NULL                    1           2       10011   1
New        3    NULL                    465         2       10012   1
New        3    NULL                    1465        2       10013   1
New        3    NULL                    132         2       10014   1
NULL    NULL    NULL                    NULL        3       20136   1
NULL    NULL    NULL                    NULL        4       22165   1
NULL    NULL    NULL                    NULL        3       24566   1

我使用这个查询：

SELECT stat.status
      ,d.[PGID] as PGID
      ,pg.[nvarchar5] as PGName
      ,COUNT(d.[nvarchar10])
FROM 
    (select distinct [status]
     from [content].[dbo].[USRData] 
     where  CatId = '1') stat cross Join
      [content].[dbo].[USRData]  d INNER JOIN
      [content].[dbo].[USRData]  pg on d.[PGID] = pg.[tp_ID]
where d.CatId = '1'
      AND d.[nvarchar10] is not null
      AND stat.status is not null
      AND d.[isActive] = 1
      AND pg.[CatId] = '2'
group by stat.status, 
         pg.[nvarchar5], 
         d.[PGID]
order by PGName

然后我得到这个结果：

status  PGID    nvarchar5               total
new     1       Information technology  5
active  1       Information technology  5
deleted 1       Information technology  5
new     2       Human Recource          6
active  2       Human Recource          6
deleted 2       Human Recource          6
new     3       Finance                 3
active  3       Finance                 3
deleted 3       Finance                 3

但我想有这个结果：

status  PGID    nvarchar5               total
new     1       Information technology  3
active  1       Information technology  1
deleted 1       Information technology  1
new     2       Human Recource          2
active  2       Human Recource          0
deleted 2       Human Recource          4
new     3       Finance                 3
active  3       Finance                 0
deleted 3       Finance                 0

在SQL中不是一个亲。也许这是一个愚蠢的，但我坚持...任何帮助是欢迎！ ：）

I'm not a pro in SQL. Maybe it's something stupid but I'm stuck... Any help is welcome! :)

推荐答案

我注意到，在您要寻找的结果中， 列比给出的结果，它可能是由于重复的行，我猜想 CROSS JOIN 负责它，为什么不您尝试 INNER JOIN 而不是状态

I noticed that in the result you're seeking, there's less rows in the total column than the result given, it may be due to duplicated rows, I'm guessing the CROSS JOIN is responsible for it, why don't you try an INNER JOIN instead on status

(select distinct [status]
     from [content].[dbo].[USRData] 
      where  CatId = '1') stat 
     INNER JOIN [content].[dbo].[USRData]  d on stat.[status]=d.[status]
     INNER JOIN [content].[dbo].[USRData]  pg on d.[PGID] = pg.[tp_ID]

这篇关于count（）中的错误数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！