MySQL -行到列

我试着搜索帖子，但我只找到SQL Server/Access的解决方案。我需要一个解决方案在MySQL (5.X)。

我有一个表(称为历史)3列:hostid, itemname, itemvalue。如果我执行select (select * from history)，它会返回

   +--------+----------+-----------+
   | hostid | itemname | itemvalue |
   +--------+----------+-----------+
   |   1    |    A     |    10     |
   +--------+----------+-----------+
   |   1    |    B     |     3     |
   +--------+----------+-----------+
   |   2    |    A     |     9     |
   +--------+----------+-----------+
   |   2    |    C     |    40     |
   +--------+----------+-----------+

如何查询数据库以返回类似的内容

   +--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

当前回答

使用子查询

SELECT  hostid, 
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='A' AND hostid = t1.hostid) AS A,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='B' AND hostid = t1.hostid) AS B,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='C' AND hostid = t1.hostid) AS C
FROM TableTest AS T1
GROUP BY hostid

但如果子查询结果超过一行，则会出现问题，在子查询中使用进一步的聚合函数

2011-11-02 06:00:53

其他回答

SELECT 
    hostid, 
    sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,  
    sum( if( itemname = 'B', itemvalue, 0 ) ) AS B, 
    sum( if( itemname = 'C', itemvalue, 0 ) ) AS C 
FROM 
    bob 
GROUP BY 
    hostid;

2009-08-10 13:12:13

我很抱歉这么说，也许我没有完全解决你的问题，但PostgreSQL比MySQL早10年，与MySQL相比是非常先进的，有很多方法可以轻松实现这一点。安装PostgreSQL并执行此查询

CREATE EXTENSION tablefunc;

然后瞧!这里有大量的文档:PostgreSQL: documentation: 9.1: tablefunc或this查询

CREATE EXTENSION hstore;

然后，瞧!PostgreSQL: Documentation: 9.0: hstore

2019-05-21 21:54:31

我把它变成Group By hostId，然后它只会显示第一行的值，如:

A   B  C
1  10
2      3

2011-04-28 08:54:56

我的解决方案:

select h.hostid, sum(ifnull(h.A,0)) as A, sum(ifnull(h.B,0)) as B, sum(ifnull(h.C,0)) as  C from (
select
hostid,
case when itemName = 'A' then itemvalue end as A,
case when itemName = 'B' then itemvalue end as B,
case when itemName = 'C' then itemvalue end as C
  from history 
) h group by hostid

它在提交的案例中产生预期的结果。

2014-01-29 21:06:37

这不是你正在寻找的确切答案，但这是一个解决方案，我需要在我的项目，希望这有助于某人。这将列出用逗号分隔的1到n行项目。Group_Concat使这在MySQL中成为可能。

select
cemetery.cemetery_id as "Cemetery_ID",
GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
cemetery.latitude as Latitude,
cemetery.longitude as Longitude,
c.Contact_Info,
d.Direction_Type,
d.Directions

    from cemetery
    left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id 
    left join names on cemetery_names.name_id = names.name_id 
    left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id 

    left join 
    (
        select 
            cemetery_contact.cemetery_id as cID,
            group_concat(contacts.name, char(32), phone.number) as Contact_Info

                from cemetery_contact
                left join contacts on cemetery_contact.contact_id = contacts.contact_id 
                left join phone on cemetery_contact.contact_id = phone.contact_id 

            group by cID
    )
    as c on c.cID = cemetery.cemetery_id


    left join
    (
        select 
            cemetery_id as dID, 
            group_concat(direction_type.direction_type) as Direction_Type,
            group_concat(directions.value , char(13), char(9)) as Directions

                from directions
                left join direction_type on directions.type = direction_type.direction_type_id

            group by dID


    )
    as d on d.dID  = cemetery.cemetery_id

group by Cemetery_ID

这个墓地有两个公共名称，因此名称被列在不同的行中，由一个id连接，但有两个名称id，查询产生如下内容 CemeteryID Cemetery_Name 纬度 1 阿普尔顿,Sulpher弹簧35.4276242832293

2013-10-10 14:58:42

MySQL -行到列

推荐文章

最新文章

标签