问题描述:

mysql> select * from categories ;

+-------------+--------------------+-----------------+---------------------

| category_id | T1 | T2 | T3

+-------------+--------------------+-----------------+---------------------

| 2 | Popcorn | Bucket | NULL

| 3 | Popcorn | Jumbo | NULL

| 3 | Popcorn | Jumbo | NULL

6 | Popcorn | Combo Relish | NULL |

7 | Soft Drinks | Fountain | Apple |

7 | Soft Drinks | Fountain | Apple |

8 | Soft Drinks | Fountain | Orange |

8 | Soft Drinks | Bottled | Orange |

I have a table called categories as shown above .

For Some T2 Values the T3 can be Null or can have Value .

I have got a query as shown below

select t1, t2,

group_concat(concat(t3,'(',category_id,')')) consildated_Data

from categories

group by 1,2;

With this the results looks like this

Popcorn | Bucket | NULL

Popcorn | Combo Relish | NULL

Popcorn | Jumbo | NULL

Soft Drinks | Fountain | Apple(7),Apple(7),Orange(8)

Soft Drinks | Bottled | Orange(8)

My question is that if T3 is null , can i have the value printed as empty with corresponding category_id number ??

so that it looke like this for Bucket

Popcorn | Bucket | empty(2)

Popcorn | Jumbo | empty(3),empty(3)

Is it possible to put a if condition under the above query ??

网友答案:

Without testing, you can use an IF statement like this:

SELECT
    t1,
    t2,
    IF(
        t3 IS NULL,                                  -- condition
        CONCAT('empty(', category_id, ')'),          -- true reaction
        GROUP_CONCAT(CONCAT(t3,'(',category_id,')')) -- false reaction
    ) AS consildated_Data
FROM categories
GROUP BY 1, 2;
网友答案:

use ifnull(x, [defalt_value]) in mysql

select t1, t2, 
    group_concat(concat(ifnull(t3, ''),'(',category_id,')')) consildated_Data 
from categories
group by 1,2;

or you can also use CASE statment, but an uglier approach:

select t1, t2, 
    group_concat(concat(case t3 when null then '' else t3 end)),'(',category_id,')')) consildated_Data 
from categories
group by 1,2;
网友答案:

yes there is Case on firebird

http://www.firebirdsql.org/refdocs/langrefupd15-case.html

相关阅读:
Top