mysql - SQL 子选择计数和组

Question

我需要提取一个月中的所有日期，按工作日对它们进行计数和分组。以下是数据库示例:

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)

Answer 1

您应该使用窗口函数:

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');

以及可以测试它的 fiddle :DB Fiddle