mysql - 根据条件计算另一个表的总结果减去原始表列的数字

Question

我担心我离开 mysql 太久了，我正在掉进兔子洞。

客户端 session

id      cid      sessions      date
------------------------------------------
1       102          5       01/07/2017
2       102          3       01/08/2017
3       141          5       29/07/2017

用户 102 自 2017 年 1 月 7 日起有 5 个可用 session id - 1用户 102 自 2017 年 1 月 8 日起有 3 个可用 session id - 2

client_sessions_history

id      sid      date
----------------------------
1       1      03/07/2017
2       1      07/07/2017
3       1      14/07/2017
4       1      21/07/2017
5       1      27/07/2017
6       2      01/08/2017

sid是client_sessions中的id，这里有5个为1此处为 2 中的 1。

我想要的结果是:

date            remaining
-----------------------------
01/07/2017          0
01/08/2017          2

这将是 client_sessions 中按日期唯一的 session 减去 client_sessions_history 中按 sid 唯一的 session 总数

我已经尝试过以下方法，但我一直在兜圈子。

select a.sessions, a.id, (select count(distinct sid) from client_sessions_history where b.sid = a.id) as remaining from client_sessions as a, client_sessions_history as b where a.cid = 102

基本上我想获得 cid 102 的剩余总数

Answer 1

创建表/插入数据

CREATE TABLE client_sessions
    (`id` INT, `cid` INT, `sessions` INT, `date` VARCHAR(10))
;

INSERT INTO client_sessions
    (`id`, `cid`, `sessions`, `date`)
VALUES
    (1, 102, 5, '01/07/2017'),
    (2, 102, 3, '01/08/2017'),
    (3, 141, 5, '29/07/2017')
;


CREATE TABLE client_sessions_history
    (`id` INT, `sid` INT, `date` VARCHAR(10))
;

INSERT INTO client_sessions_history
    (`id`, `sid`, `date`)
VALUES
    (1, 1, '03/07/2017'),
    (2, 1, '07/07/2017'),
    (3, 1, '14/07/2017'),
    (4, 1, '21/07/2017'),
    (5, 1, '27/07/2017'),
    (6, 2, '01/08/2017')
;

您需要创建一个交付的表，在其中计算客户端历史 session 数。

查询

SELECT 
    client_sessions.date
 , (client_sessions.sessions - client_sessions_grouped.client_sessions_history_sid_count) AS remaining
FROM ( 

  SELECT 
      client_sessions.id
    , COUNT(client_sessions_history.sid) AS client_sessions_history_sid_count
   FROM 
    client_sessions
   INNER JOIN 
     client_sessions_history
   ON
     client_sessions.id =  client_sessions_history.sid 
   GROUP BY
    client_sessions.id 
) 
 AS
   client_sessions_grouped
INNER JOIN
  client_sessions
ON
  client_sessions.id = client_sessions_grouped.id

WHERE
  client_sessions.cid = 102  

结果

date        remaining  
----------  -----------
01/07/2017            0
01/08/2017            2