如何简化此查询以不终止SQL Server

Question

我的任务是在数据库中查找几个符合特定条件的票证。但我认为我已经到了SQL所能处理的极限。下面有一个问题，因为所有属于"1646"公司的门票都像是该客户的12000多张门票。。。

所以一开始我想到了一个内部联接语句，但后来我开始头疼，因为事情一直在循环。下面是试图运行的脚本，但随后我的数据库开始冒烟。。。并且存在读/写问题。滞后变得非常真实，很明显是我提出了这个查询。罪魁祸首：

SELECT
s.SR_Service_RecID as 'Ticket #',
t.Description as 'Description',
s.Summary as 'Summary Description',
d.Date_Created as 'Entered',
b.Board_Name as 'Board',
c.Company_ID as 'Company',
q.Description as 'Status',
p.SR_Severity_Name as 'Priority',
a.Description as 'Type',
z.Description as 'SubType',
s.Date_Closed as 'Closed'
d.SR_Detail_Notes_Markdown as 'Notes',
From
dbo.SR_Service as s,
dbo.SR_Type as t,
dbo.SR_Detail as d,
dbo.SR_Board as b,
dbo.Company as c,
dbo.SR_Status as q,
dbo.SR_Severity as p,
dbo.SR_Type as a,
dbo.SR_Subtype as z
WHERE
s.Company_RecID like '1646'
and s.Entered_By not like 'zadmin'
and s.Updated_By not like 'zadmin'
and s.Entered_By not like 'RESTAPI'
and s.Updated_By not like 'RESTAPI'
and s.Entered_By not like 'techautomate'
and s.Updated_By not like 'techautomate'
and s.Entered_By not like 'template%'
and s.Updated_By not like 'template%'
and s.Entered_By not like 'HelpDesk'
and s.Updated_By not like 'HelpDesk'
and s.Entered_By not like 'Email Connector'
and s.Updated_By not like 'Email Connector'
and d.SR_Detail_Notes_Markdown not like '%Assigned%'
ORDER BY
s.Date_Entered ASC;`

如何将其细化为更好的查询或更改8个内部联接以使其工作？如何制作的SQL脚本？

首先是他的友好名称，然后是主键和外键关系：

ticket number=Service_RecID是dbo中的主键。dbo中的服务注册表项。详细信息

ticket type=SR_type_RecID是dbo.type中的主键。dbo.Service的外键。但我需要数据库中的id的描述。type。

摘要描述=位于dbo中。SR_服务列标题为摘要

Entered(是输入票证的日期)=位于dbo中。列标题Date_created下的SR_Detail。

板(是分配给服务板票证的)=dbo中的SR_Board_RecID主键。SR_Service中的SR_Board外键。但我需要位于dbo.SR_Board.中的列标题Board_Name

Company=数据库中的Company_RecID主键。Config，但dbo中的外键。SR_服务

状态=SR_Status_RecID主键dbo。SR_Status外键位于dbo中。SR_服务

优先级=dbo的SR_Serity_RecID主键。SR_Serity外键位于dbo中。SR_服务。但是我需要SR_Sverity_Name，它是dbo中的列标题。SR_真实性

type=dbo中的SR_type_RecID主键。类型与dbo共享外键。服务但我需要与dbo中的SR_Type_RecID相关联的描述。SR_类型

subtype=SR_subtype_RecID主键位于dbo中。SR_subtype，共享的外键位于dbo下。SR_service。但我再次需要数据库下的描述。SR_subtype.

closed=因为它位于dbo中。SR_列标题下的服务date_closed

notes=另一方面位于dbo中。列标题SR_Detail_Notes_Markdown下的详细信息，它在数据库中共享的唯一密钥是外键SR_Service_Rec_ID

我似乎无法使INNER JOIN语句正常工作。

INNER JOIN dbo.SR_Service.Service_RecID on dbo.SR_Detail.Service_RecID
INNER JOIN dbo.Type.SR_Type_RecID on dbo.SR_Service.SR_Type_RecID
INNER JOIN dbo.Type.Description on dbo.SR_Service.Type_Description
INNER JOIN dbo.SR_Board.SR_Board_RecID on dbo.Service.SR_BoardRecID
INNER JOIN dbo.Config.Company_RecID on dbo.Service.Company_RecID
INNER JOIN dbo.SR_Status.SR_Status_RecID on dbo.Service.SR_Status_RecID
INNER JOIN dbo.SR_SubType.SR_Type_RecID on dbo.Type.SR_Type_RecID

我想这就是我声明FROM声明的方式。。。

我可以不只是运行单一的数据库查询，并将结果转储和附加到一个新的数据库对象吗？

Answer 1

JOIN条件是一个表如何基于公共列与另一个表相关联。除了你的具体问题，想想订单。

订单是由客户发出的。订单有详细信息，订单详细信息产品来自产品表。

话虽如此，你可能想要类似的东西

select
c.customername,
o.orderdate,
od.qty,
p.productname
from
customer c
join orders o
on c.customerid = o.customerid
join orderDetails od
on o.orderid = od.orderid
join products p
on od.productid = p.productid

您在原始问题中提供的编辑反馈

既然您已经提供了各自的主键和外键，我们可以进一步提供帮助。首先，不要试图用空格或像'Ticket #'这样的特殊字符来命名列。通常在'CamelCaseWhereUpperPerWord'中更容易命名为可读的。2只是一个例子。通过任何方法的输出都应该担心输出中此类头列的格式问题。

接下来，当应用过滤器时，比如你的公司编号，如果它是一个数字，不要把它放在引号里，把它留作一个数字进行相等性测试。在这种情况下，您正在寻找单人票公司=1646。我会确保服务表上的索引包括该列作为其第一个位置的索引。

根据您编辑的PK/FK，我修改了以下查询，并嵌入了注释

SELECT 
-- per ticket found
s.SR_Service_RecID TicketNumber,
-- dont worry about renaming Summary AS SummaryDescription,
-- let the OUTPUT process add whatever column headers is more common approach
-- just get the column and get it to work first, then fine-tune it
s.Summary,
s.Date_Closed Closed,
-- dont worry for now about specific column order, I am trying to match
-- the hierarchy of data trying to acquire.  You want for a specific company,
-- so I am just putting that up front for now.
c.Company_ID Company,
-- description from the TYPE table
t.Description type,
-- pull from sub-type of the type
st.Description SubType,
-- now I can pull columns from the SR_Detail table
d.Date_Created Entered,
d.SR_Detail_Notes_Markdown Notes,
-- now any columns from the SR_Board table
b.Board_Name Board,
-- columns from the status table
q.Description Status,
p.SR_Severity_Name Priority
From 
-- or is this table SUPPOSED to be Service vs SR_Service
SR_Service as s
-- first, joining to the config table so you can get the descriptive company "name", such as your "Company_ID"
JOIN Config as c
on s.Company_RecID = c.Company_RecID
-- now join the outer hierarchy SR_Service to the SR_Type on its PK/FK relationship
join SR_Type as t
on s.SR_Type_RecID = t.SR_Type_RecID
-- changing the subtype to a LEFT-JOIN in case a sub-type does not exist
LEFT JOIN SR_Subtype as st
on s.SR_SubType_RecID = st.SR_SubType_RecID
-- now join the outer hierarchy SR_Service to the SR_Detail on its PK/FK relationship
-- or is this table SUPPOSED to be Detail vs SR_Detail
JOIN SR_Detail as d
on s.Service_RecID = d.Service_RecID
-- doing a LEFT-JOIN since an entry may NOT be assigned to a board (yet).
-- otherwise the ticket will be excluded from final list if no such board assignment
LEFT JOIN SR_Board as b
on s.SR_Board_RecID = b.SR_Board_RecID
-- etc., similar joins to each other lookup table for clear descriptions based on given PK/FK relationship
JOIN SR_Status as q
on s.SR_Status_RecID = q.SR_Status_RecID
JOIN SR_Severity as p
on s.SR_Severity_RecID = p.SR_Severity_RecID
WHERE 
-- NOW, you can apply your filtering conditions here as you have them
s.Company_RecID = 1646
and s.Entered_By not like 'zadmin'
and s.Updated_By not like 'zadmin'
and s.Entered_By not like 'RESTAPI'
and s.Updated_By not like 'RESTAPI'
and s.Entered_By not like 'techautomate'
and s.Updated_By not like 'techautomate'
and s.Entered_By not like 'template%'
and s.Updated_By not like 'template%'
and s.Entered_By not like 'HelpDesk'
and s.Updated_By not like 'HelpDesk'
and s.Entered_By not like 'Email Connector'
and s.Updated_By not like 'Email Connector'
and d.SR_Detail_Notes_Markdown not like '%Assigned%'
ORDER BY 
s.Date_Entered ASC;

现在您已经有了一个完整的查询，将来，一次取一个。看看你想要什么。与一个客户关联的所有票证。对此提出质疑。当你有一个表(或别名)时，在继续之前，先从该表中获得你期望的所有列

select
s.SR_Service_RecID TicketNumber,
s.Summary,
s.Date_Closed Closed
from
SR_Service s
where
s.Company_RecID = 1646

太好了，这能让你立即得到门票的效果。但现在你想要更多的东西。所以，一次一张桌子，做一个连接。第二张表与第一张表的关系如何。我总是试图在左边(或第一个位置)列出我的主要表(在这种情况下是你的服务表)，并根据FK/PK关系加入第二个表(右边)。在这种情况下，您希望在Config表中清除公司名称。因此，根据联接本身获取额外的列。

select
s.SR_Service_RecID TicketNumber,
s.Summary,
s.Date_Closed Closed,
-- and now columns from next table
c.Company_ID Company
from
SR_Service s
JOIN Config as c
on s.Company_RecID = c.Company_RecID
where
s.Company_RecID = 1646

继续一次添加一个表，每次添加一个WHERE子句，直到完成整个查询。在SQL Server中，通过执行

SELECT TOP 10 (rest of query)

将导致列表只有10条记录。通过这种方式，您可以对"；此查询是否按编写的方式工作"你得到答案了吗？你已经把命令搞砸了吗？尽早修复，一次修复一块。检索到的每列之间的列。我在这两个表之间有合适的JOIN条件吗？然后继续前进。

希望这个数据库场景的详细示例能帮助您更轻松地编写查询。另请参阅如何更好地列出表结构(所有相关列)，以帮助获得更好的响应，而不是我们试图猜测表中的列名。

Answer 2

正如Martin Smith所说，您需要定义表联接。按照现在的查询，任何结果都是无用的。

要做到这一点，您必须首先了解数据库模式。更具体地说，实体(=表)之间的关系。为此，您需要了解和理解业务域以及它在表中的表示方式。

如果不了解业务领域，此站点的SQL Server专家将无法为您执行此操作。

完成上述操作后，您可以从数据库模式中推断出正确的联接，并相应地改进查询草稿。