合作 基于相关表中的查询结果的查询结果

Question

我正在查询所有可用记录的表。结果具有一个名为 ticket_id 的主键

<cfquery name="get_all_active_tickets">
SELECT *
FROM service_ticket
where technician_id != <CFQUERYPARAM Value="#techID#"> AND technician_name != "" 
</cfquery>

查询返回 42 条唯一记录，每条记录对列ticket_id都有一个唯一的值。

然后，我想在另一个表中查询所有也包含主键ticket_id的记录，并在这些结果中为这些相关记录service_qty SUM 一列求和。我的目标是将包含相同ticket_id值的记录的所有 service_qty 值相加，并比较这些值。

我正在尝试这样做：

<cfset IDs = valueList(get_all_active_tickets.ticket_id) >
<cfquery name="high_hours_tickets">
select SUM(service_qty) as total, ticket_id
from service_ticket_detail
where ticket_id in (<cfqueryparam cfsqltype="cf_sql_integer" value="#IDs#" list="true">)
</cfquery>

然而，结果并不像预期的那样。查询"high_hours_tickets"的记录计数仅为 1，看起来它正在汇总返回的所有记录的列"service_qty"，而不仅仅是具有相同ticket_id值的记录。

我该如何解决这个问题？谢谢。

---

一个有效的解决方案，感谢Ageax在下面的贡献：

<cfset IDs = valueList(get_all_active_tickets.ticket_id) >
<cfquery name="high_hours_tickets" datasource="#datasource#">        
SELECT t.ticket_id, SUM( d.service_qty) AS totalQty
FROM   service_ticket t 
INNER JOIN service_ticket_detail d ON d.ticket_id = t.ticket_id
WHERE   t.technician_id <> <cfqueryparam value="#techID#" cfsqltype="cf_sql_integer"> 
AND     t.technician_name <> "" 
GROUP BY t.ticket_id
HAVING  SUM( d.service_qty) > 2
</cfquery>
<cfoutput><cfset lstIDs = ValueList(high_hours_tickets.ticket_id) /></cfoutput>
<cfquery name="flagged_tickets" datasource="#datasource#">
select * 
from service_ticket
where ticket_id IN ( 
<cfqueryparam 
value="#lstIDs#" 
cfsqltype="CF_SQL_INTEGER"
list="yes" 
/> 
)
</cfquery>

Answer 1

更新：

由于对表结构和期望的结果存在一些混淆，因此我根据您的描述整理了一个SQL小提琴。它是共享架构和示例数据的绝佳工具，有助于避免大量查询问题：-( 我不知道您使用的是哪个版本的MySQL，因此我将假设旧版本以获得最佳兼容性。

如果我理解正确，目标是显示所有service_ticket记录，总体service_qty> 2。 为此，请使用子查询对service_qty值求和，按ticket_id，并删除任何 <= 2 的值。然后将这些结果连接回主票证表以获取详细信息(姓名、地址等(。

SQL 小提琴

查询：

SELECT t.ticket_id
, t.address
, t.name
-- ... additional columns
, total.qty
FROM   service_ticket t INNER JOIN 
(
SELECT   ticket_id
, SUM( service_qty) AS Qty
FROM   service_ticket_detail 
GROUP BY ticket_id
HAVING  Qty > 2
) 
total ON total.ticket_id = t.ticket_id
WHERE   t.technician_id <> <cfqueryparam value="#techID#" cfsqltype="cf_sql_integer"> 
AND     t.technician_name <> '' 

---

源语言：

忘记单独的查询。这是 JOIN 的工作。它们就是为此目的而设计的。

SELECT t.ticket_id, SUM( d.service_qty) AS totalQty
FROM   service_ticket t 
INNER JOIN service_ticket_detail d ON d.ticket_id = t.ticket_id
WHERE   t.technician_id <> <cfqueryparam value="#techID#" cfsqltype="cf_sql_integer"> 
AND     t.technician_name <> ''
GROUP BY t.ticket_id
HAVING  SUM( d.service_qty) > 2

IMO，JOIN是不可或缺的。如果您不熟悉它们，我强烈建议您阅读一些有关 JOIN 的介绍性教程。你会很高兴你做到了。