sql Server：如果您使用的是sql Server 2005，则可以实现PIVOT功能。

如果您要将已知数量的值转换为列，则可以对查询进行硬编码。

select typename,total,Deployed,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,shipped)
) piv;

见SQL Fiddle with Demo。

但是，如果您具有未知数量的状态值，则需要使用动态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)'),'')

set @query = 'SELECT typename,' + @cols + ' from 
             (
                select count(*) over(partition by t.typename) total,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 Fiddle with Demo

这也可以使用带有case表达式的聚合函数编写：

select typename,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,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 Fiddle with Demo

MysqL：这个数据库没有pivot函数，所以你必须使用aggregate函数和CASE表达式。它也没有窗口函数，因此您必须稍微更改查询到以下内容：

select typename,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 Fiddle with Demo

然后，如果您需要MysqL中的动态解决方案，则必须使用预准备语句来生成要执行的sql字符串：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN statusname = ''',statusname,''' THEN 1 else 0 END) AS `','`'
    )
  ) INTO @sql
FROM assetstatus;

SET @sql 
  = CONCAT('SELECT typename,',@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 Fiddle with Demo。

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

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