时间戳

首页 > 解决方案 > 使用来自一个查询的（多个）结果作为另一个查询的输入

sql - 使用来自一个查询的（多个）结果作为另一个查询的输入

问题描述

我正在使用 Vb.net 和 SQL 编写票务程序

我有 2 个表需要在查询中使用。一张表（Tickets）包含我需要的所有值。第二个表 (Ticketactions) 包含链接到 Ticket 的所有 TicketActions

我想要的是创建一个搜索，搜索以下列：

我有一个运行良好的搜索并搜索门票表

        SELECT 
    tickets.TicketNumber, 
    tickets.Description,
    tickets.departmentname,
    tickets.devicename,
    tickets.creatorname,
    tickets.creationdate,
    tickets.ExecutionDate,
    tickets.closedate
    stateName,
    PriorityName,
    FullName,
         FROM tickets
    INNER Join Users On Tickets.AssignedTo = Users.UserID
    INNER Join States On Tickets.StateID = States.StateID 
    INNER Join Priorities On Tickets.priority = Priorities.PriorityID 
         WHERE Description Like '%SEARCHTEXT%' OR TicketNumber Like '%SEARCHTEXT%'

我想要的是用额外的 TicketNumbers 扩展上面的查询，这些 TicketNumbers 是 TicketActions 表中查询的结果

    SELECT TicketNumber
    FROM dbo.ticketaction
    WHERE ActionDescription like '%SEARCHTEXT%'

因此结果应该与第一个查询完全相同，但添加了第二个查询找到的票号。

任何帮助都会非常受欢迎，因为我真的不知道如何解决这个问题

标签： sqlsql-servermultiple-results

解决方案

要回答你的字面问题，这将是......

SELECT 
    tickets.TicketNumber, 
    tickets.Description,
    tickets.departmentname,
    tickets.devicename,
    tickets.creatorname,
    tickets.creationdate,
    tickets.ExecutionDate,
    tickets.closedate
    stateName,
    PriorityName,
    FullName,
FROM
           tickets
INNER Join Users      On Tickets.AssignedTo = Users.UserID
INNER Join States     On Tickets.StateID = States.StateID 
INNER Join Priorities On Tickets.priority = Priorities.PriorityID 
WHERE tickets.Description Like '%SEARCHTEXT%'
   OR tickets.TicketNumber Like '%SEARCHTEXT%'
   OR tickets.TicketNumber IN (SELECT TicketNumber
                                 FROM dbo.ticketaction
                                WHERE ActionDescription like '%SEARCHTEXT%'
                              )


更好的解决方案，可能是JOIN在ticketAction桌子上使用

SELECT 
    tickets.TicketNumber, 
    tickets.Description,
    tickets.departmentname,
    tickets.devicename,
    tickets.creatorname,
    tickets.creationdate,
    tickets.ExecutionDate,
    tickets.closedate
    stateName,
    PriorityName,
    FullName,
FROM
           tickets
INNER JOIN Users        ON tickets.AssignedTo = Users.UserID
INNER JOIN States       ON tickets.StateID = States.StateID 
INNER JOIN Priorities   ON tickets.priority = Priorities.PriorityID 
INNER JOIN ticketAction ON ticket.TicketNumber = ticketAction.TicketNumber
WHERE tickets.Description Like '%SEARCHTEXT%'
   OR tickets.TicketNumber Like '%SEARCHTEXT%'
   OR ticketAction.ActionDescription LIKE '%SEARCHTEXT%'

（假设每张票都有一个 ticketAction。如果不是这种情况，请使用 a LEFT JOIN）


或者，EXISTS()...

SELECT 
    tickets.TicketNumber, 
    tickets.Description,
    tickets.departmentname,
    tickets.devicename,
    tickets.creatorname,
    tickets.creationdate,
    tickets.ExecutionDate,
    tickets.closedate
    stateName,
    PriorityName,
    FullName,
FROM
           tickets
INNER Join Users      On Tickets.AssignedTo = Users.UserID
INNER Join States     On Tickets.StateID = States.StateID 
INNER Join Priorities On Tickets.priority = Priorities.PriorityID 
WHERE tickets.Description Like '%SEARCHTEXT%'
   OR tickets.TicketNumber Like '%SEARCHTEXT%'
   OR EXISTS (SELECT *
                FROM dbo.ticketaction
               WHERE ActionDescription like '%SEARCHTEXT%'
                 AND TicketNumber = tickets.TicketNumber
             )

推荐阅读