mysql - SQL子选择计数和组

我需要把一个月中的所有日子都拉起来，按工作日进行计数和分组。以下是数据库示例：

id | uID | dateTime
0  | 01  | Monday
1  | 01  | Monday
2  | 01  | Monday
3  | 01  | Tuesday
4  | 01  | Tuseday
5  | 01  | Friday

以下是我要从SQL检索的数组结果：

id=>0 uID=>01 dateTime=>Monday  day_group=>3
id=>1 uID=>01 dateTime=>Monday  day_group=>0
id=>2 uID=>01 dateTime=>Monday  day_group=>0
id=>3 uID=>01 dateTime=>Tuesday day_group=>2
id=>4 uID=>01 dateTime=>Tuesday day_group=>0
id=>5 uID=>01 dateTime=>Friday  day_group=>1

这是我尝试执行的操作之一：

SELECT
id,
uID,
dateTime
FROM database,
  (SELECT
  COUNT(id) AS day_group,
  uID,
  dateTime
  WHERE uID=:uID AND MONTH(:month)
  GROUP BY DAY(dateTime)
  FROM database)
WHERE uID=:uID AND MONTH(:month)

最佳答案

您应该使用窗口函数：

set @uID = 1;
set @month = 7;
select
   id, uID, datetim
   , COUNT(*) over (partition by DAY(dateTim)) AS day_group
from test
where uID=@uID and month(datetim) = @month

或者，如果您使用的是8.0之前的mysql，则这是等效的子查询：

set @uID = 1;
set @month = 7;
select
   t1.id, t1.uid, t1.datetim
   , coalesce(sq.day_group, 0)
from test as t1
    left outer join (select min(id) id, count(id) day_group from test as t2 where t2.uID=@uID and month(t2.datetim) = @month group by day(t2.datetim)) as sq on sq.id = t1.id
where t1.uID=@uID and month(t1.datetim) = @month

和我测试过的结构：

CREATE TABLE test (id INT auto_increment, uid int, datetim datetime, primary key(id));
INSERT INTO test (uid, datetim) VALUES (1, '2019-07-01');
INSERT INTO test (uid, datetim) VALUES (1, '2019-07-01');
INSERT INTO test (uid, datetim) VALUES (1, '2019-07-01');
INSERT INTO test (uid, datetim) VALUES (1, '2019-07-02');
INSERT INTO test (uid, datetim) VALUES (1, '2019-07-02');
INSERT INTO test (uid, datetim) VALUES (1, '2019-07-05');

可以测试的小提琴：DB Fiddle