代码之家 › 专栏 › 技术社区 › ringord

SQL Server,透视未指定的列数和行数

pivot sql-server-2012 sql

0

ringord · 技术社区 · 8 年前

我每天查询每个门的入口数量。

首先,我查询进入由“door\u code column”标识的相关门的人数,以及由pass\u id column标识的pass key id。所以我将“pass\u id”列分组,以删除入口中的重复人员。

SELECT 
    t1.[entrance_date],
    [door_code],
    COUNT([pass_id]) AS [entrance_count]
FROM
    (SELECT
         CONVERT(date, substring(entrance_date, 0, 9)) as [entrance_date],   
         [pass_id], [door_code]
     FROM
         dbo.entrance_history
     WHERE
         door_code IN ('000A', '000B', '000C'....continued)
     GROUP BY
         CONVERT(date, substring(entrance_date, 0, 9)), [pass_id], [door_code]) t1
WHERE
    t1.entrance_date BETWEEN '2018-03-10' AND '2018-03-13' 
GROUP BY 
    [door_code], [entrance_date]
ORDER BY 
    [door_code] DESC, [entrance_date] DESC

此查询返回如下结果集:

entrance_date|door_code|entrance_count
2018-03-13   |000C     |9
2018-03-12   |000C     |23
2018-03-11   |000C     |18
2018-03-10   |000C     |19
2018-03-13   |000B     |1
2018-03-12   |000B     |1
2018-03-11   |000B     |1
2018-03-10   |000B     |1
2018-03-12   |000A     |1
.....continued

我想把它变成

entrance_date|000C|000B|000A
2018-03-13   |9   |1   |0
2018-03-12   |23  |1   |1
2018-03-11   |18  |1   |1
2018-03-10   |19  |1   |1

我尝试了如下pivot,但它不起作用。我正在使用SQL Server 2012。

declare @doorCode nvarchar(Max) = '''000A'',''000B'',''000C'''
declare @sql nvarchar(Max);

set @sql = 'select base_table.[enterance_date], '
+ @doorCode +
' from
(SELECT t1.[entrance_date],
[door_code],
count([pass_id]) as [entrance_count]
FROM
(
    select CONVERT(date,substring(entrance_date,0,9)) as [entrance_date], [pass_id], [door_code]
    from dbo.entrance_history
    where door_code in ('+@doorCode+')
    group by CONVERT(date,substring(entrance_date,0,9)),[pass_id], [door_code]
) t1
group by [door_code], [entrance_date]
) as [base_table]
    pivot 
    (
      base_table.enterance_count 
      for base_table.[door_code] in ('+@doorCode+')
    ) as pivottable'

exec sp_executesql @sql

2 回复 | 直到 8 年前

1

0

uzi 8 年前

检查此查询。我相信一组就足够了

declare @doorCode nvarchar(Max) = '''000A'',''000B'',''000C'''
declare @doorCode2 nvarchar(Max) = '[000A],[000B],[000C]'
declare @sql nvarchar(Max);

set @sql = '
    select
        entrance_date, ' + @doorCode2 + '
    from (
        select 
            CONVERT(date,substring(entrance_date,0,9)) as [entrance_date], count([pass_id]) as [entrance_count], [door_code]
        from 
            dbo.entrance_history
        where 
            door_code in (' + @doorCode + ')
            group by CONVERT(date,substring(entrance_date,0,9)), [door_code]
    ) as [base_table]
    pivot (
        max([entrance_count]) for [door_code] in (' + @doorCode2 + ')
    ) as pvt
    '
exec sp_executesql @sql

2

0

ringord 8 年前

乌兹的回答部分正确。虽然使用一个分组就足够了,但分组子句中必须包含pass id,这样我们就可以删除重复的人的入学记录。我想问一下人数进了门,没有多少入口。

为此,我首先查询每个 pass\u id 如下面的数据集。

entrance_date|door_code|entrance_count|pass_id
2018-03-13   |000C     |4             |P0001
2018-03-13   |000C     |2             |P0002
2018-03-13   |000C     |1             |P0003
2018-03-13   |000C     |1             |P0004
2018-03-13   |000B     |1             |P0001
2018-03-13   |000B     |1             |P0002
2018-03-13   |000B     |1             |P0003
2018-03-13   |000B     |1             |P0004
2018-03-13   |000A     |1             |P0001
.... continued

上面的结果由下面的查询返回。

select 
  CONVERT(date,substring(entrance_date,0,9)) as [entrance_date],  [door_code], count([pass_id]) as [entrance_count], [pass_id]
  from dbo.entrance_history
  where door_code in ('000A', '000B', '000C'....continued)
  group by CONVERT(date,substring(entrance_date,0,9)), [door_code], [pass_id]

现在,我需要在表上方透视以获得我想要的数据集。要获取人数(根据“1人1次通过”规则,由“pass\u id”列标识),我需要计数 pass\u id。然后我得到这个查询。

declare @doorCode nvarchar(Max) = '''000A'',''000B'',''000C'''
declare @doorCode2 nvarchar(Max) = '[000A],[000B],[000C]'
declare @sql nvarchar(Max);

set @sql = '
  select entrance_date, ' + @doorCode2 + '
  from (
    select 
      CONVERT(date,substring(entrance_date,0,9)) as [entrance_date],  [door_code], [pass_id]
      from dbo.entrance_history
      where door_code in ('+ @doorCode +')
      group by CONVERT(date,substring(entrance_date,0,9)), [door_code], [pass_id]
  ) as [base_table]
  pivot (
    count([pass_id]) 
    for [door_code] in ('+ @doorCode2 +')
  ) as [pvt] 
  order by entrance_date desc
'
exec sp_executesql @sql

这将返回我想要的数据集。

entrance_date|000A|000B|000C
2018-03-13   |9   |4   |1
2018-03-12   |23  |1   |1
2018-03-11   |18  |1   |1
2018-03-10   |19  |1   |1