我需要知道如何创建交叉表查询 [英] I need to know how to create a crosstab query
问题描述
我需要帮助来创建以下结果.我想到了sql枢纽,但我不知道如何使用它.看了几个例子,无法提出解决方案.也欢迎任何其他有关如何完成此操作的想法.状态列必须动态生成.
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.
具有三个表,资产,资产类型,资产状态
Have three tables, assets, 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.)
所需结果:
AssetType Total Deployed Inventory Shipped ...
-----------------------------------------------------------
Desktop 100 75 20 5 ...
Laptop 75 56 19 1 ...
Server 60 50 10 0 ...
一些数据:
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
推荐答案
这种类型的转换称为枢轴.您没有指定要使用的数据库,所以我将为SQL Server和MySQL提供答案.
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::如果您使用的是SQL Server 2005+,则可以实现PIVOT
函数.
SQL Server: If you are using SQL Server 2005+ you can implement the PIVOT
function.
如果您想将已知数量的值转换为列,则可以对查询进行硬编码.
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;
请参见带有演示的SQL小提琴.
但是,如果您有未知数量的status
值,那么您将需要在运行时使用动态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.
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小提琴
这也可以使用带有case表达式的聚合函数来编写:
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
请参见带有演示的SQL小提琴
MySQL::该数据库没有 pivot 函数,因此您必须使用聚合函数和CASE
表达式.它也没有开窗功能,因此您必须将查询稍作更改为以下内容:
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:
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小提琴
然后,如果您需要MySQL中的动态解决方案,则必须使用准备好的语句来生成要执行的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:
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提琴.
两个数据库中的所有查询的结果都相同:
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 |
这篇关于我需要知道如何创建交叉表查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!