查询帮助 [英] query help

问题描述

写这篇文章有点麻烦。如果你这么倾向并且有一个

的时刻，我真的很感激你的洞察力。


i具有相当于采购订单类型的设置。 ..一个描述性标题

与订单项相关联。我有3个表...标题，lineitem和

辅助。辅助表保存有关非标准的线条项的信息，如subbordinate

。每个表格

都有一个uniqueId列。 lineitem和辅助表也有一个

parentUniqueId列。总是至少有一个lineitem

parentUniqueId引用标题'的uniqueId。有时间，当一个

lineitem会引用另一个lineitem'的UniqueId。补充数据在
中，辅助表以相同的方式引用lineitems，除了

没有反身的自我加入。我知道一张图片胜过千言万语，但我认为这很简单。


这些表中的数据以xml格式发送通过Web服务到另一个使用完全不同的平台的
服务器。 Web服务以xml格式发回每条记录的

状态，确认哪些标题，

和/或失败。我一直在尝试编写的查询应该只是简单地从所有3个表中返回数据，因为一个合并的记录集已过滤

这样所有相关的记录都会被返回一个给定的，与
相关的标题或lineitem或辅助，如果确认为失败。


我现在拥有的东西是：


选择项目。*，

a.uniqueId auxId

来自

（

选择h.uniqueId poId，

l.uniqueId lineId

from lineItems l

加入标题h

on h .uniqueId = l.parentUniqueId

union

选择h.uniqueId poId，

child.uniqueId lineId

from lineItems child

join lineItems parent

on child.parentUniqueId = pa rent.uniqueId

加入标题h

on h.uniqueId = parent.parentUniqueId

）项目

左加入辅助a

on a.parentUniqueId = items.lineId


这会生成* all *数据的正确视图。我需要帮助

标准，以便如果任何相关项目的uniqueId被称为已解散

那么其他相关项目中的任何一项都应返回记录集。


感谢您的时间，想法和建议。


我

解决方案

？ （[e-mail]）写道：

我有一个相当于采购订单类型的设置......与订单项相关联的描述性页眉。我有3个表...标题，lineitem，
和辅助。辅助表保存诸如关于非标准的线条项的下属信息之类的信息。这些表中的每一个都有一个uniqueId列。 lineitem和辅助表也有一个
parentUniqueId列。始终至少有一个lineitem
parentUniqueId引用标题'的uniqueId。有时间
但是当一个lineitem引用另一个lineitem的UniqueId时。
辅助表中的补充数据以相同的方式引用lineitems，除了没有反身的自我加入。我知道一张图片值得千言万语，但我认为这很简单。

我们可以不用图片。但是，我们非常喜欢的是：


o所涉及表格的CREATE TABLE脚本（最好简化

以涵盖问题中必不可少的内容）

o带有INSERT数据的示例语句。

o给出样本的预期结果。


至少有两个原因我们喜欢这个：


1）通过将脚本复制并粘贴到查询分析器中，可以很容易地开发出经过测试的解决方案。

2）它有助于澄清你叙述中的含糊之处。


从问题来看，似乎有一些状态栏

某处，但我可以找不到文字。 （你不愿意使用

移位键并没有真正帮助。）而且我不介意看一个

的例子如何一个lineitem可以引用不同类型的父项。

-

Erland Sommarskog，SQL Server MVP， es *** *@sommarskog.se


SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/books.asp

抱歉，ejo ......


让'不要嘲笑表，除非所有表都有varchar（ 50）

列名为uniqueId。 lineitem和辅助表有一个名为parentUniqueId的额外

varchar（50）列。让我们说标题表'的数据

看起来像：


标题（uniqueId是fk到lineitem.parentUniqueId）：


uniqueId

A


lineitem表的数据如下：


lineitem（uniqueId由parentUniqueId自行加入或fk为辅助）：


uniqueId parentUniqueId

XA

YX

ZX


辅助表的数据如下：


辅助：


uniqueId parentUniqueId

LY

MZ


来自上一篇文章的查询将返回：


poId lineId auxId

AX

AYL

AZM


我想要只获得那些没有poId，lineId或
auxId的相关项目都在提供的值列表中。即如果列表是QRS，那么

以上记录*应该*显示。如果列表是L，例如，那么上面的数据都不会显示......因为L是poId，lineId或者auxId -

这是实际情况。

"？" < [e-mail]>在留言新闻中写道：ti ******************* @ fe07.lga ...

抱歉，ejo ...

让'不要嘲笑表，除非所有表都有一个名为uniqueId的
varchar（50）列。 lineitem和辅助表有一个名为parentUniqueId的
附加varchar（50）列。让我们说标题表'的
数据看起来像：


让'不是'说标题表'的数据看起来像'任何东西。


Erland关于提供真正的DDL的观点是，这里的人们可以再次使用
重新创建您的确切配置，设计查询并针对您进行测试

确切的架构，而不是他们对你的解释的最佳猜测。


如果你有商业原因（通常是这种情况）需要保持

您的架构秘密的详细信息，将DDL降低到最低限度

准确描述了您试图解决的问题。


header（uniqueId是fk to lineitem.parentUniqueId）：

uniqueId
这个lineitem表的数据如下：

lineitem（uniqueId由parentUniqueId自行加入，或者fk为
辅助）：
uniqueId parentUniqueId
XA
YX
ZX

辅助：

uniqueId parentUniqueId
LY
MZ

这个问题来自上一篇文章的y将会返回：

poId lineId auxId
AX
AYL
AZM

我想要只获得那些相关的项目，其中poId，lineId和
auxId都不在提供的值列表中。即如果列表是QRS，那么
上述记录*应该*显示。如果列表是L，例如，那么上面的数据都不会显示......因为L是poId，lineId或auxId -
这是实际情况。

