jquerydatatable：使用left join和where子句统计记录

wvmv3b1j 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(142)

我有两张table，“队”和“票”。现在它显示了所有的记录，也显示了团队成员被发送的罚单。
我现在要数一数每队的票数。
http://sqlfiddle.com/#!2018年9月609d4f
以下是我的尝试：

SELECT
    team.techid,
    team.name,
    tickets.techid,
    tickets.customer,
    tickets.callstatus,
    tickets.serialnumber
FROM team
LEFT JOIN tickets
    ON tickets.techid = team.techid AND (tickets.callstatus = 'Dispatch') AND 
(COUNT(tickets.customer) WHERE tickets.techid = team.techid )

更新
工作示例，但仅缺少计数列：
http://sqlfiddle.com/#!9/609d4f/19号
更新2
蒂姆，谢谢你的帮助，但你的例子行不通。
表应该是这样的，当然，减去缺少的列：

|---------------------|------------------|
|      Tech ID        |     Count        |
|---------------------|------------------|
|         Tech1       |      1           |
|---------------------|------------------|
|         Tech2       |      1           |
|---------------------|------------------|
|         Tech3       |      0           |
|---------------------|------------------|

http://sqlfiddle.com/#!9/bfcdf5/1号
如下所示，tech1和tech2在ato\u openservicecalls中都有记录，其中sc\u callstatus为dispatch

insert into `serviceteam` VALUES (1, 'tech1', 'name1', 'manager1', 'dispatcher1', 'cellphone1');
insert into `serviceteam` VALUES (2, 'tech2', 'name2', 'manager2', 'dispatcher2', 'cellphone2');
insert into `serviceteam` VALUES (3, 'tech3', 'name3', 'manager3', 'dispatcher3', 'cellphone3');

insert into `ato_openservicecalls` VALUES (1, 'tech1', 'Dispatch', 'customer1', 'age1', 'timestamp1', 'serial1', 'comment1');
insert into `ato_openservicecalls` VALUES (2, 'tech2', 'Dispatch', 'customer2', 'age2', 'timestamp2', 'serial2', 'comment2');
insert into `ato_openservicecalls` VALUES (3, 'tech3', 'callstatus3', 'customer3', 'age3', 'timestamp3', 'serial3', 'comment3');

SELECT
t1.techid,
t1.techname,
t1.manager,
t1.dispatcher,
t1.cellphone,
t2.SC_SCTechID,
t2.BCARNA,
t2.SC_CallStatus,
t2.Serial_ID,
t2.Age,
t2.SC_CallTimestamp,
t2.SC_CallComment,
COALESCE(t3.num_tickets, 0) AS num_tickets
FROM serviceteam t1
LEFT JOIN ato_openservicecalls t2
    ON t1.techid = t2.SC_SCTechID AND t2.SC_CallStatus = 'Dispatch'
    LEFT JOIN
(
    SELECT t1.techid, COUNT(*) AS num_tickets
    FROM serviceteam t1
    INNER JOIN ato_openservicecalls t2
        ON t1.techid = t2.SC_SCTechID
    WHERE t2.SC_CallStatus = 'Dispatch'
) t3
    ON t1.techid = t3.techid;

mysql

来源：https://stackoverflow.com/questions/52940754/jquery-datatable-count-records-with-left-join-and-where-clause

1条答案

按热度按时间

pengsaosao1#

在早于8+的mysql版本中，我们可以使用子查询找到计数，然后加入到其中：

SELECT
    t1.techid,
    t1.name,
    t2.techid,
    t2.customer,
    t2.callstatus,
    t2.serialnumber,
    COALESCE(t3.num_tickets, 0) AS num_tickets
FROM team t1
LEFT JOIN tickets t2
    ON t1.techid = t2.techid AND t2.callstatus = 'Dispatch'
LEFT JOIN
(
    SELECT t1.techid, COUNT(*) AS num_tickets
    FROM team t1
    INNER JOIN tickets t2
        ON t1.techid = t2.techid
    WHERE t2.callstatus = 'Dispatch'
) t3
    ON t1.techid = t3.techid;

使用mysql 8+或更高版本，我们可以利用分析功能：

SELECT
    t1.techid,
    t1.name,
    t2.techid,
    t2.customer,
    t2.callstatus,
    t2.serialnumber,
    COUNT(t2.customer) OVER (PARTITION BY t1.techid) num_tickets
FROM team t1
LEFT JOIN tickets t2
    ON t1.techid = t2.techid AND t2.callstatus = 'Dispatch';

编辑：
你完全改变了你的问题，使我第一次接受的答案无效。新查询如下：

SELECT
    t1.techid,
    COUNT(t2.customer) AS num_tickets
FROM team t1
LEFT JOIN tickets t2
    ON t1.techid = t2.techid AND t2.callstatus = 'Dispatch'
GROUP BY
    t1.techid;

赞(0）回复(0）举报 2021-06-20

我来回答

jquerydatatable：使用left join和where子句统计记录

1条答案

相关问题

热门标签

最新问答