我需要简化一个复杂的旧查询,以便筛选具有日期范围的is。
我有张table Tickets
以及 TicketNotes
.
我需要
列有当天票数的专栏
有票的一栏,有一天的特定音符
日期
旧的查询
SELECT SUM(IFNULL(qtickets.count, 0)) j, SUM(IFNULL(mtickets.count, 0)) m FROM (
SELECT
COUNT(tickets.id) COUNT,
DATE(tickets.date) DATE
FROM
tickets
WHERE
tickets.status = 'Closed' AND tickets.did = 7
AND MONTH(tickets.date) = MONTH( CURRENT_DATE - INTERVAL 1 MONTH )
AND YEAR(tickets.date) = YEAR( CURRENT_DATE - INTERVAL 1 MONTH )
GROUP BY
DATE(tickets.date)
) AS mtickets LEFT JOIN (
SELECT
1 AS COUNT,
DATE(tickets.date) DATE
FROM
ticketnotes
INNER JOIN tickets ON tickets.id = ticketnotes.ticketid
WHERE
ticketnotes.message LIKE '%https://xxxxx.net/help/tickets/%'
AND tickets.status = 'Closed'
AND tickets.did = 7
AND MONTH(tbltickets.date) = MONTH( CURRENT_DATE - INTERVAL 1 MONTH )
AND YEAR(tbltickets.date) = YEAR( CURRENT_DATE - INTERVAL 1 MONTH )
GROUP BY
DATE(tickets.date)
) AS qtickets ON (mtickets.date = qtickets.date)
目标是获得
Date | M | Q
===================
2020-04-01 | 1 | 1
2020-04-02 | 2 | 1
2020-04-03 | 5 | 2
...
2020-04-30 | 3 | 0
其中m是did=7当天的总关闭票,q是获得note.message的总关闭票。
我需要用date filter date的一个示例检查查询 BETWEEN '2020-04-01' AND '2020-04-30'
仍然得到正确的三列。
=======更新:
当我试图添加 AND DATE(tickets.date) BETWEEN DATE('2020-04-01') AND DATE('2020-04-30')
在gordon的回答中,我从主要查询中得到了其他结果数据。
查询:
SELECT
DATE(t.date),
COUNT(t.id) AS num_tickets,
(CASE WHEN COUNT(tn.ticketid) = 0 THEN 0 ELSE 1 END) AS num_with_message
FROM
tickets t
LEFT JOIN ticketnotes tn ON
tn.ticketid = t.id AND tn.message LIKE '%https://xxxxx.net/help/tickets/%'
WHERE
t.status = 'Closed' AND t.did = 7
AND DATE(t.date) BETWEEN DATE('2020-04-01') AND DATE('2020-04-30')
GROUP BY
DATE(t.date)
结果是 num_tickets
获取错误数据 num_ticket
没有连接。
有什么建议吗?
2条答案
按热度按时间ijxebb2r1#
你可以试着用一个类似ehere的案例
uurity8g2#
这回答了问题的原始版本。
你所描述的听起来像是
group by
与left join
. 然而,现在还不清楚你到底在找什么。我最好的猜测是: