jquery datatable:使用左联接和where子句的计数记录

我有两张桌子,“团队”和“门票”。现在它显示了所有的记录以及团队成员被发送的票证。

我现在需要计算每个队的票数。

我的尝试是:

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 )

更新

示例,但只缺少count列:

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_openservicescalls中有记录,其中sc_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;

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;

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