这是我的SQL Fiddle
我现在正在加入user
和address
表。现在,我需要记录填充in_time
和out_time
的日志表的计数
这是我到目前为止的SQL查询
select u.id, u.name, a.address from user u
left join address a on u.id = a.user_id
where u.id = 1
即输出应该是这样的
id name address total_count proper_count
1 Alpha Chennai 4 3
最佳答案
使用以下架构设计来满足您的需求。 User_id必须为INTEGER,并在输入和输出时间列中使用datetime数据类型。
CREATE TABLE log (
id BIGINT,
user_id BIGINT,
in_time datetime,
out_time datetime
);
INSERT INTO log (id,user_id, in_time, out_time) VALUES (1,1,'2018-07-21 06:50:41','2018-07-21 10:50:41');
INSERT INTO log (id,user_id, in_time, out_time) VALUES (2,1,'2018-07-22 06:50:41','2018-07-22 10:50:41');
INSERT INTO log (id,user_id, in_time) VALUES (3,1,'2018-07-23 06:50:41');
INSERT INTO log (id,user_id, in_time, out_time) VALUES (4,1,'2018-07-24 06:50:41','2018-07-22 10:50:41');
select u.id as user_id, u.name, a.address, COUNT(in_time) AS total_count, COUNT(out_time) as proper_count
from log l
INNER JOIN user u on u.id = l.user_id
INNER JOIN address a on a.user_id = u.id
GROUP BY u.id , u.name, a.address
关于mysql - SQL联接并在表中计数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52049471/