sql - 我需要知道如何创建交叉表查询

Question

我需要帮助创建以下结果。我想到了一个 sql pivot 但我不知道如何使用它。看了几个例子，想不出解决办法。也欢迎任何其他关于如何实现这一点的想法。状态列必须是动态生成的。

有三个表，assets，assettypes，assetstatus

表: Assets
Assets ID
Assets 标签 varchar(25)
Assets 类型 int
Assets 状态整数

表: Assets 类型
id int
typename varchar(20)(例如:台式机、笔记本电脑、服务器等)

表: Assets 状况
id int
statusname varchar(20)(例如:已部署、库存、已发货等)

期望的结果:

AssetType Total Deployed Inventory Shiped ...
-------------------------------------------------- ---------
台式机 100 75 20 5 ...
笔记本电脑 75 56 19 1 ...
服务器 60 50 10 0 ...

一些数据:

Assets 表:
1,hol1234,1,1
2,hol1233,1,2
3,hol3421,2,3
4,svr1234,3,1

Assets 类型表:
1、桌面
2、笔记本电脑
3、服务器

Assets 状况表:
1、部署
2、库存
3、发货

Answer 1

这种类型的转换称为枢轴。您没有指定您使用的数据库，因此我将为 SQL Server 和 MySQL 提供答案。

SQL 服务器:如果您使用 SQL Server 2005+，您可以实现 PIVOT功能。

如果您有已知数量的要转换为列的值，则可以对查询进行硬编码。

select typename, total, Deployed, Inventory, shipped
from
(
  select count(*) over(partition by t.typename) total,
    s.statusname,
    t.typename
  from assets a
  inner join assettypes t
    on a.assettype = t.id
  inner join assetstatus s
    on a.assetstatus = s.id
) d
pivot
(
  count(statusname)
  for statusname in (Deployed, Inventory, shipped)
) piv;

见 SQL Fiddle with Demo .

但是如果你有一个未知数量的 status值，那么您将需要使用动态 sql 在运行时生成列列表。

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(statusname) 
                    from assetstatus
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT typename, total,' + @cols + ' from 
             (
                select count(*) over(partition by t.typename) total,
                  s.statusname,
                  t.typename
                from assets a
                inner join assettypes t
                  on a.assettype = t.id
                inner join assetstatus s
                  on a.assetstatus = s.id
            ) x
            pivot 
            (
                count(statusname)
                for statusname in (' + @cols + ')
            ) p '

execute(@query)

见 SQL Fiddle with Demo

这也可以使用带有 case 表达式的聚合函数来编写:

select typename,
  total,
  sum(case when statusname ='Deployed' then 1 else 0 end) Deployed,
  sum(case when statusname ='Inventory' then 1 else 0 end) Inventory,
  sum(case when statusname ='Shipped' then 1 else 0 end) Shipped
from
(
  select count(*) over(partition by t.typename) total,
    s.statusname,
    t.typename
  from assets a
  inner join assettypes t
    on a.assettype = t.id
  inner join assetstatus s
    on a.assetstatus = s.id
) d
group by typename, total

见 SQL Fiddle with Demo

MySQL:该数据库没有数据透视函数，因此您必须使用聚合函数和 CASE表达。它也没有窗口函数，因此您必须将查询稍微更改为以下内容:

select typename,
  total,
  sum(case when statusname ='Deployed' then 1 else 0 end) Deployed,
  sum(case when statusname ='Inventory' then 1 else 0 end) Inventory,
  sum(case when statusname ='Shipped' then 1 else 0 end) Shipped
from
(
  select t.typename,
    (select count(*) 
     from assets a1 
     where a1.assettype = t.id 
     group by a1.assettype) total,
    s.statusname
  from assets a
  inner join assettypes t
    on a.assettype = t.id
  inner join assetstatus s
    on a.assetstatus = s.id
) d
group by typename, total;

见 SQL Fiddle with Demo

那么如果你需要在 MySQL 中使用动态解决方案，你将不得不使用准备好的语句来生成要执行的 sql 字符串:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN statusname = ''',
      statusname,
      ''' THEN 1 else 0 END) AS `',
      statusname, '`'
    )
  ) INTO @sql
FROM assetstatus;

SET @sql 
  = CONCAT('SELECT typename,
              total, ', @sql, ' 
            from
            (
              select t.typename,
                (select count(*) 
                 from assets a1 
                 where a1.assettype = t.id 
                 group by a1.assettype) total,
                s.statusname
              from assets a
              inner join assettypes t
                on a.assettype = t.id
              inner join assetstatus s
                on a.assetstatus = s.id
            ) d
            group by typename, total');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

见 SQL Fiddle with Demo .

两个数据库中的所有查询的结果都是相同的:

| TYPENAME | TOTAL | DEPLOYED | INVENTORY | SHIPPED |
-----------------------------------------------------
|  Desktop |     2 |        1 |         1 |       0 |
|   Laptop |     1 |        0 |         0 |       1 |
|   Server |     1 |        1 |         0 |       0 |