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

I need help creating the below results. I thought of a sql pivot but I don't know how to use it. Looked at a few examples and cannot come up with a solution. Any other ideas on how to accomplish this is also welcome. Status columns must be dynamically generated.

我需要帮助创建下面的结果。我想到了一个sql pivot，但我不知道如何使用它。看了几个例子，没有找到解决方案。关于如何实现这一目标的任何其他想法也受到欢迎。状态列必须动态生成。

Have three tables, assets, assettypes, assetstatus

有三个表，资产，assettypes, assetstatus

Table: assets
assetid     int
assettag    varchar(25)
assettype   int
assetstatus int

Table: assettypes
id         int
typename   varchar(20)  (ex: Desktop, Laptop, Server, etc.)

Table: assetstatus
id         int
statusname varchar(20)  (ex: Deployed, Inventory, Shipped, etc.)

Desired results:

预期的结果:

AssetType     Total   Deployed   Inventory  Shipped     ...
-----------------------------------------------------------
Desktop         100       75        20          5       ...
Laptop           75       56        19          1       ...
Server           60       50        10          0       ...

Some Data:

一些数据:

assets table:
1,hol1234,1,1
2,hol1233,1,2
3,hol3421,2,3
4,svr1234,3,1

assettypes table:
1,Desktop
2,Laptop
3,Server

assetstatus table:
1,Deployed
2,Inventory
3,Shipped

1 个解决方案

#1

This type of transformation is called a pivot. You did not specify what database you are using so I will provide a answers for SQL Server and MySQL.

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

---

SQL Server: If you are using SQL Server 2005+ you can implement the PIVOT function.

SQL Server:如果您正在使用SQL Server 2005+，您可以实现PIVOT函数。

If you have a known number of values that you want to convert to columns then you can hard-code the query.

如果您想要转换为列的值的数量是已知的，那么您可以硬编码查询。

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;

See SQL Fiddle with Demo.

参见SQL小提琴演示。

But if you have an unknown number of status values, then you will need to use dynamic sql to generate the list of columns at run-time.

但是，如果您有一个未知的状态值，那么您将需要使用动态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)

See SQL Fiddle with Demo

参见SQL小提琴演示

This can also be written using an aggregate function with a case expression:

这也可以用一个案例表达式的聚合函数来编写:

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

See SQL Fiddle with Demo

参见SQL小提琴演示

---

MySQL: This database does not have a pivot function so you will have to use the aggregate function and a CASE expression. It also does not have windowing functions, so you will have to alter the query slightly to the following:

MySQL:这个数据库没有pivot函数，所以必须使用聚合函数和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;

See SQL Fiddle with Demo

参见SQL小提琴演示

Then if you need a dynamic solution in MySQL, you will have to use a prepared statement to generate the sql string to execute:

然后，如果需要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;

See SQL Fiddle with Demo.

参见SQL小提琴演示。

The result is the same for all queries in both databases:

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

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