mysql数据库创建交叉表查询_sql – 我需要知道如何创建交叉表查询

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

SQL Server:如果您使用的是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在运行时生成列列表。

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)

这也可以使用带有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:这个数据库没有pivot函数,所以你必须使用aggregate函数和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;

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

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

| TYPENAME | TOTAL | DEPLOYED | INVENTORY | SHIPPED |

-----------------------------------------------------

| Desktop | 2 | 1 | 1 | 0 |

| Laptop | 1 | 0 | 0 | 1 |

| Server | 1 | 1 | 0 | 0 |

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页

打赏作者

徐逸卿

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值