MySQL查询多个值计数

Question

我正在尝试检索多个值的计数，但只能得到部分计数。

我的代码

SELECT COUNT(*) as count, `dateadded` FROM s2 WHERE 'LEVEL` IN (1,2,3) and 
client = 'myuser' GROUP BY `LEVEL`,`dateadded` ORDER BY `dateadded` DESC 
LIMIT 1");

我的桌子

client   dateadded     level
myuser   2019-01-21      3
myuser   2019-01-21      2
myuser   2019-01-21      5
myuser   2019-02-16      3
myuser   2019-02-16      2
myuser   2019-02-16      8
myuser   2019-02-16      2

我的退货值应该是：3->最新日期（2019-02-19），计数为1,2和3。

我想计算最新日期的多少个1、2和3。

非常感谢！

nathalie

Answer 1

虽然您的需求尚不清楚，但我认为您想要级别的计数，仅在最新日期进行数据。

这应该为您提供帮助：

SELECT COUNT(*) as count, `dateadded` , `LEVEL` FROM s2 WHERE `LEVEL` IN 
(1,2,3) and 
client = 'myuser' GROUP BY `dateadded`,`LEVEL` ORDER BY `dateadded` DESC 
LIMIT 1;

如果您不想按每个级别计数，请使用以下方式：

SELECT COUNT(*) as count, `dateadded` FROM s2 WHERE `LEVEL` IN 
(1,2,3) and 
client = 'myuser' GROUP BY `dateadded` ORDER BY `dateadded` DESC 
LIMIT 1;

Answer 2

尝试以下：

SELECT COUNT(*) as count, `dateadded` FROM s2 WHERE 'LEVEL' IN (1,2,3) and 
client = 'myuser' and `dateadded` IN (select max(dateadded) from s2) GROUP BY `LEVEL`,`dateadded` ORDER BY `dateadded` DESC 
LIMIT 1");