having a spot of trouble writing this one. if you are so inclined and have a
moment, i''d really appreciate your insight.

i have what amounts to a purchase order type of setup...a descriptive header
associated with line items. i have 3 tables...header, lineitem, and
auxiliary. the auxiliary table holds information like subbordinate
information about lineitems that are "non-standard". each of these tables
has a uniqueId column. the lineitem and auxiliary tables have a
parentUniqueId column as well. there is always at least one lineitem whos
parentUniqueId references a header''s uniqueId. there are time though, when a
lineitem will reference another lineitem''s UniqueId. supplimental data in
the auxiliary table references lineitems in the same fashion except there is
no reflexive self-joining. i know a picture is worth a thousand words, but i
think this is pretty straight-forward.

the data in these tables is sent in xml format via web services to another
server that uses a completely different platform. the web service sends the
status of each record back in xml format confirming which headers,
lineitems, and auxiliary items (by uniqueId) were successfully transfered
and/or which failed. the query i have been trying to write should simply
return the data from all 3 tables as one consolidated record set filtered
such that all related records are returned as long as neither a given,
related header or lineitem or auxiliary where confirmed as failed.

the meat of what i have now is something like:

select items.* ,
a.uniqueId auxId
from
(
select h.uniqueId poId ,
l.uniqueId lineId
from lineItems l
join header h
on h.uniqueId = l.parentUniqueId
union
select h.uniqueId poId ,
child.uniqueId lineId
from lineItems child
join lineItems parent
on child.parentUniqueId = parent.uniqueId
join header h
on h.uniqueId = parent.parentUniqueId
) items
left join auxiliary a
on a.parentUniqueId = items.lineId

this generates the correct view of *all* the data. i need help with the
criterion so that if any related item''s uniqueId was said to be "defunct"
then NONE of the other related items should return in the recordset.

thanks for your time, thoughts, and advice.

me

解决方案

 ([e-mail]) writes:

i have what amounts to a purchase order type of setup...a descriptive
header associated with line items. i have 3 tables...header, lineitem,
and auxiliary. the auxiliary table holds information like subbordinate
information about lineitems that are "non-standard". each of these
tables has a uniqueId column. the lineitem and auxiliary tables have a
parentUniqueId column as well. there is always at least one lineitem
whos parentUniqueId references a header''s uniqueId. there are time
though, when a lineitem will reference another lineitem''s UniqueId.
supplimental data in the auxiliary table references lineitems in the
same fashion except there is no reflexive self-joining. i know a picture
is worth a thousand words, but i think this is pretty straight-forward.

We can do without the pictures. However, what we are very fond of are:

o CREATE TABLE scripts for the involved tables (preferrably simplified
to cover the essential in the problem)
o Sample statements with INSERT data.
o The desired result given that sample.

There are at least two reasons we like this:

1) By copying-and-pasting the scripts into Query Analyzer, it''s easy to
develop a tested solution.
2) It helps to clarify ambiguities in your narrative.

Judging from the problem it appears that there is some status column
somewhere, but I could not find it the text. (Your reluctance to use
the shift key does not really help.) And I wouldn''t mind to see an
example how a lineitem can refer to different sort of parent items.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

sorry, ejo...

let''s not trifle over the tables save that the all tables have a varchar(50)
column called uniqueId. the lineitem and auxiliary tables have an additional
varchar(50) column called parentUniqueId. let''s say the header table''s data
looks like:

header (uniqueId is fk to lineitem.parentUniqueId):

uniqueId
A

the lineitem table''s data looks like:

lineitem (uniqueId is self-joined by parentUniqueId or an fk to auxiliary):

uniqueId parentUniqueId
X A
Y X
Z X

the aux table''s data looks like:

auxiliary:

uniqueId parentUniqueId
L Y
M Z

the query from the previous post would return:

poId lineId auxId
A X
A Y L
A Z M

i''m trying to get only those related items where neither poId, lineId, nor
auxId are in a list of supplied values. i.e. if the list was "QRS", then the
above records *should* show. if the list was "L" for example, then none of
the data above should show...since L is either a poId, lineId, or auxId -
which is the actual case here.

"" <[e-mail]> wrote in message news:ti*******************@fe07.lga...

sorry, ejo...

let''s not trifle over the tables save that the all tables have a varchar(50) column called uniqueId. the lineitem and auxiliary tables have an additional varchar(50) column called parentUniqueId. let''s say the header table''s data looks like:
Let''s not "say the header table''s data looks like" anything.

Erland''s point about providing a real DDL is that folks here can then
recreate your exact configuration, design queries and test them against your
exact schema, not their best guess of your interpretation.

If you have business reasons (as is often the case) for needing to keep
details of your schema secret, break the DDL down to the bare minimum that
accurate describes the problm you''re trying to solve.


header (uniqueId is fk to lineitem.parentUniqueId):

uniqueId
A

the lineitem table''s data looks like:

lineitem (uniqueId is self-joined by parentUniqueId or an fk to auxiliary):
uniqueId parentUniqueId
X A
Y X
Z X

the aux table''s data looks like:

auxiliary:

uniqueId parentUniqueId
L Y
M Z

the query from the previous post would return:

poId lineId auxId
A X
A Y L
A Z M

i''m trying to get only those related items where neither poId, lineId, nor
auxId are in a list of supplied values. i.e. if the list was "QRS", then the above records *should* show. if the list was "L" for example, then none of
the data above should show...since L is either a poId, lineId, or auxId -
which is the actual case here.

这篇关于查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！