mysql查询null值问题中的行到列 [英] rows to colum in mysql query null value problem

问题描述

我有2个表类别和模块。类别表有2个字段id& category模块有3个fields.id，modulename& catid



类别表有2个字段：



id&类别



模块有3个字段：



id，modulename& catid



类别

I have 2 table category and module. category table has 2 fields id & category module has 3 fields.id,modulename & catid

category table has 2 fields :

id & category

module has 3 fields:

id,modulename & catid

category

id         |        category
-----------------------------
1          |         ABC
2          |         PQR

模块

module

id   catid      modulename
--------------------------
1   1              A 
2   1              B
3   2              Q
4   2              P

我想将行转换为列，我得到如下结果：

I want to convert row to column and I'm getting a result like below:

ABC     |    PQR    
-------------------
A       |    NULL  
B       |    NULL  
NULL    |    Q
NULL    |    P

我希望得到如下结果（没有空值）

and I want result like below (without null values)

ABC      |     PQR    
-------------------
A        |      Q
B        |      P

这是我的查询

this is my query

SELECT * FROM ( SELECT CASE WHEN category='ABC' THEN modulename ELSE '' END AS 'ABC', CASE WHEN category='PQR' THEN modulename ELSE '' END AS 'PQR' FROM category c INNER JOIN module m ON m.catid=c.id )t  

但来自这个查询我有空值，你能告诉我如何避免这个空值吗？

but from this query i had null values, can u tell me how to avoid this null value?

推荐答案

看看这里： http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns [ ^ ]



也可以看看我过去的答案：自定义查询结果 [ ^ ]

Have a look here: http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns[^]

See my past answer too: customize query result[^]

这篇关于mysql查询null值问题中的行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！