SQL计数和不同的查询 [英] SQL count and distinct query

问题描述

我试图建立一个同时计数和区分的查询。这是原始数据

原始数据

  + -------- + --------- + 
 | IntID | ErrorID | 
 + -------- + --------- + 
 | ins001 | 1 | 
 | ins001 | 1 | 
 | ins001 | 2 | 
 | ins002 | 3 | 
 | ins002 | 5 | 
 | ins002 | 5 | 
 | ins003 | 4 | 
 | ins003 | 1 | 
 | ins003 | 1 | 
 + -------- + --------- + 
  

尝试完成的是计算每个不同乐器ID的每个错误ID，如下所示：

期望什么

  ------- + ------------- + ------------- + ------------- + ------------- + ------------- + 
 | IntID | ErrorID = 001 | ErrorID = 002 | ErrorID = 003 | ErrorID = 004 | ErrorID = 005 | 
 + -------- + ------------- + ------------- + -------- ----- + ------------- + ------------- + 
 | ins001 | 2 | 1 | 0 | 0 | 0 | 
 | ins002 | 0 | 0 | 1 | 0 | 2 | 
 | ins003 | 2 | 0 | 0 | 1 | 0 | 
 + -------- + ------------- + ------------- + -------- ----- + ------------- + ------------- + 
  

非常感谢任何建议或帮助。提前感谢

解决方案

如果您有一组有限的错误ID，您应该提前知道。选择IntID，[1]表示为[ErrorID = 001]，[2]表示为[ErrorID = 002]，[3]表示为[ErrorID =从
（选择IntID，ErrorID，1作为cnt
，来自#YourTable）中的[$ 003]，[4] as [ErrorID = 004]，[5] as [ErrorID = 005]

pivot
（
count（cnt）
for（[1]，[2]，[3]，[4]，[5]）中的ErrorID
）as pvt

编辑

请求，如果你有大量的错误，你可以动态构建查询。 Coalesce帮助构建任意数量的错误的字符串。 （RIGHT只用于从字符串中删除第一个逗号）

  DECLARE @DisplayList varchar（1000）
 DECLARE @SearchList varchar（1000）
 DECLARE @sql varchar（MAX）
 
 select @DisplayList = COALESCE（@DisplayList，''）+'，['+ cast（ErrorID as VARCHAR ）+'] as'+'[ErrorID ='+ cast（ErrorID as VARCHAR（100））+']'from #YourErrorsTable 
 select @SearchList = COALESCE（@SearchList，''）+'， '+ cast（ErrorID as VARCHAR（100））+']'from #YourErrorsTable 
 
 set @sql ='select IntID'+ @DisplayList +'
 from 
 （'+ RIGHT（@SearchList，））中的错误ID，因为它是一个错误ID， LEN（@SearchList）-1）+'）
）as pvt'
 
 EXEC（@sql）
  

I'm trying to build up a query that would count and distinct at the same time. This is the raw data

Raw Data

+--------+---------+
| IntID  | ErrorID |
+--------+---------+
| ins001 |    1    |
| ins001 |    1    |
| ins001 |    2    |
| ins002 |    3    |
| ins002 |    5    |
| ins002 |    5    |
| ins003 |    4    |
| ins003 |    1    |
| ins003 |    1    |
+--------+---------+

What im trying to accomplish is a count for each error id for each distinct instrument id, as shown below:

What is expected

+--------+-------------+-------------+-------------+-------------+-------------+
| IntID  | ErrorID=001 | ErrorID=002 | ErrorID=003 | ErrorID=004 | ErrorID=005 |
+--------+-------------+-------------+-------------+-------------+-------------+
| ins001 |      2      |      1      |      0      |      0      |      0      |
| ins002 |      0      |      0      |      1      |      0      |      2      |
| ins003 |      2      |      0      |      0      |      1      |      0      |
+--------+-------------+-------------+-------------+-------------+-------------+

Any advice or help is greatly appreciated. Thanks in advance

解决方案

This should work if you have a finite set of ErrorIDs that you know ahead of time.

    select IntID, [1] as [ErrorID=001], [2] as [ErrorID=002], [3] as [ErrorID=003], [4] as [ErrorID=004], [5] as [ErrorID=005]
    from
    (select IntID, ErrorID, 1 as cnt
        from #YourTable) as t
    pivot
    (
        count(cnt)
        for ErrorID in ([1], [2], [3], [4], [5])
    ) as pvt

Edit

As you requested, If you have a larger amount of Errors you can dynamically build the query. Coalesce helps to build the strings for any number of Errors. (RIGHT is only used to remove the first comma from the string)

    DECLARE @DisplayList    varchar(1000)
    DECLARE @SearchList     varchar(1000)
    DECLARE @sql            varchar(MAX)

    select @DisplayList = COALESCE(@DisplayList, '') + ',[' + cast(ErrorID as VARCHAR(100)) + '] as ' + ' [ErrorID=' + cast(ErrorID as VARCHAR(100)) + ']' from #YourErrorsTable
    select @SearchList = COALESCE(@SearchList, '') + ',[' + cast(ErrorID as VARCHAR(100)) + ']' from #YourErrorsTable

    set @sql = 'select IntID' + @DisplayList +'
        from
        (select IntID, ErrorID, 1 as cnt
            from #YourTable) as t
        pivot
        (
            count(cnt)
            for ErrorID in (' + RIGHT(@SearchList, LEN(@SearchList)-1) + ')
        ) as pvt'

    EXEC(@sql)

这篇关于SQL计数和不同的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！