数据库的有关问题

数据库的问题
有一个设备表（tb_equip)里面有用户ID（u_name)，设备状态(status)，主健是自己增长的(ID)，设备状态有三种：正常，已报修，正在处理）现在我想统计某个用户（A)有多少设备处在报修中，多少设备正常，多少设备正在处理。研究了很久没有弄懂，请各位高手点化一下。SQL语句或是存储过程都可以。

[解决办法]

探讨
写一个存储过程
1.执行分组查询

select usetname,status,count(1) as hj into #tmp from Table group by username,status
2.行列互换
select username,case status when 1 then status end as col1,case status when 2 then status end as col2,case status when 3 then status end as col3 into #tmp2 from #tmp1

3.sum

select username,sum(isnull(col1,0)) as 正常,sum(isnull(col2,0)) as 已报修,sum(isnull(col…

[解决办法]

SQL code

select u_name, 报修 = (select count(*) from tb_equip where u_name = a.u_name and status = '已报修'), 正常 = (select count(*) from tb_equip where u_name = a.u_name and status = '正常'),正在处理 = (select count(*) from tb_equip where u_name = a.u_name and status = '正在处理')  from tb_equip a group by u_name

数据库的有关问题

热点推荐