I'm trying to group (or at least make uniq开发者_运维百科ue) a column m_group_name
unless the group is empty. This is to basically see what doesn't yet have a group (and could then be edited to be placed into one) while being able to see the existing groups.
For example;
id | m_group_name
---------------------
1 | Red
2 | Blue
3 |
4 | Red
5 |
Which, ideally, would result in one red, one blue, and 2 blanks.
I've tried making the blanks NULL and giving it a UUID value (after adapting something similar found here on Stackoverflow), but it seems to make every NULL the same value.
SELECT *, IFNULL(m_group_name,UUID()) AS m_group FROM m GROUP BY m_group
Ideally I'd rather not use NULL to be perfectly honest
EDIT: Realised that 'm_group_name' was incorrectly labelled as 'group' in the example - corrected.
That approach will not work because the UUID() function will return a single value per query, rather than a separate value for each row.
You can use the primary key:
SELECT *, IFNULL(m_group_name,id) AS m_group FROM m GROUP BY m_group
Or you can use a different function, such as RAND(), to generate a separate value for each row. You can apply a hash function like MD5() to it if you want a string value:
SELECT *, IFNULL(m_group_name,md5(rand())) AS m_group FROM m GROUP BY m_group
SELECT * FROM m WHERE LENGTH(Group) IN (0, NULL)
*LENGTH(NULL)
will return NULL
. LENGTH('')
will return 0
.
am I right that you want to see ALL the rows that DON'T yet have a Group
?
EDIT: Okay, i read it carefully. How about this:
SELECT * FROM m WHERE LENGTH(Group) NOT IN (0, NULL) GROUP BY Group
UNION ALL
SELECT * FROM m WHERE LENGTH(Group) IN (0, NULL)
精彩评论