SQL 我需要知道如何创建交叉表查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15714265/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
I need to know how to create a crosstab query
提问by Sam
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 数据透视表，但我不知道如何使用它。看了几个例子，想不出解决办法。也欢迎任何其他关于如何实现这一点的想法。状态列必须动态生成。
Have three tables, assets, assettypes, assetstatus
有三张表，assets，assettype，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
回答by Taryn
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 PIVOTfunction.
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.
But if you have an unknown number of statusvalues, then you will need to use dynamic sql to generate the list of columns at run-time.
但是如果您有未知数量的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)
This can also be written using an aggregate function with a case expression:
这也可以使用带有 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
MySQL:This database does not have a pivotfunction so you will have to use the aggregate function and a CASEexpression. It also does not have windowing functions, so you will have to alter the query slightly to the following:
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;
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.
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 |
回答by Niels Knabe
Using a non pivot compliant DBMS (Absolute Database) I was more successful using this SQL cross-tab equivalent statement:
使用非枢轴兼容的 DBMS（绝对数据库）我使用这个 SQL 交叉表等效语句更成功：
SELECT
sub.TypeName
, SUM(sub.[Count]) AS "Total"
, SUM(CASE WHEN AssetStatus='1' THEN sub.[Count] ELSE 0 END) AS "Deployed"
, SUM(CASE WHEN AssetStatus='2' THEN sub.[Count] ELSE 0 END) AS "Inventory"
, SUM(CASE WHEN AssetStatus='3' THEN sub.[Count] ELSE 0 END) AS "Shipped"
FROM
(
SELECT
t.TypeName
, AssetStatus
, COUNT(AssetID) AS "Count"
FROM
Assets
JOIN AssetTypes t ON t.ID = AssetType
JOIN AssetStatus s ON s.ID = AssetStatus
GROUP BY t.TypeName, AssetStatus, s.StatusName
) sub
GROUP BY sub.TypeName
;
As I realized this code (above) didn't work with MySQL I adapted my code as below executing equally well in MySQL as in my current Absolute Database. The reason is the specific NULL handling avoiding the pitfall of dBase, Paradox as well as Absolute Database generously accepting COUNT(NULL) = 0 not accepted in mainstream databases. So believing this will execute well in most databases (handling CASE ..) this is my adapted code:
当我意识到这段代码（上面）不适用于 MySQL 时，我调整了我的代码，如下所示，在 MySQL 中与在我当前的绝对数据库中的执行情况一样好。原因是特定的 NULL 处理避免了 dBase、Paradox 以及 Absolute Database 慷慨接受 COUNT(NULL) = 0 不被主流数据库接受的陷阱。所以相信这会在大多数数据库中很好地执行（处理 CASE ..）这是我改编的代码：
SELECT
sub.TypeName
, SUM(sub.AssetCase) AS "Total"
, SUM(CASE WHEN sub.StatusName = 'Deployed' THEN sub.AssetCase ELSE 0 END) AS "Deployed"
, SUM(CASE WHEN sub.StatusName = 'Inventory' THEN sub.AssetCase ELSE 0 END) AS "Inventory"
, SUM(CASE WHEN sub.StatusName = 'Shipped' THEN sub.AssetCase ELSE 0 END) AS "Shipped"
FROM
(
SELECT
c.TypeName
, c.StatusName
, CASE WHEN a.AssetID IS NULL THEN 0 ELSE 1 END AS "AssetCase"
FROM
(
SELECT
t.ID AS tID
, t.TypeName
, s.ID AS sID
, s.StatusName
FROM
AssetTypes t, AssetStatus s
) c
LEFT JOIN Assets a
ON a.AssetType = c.tID AND a.AssetStatus = c.sID
) sub
GROUP BY
sub.TypeName
;
Best Regards Niels Knabe
最好的问候 Niels Knabe
