mysql - 查询特定字段元素不为空的行

Question

我有以下 MySQL 查询

SELECT field1,
COUNT(DISTINCT field2) AS numbercount, 
MAX(ADDTIME(`date`,`start`)) AS `datetime` FROM mydatabase GROUP BY field1 
ORDER BY numbercount DESC, `datetime` ASC LIMIT 0,11

但我的结果并不像我想要的那样。我只需要考虑“field1”具有任何值且不为空的行。

建议？

编辑:同时我测试了以下解决方案(添加 WHERE NOT LIKE '')，似乎没问题:

SELECT field1, COUNT(DISTINCT field2) AS numbercount,
MAX(ADDTIME(date,start)) AS datetime FROM mydatabase WHERE NOT LIKE '' GROUP BY field1
ORDER BY numbercount DESC, datetime ASC LIMIT 0,11

有更好的解决方案吗？

Answer 1

您需要添加field is not null和empty语句。

SELECT field1,
COUNT(DISTINCT field2) AS numbercount, 
MAX(ADDTIME(`date`,`start`)) AS `datetime`
FROM mydatabase WHERE field1<>null AND TRIM(field1) <> ‘’ GROUP BY field1 
ORDER BY numbercount DESC, `datetime` ASC LIMIT 0,11