请求帮助创建SQL查询 [英] Plea for help creating SQL query
问题描述
我正在尝试进行连接两个表的查询。诀窍是我只需要它根据一定的标准返回行。
表1：库存
字段：库存＃，描述
表2：价格
字段：库存＃，价格，有效年份，有效月份，有效
天
我想返回一行给我库存＃，描述和
最新价格。我不在乎价格的生效日期
是多少，但我需要它是最新的。
我一直在想这条线：
SELECT Inventory.InvNo，Inventory.Description，Price.Price
来自库存，价格
WHERE EffYear =（SELECT MAX（EffYear）FROM价格在哪里price.InvNo =
Inventory.InvNo）
AND Inventory.InvNo = Prices.InvNo
但复杂的是，同年可能有2个不同的价格来自
。如果他们没有将日期设置为3个不同的领域
那么它就会简单得多。任何想法都非常受欢迎！
" Susan M." <苏****** @ hotmail.com>在消息中写道
新闻：c5 ************************* @ posting.google.co m ... < blockquote class =post_quotes>我正在尝试进行连接两个表的查询。诀窍是我只希望它根据一定的标准返回行。
表1：库存
字段：库存＃，描述
表2：价格
字段：库存＃，价格，有效年份，有效月份，有效
我想返回一行给我库存＃，描述，以及
最新价格。我不在乎价格的生效日期是什么，但我需要它是最新的。
我在想这个：
SELECT Inventory.InvNo，Inventory.Description，Price.Price
来自库存，价格
WHERE EffYear =（选择MAX（EffYear）来自哪里有Price.InvNo =
库存.InvNo）
AND Inventory.InvNo = Price.InvNo
但复杂的是，同年可能会有两种不同的价格。如果他们没有在3个不同的领域设置日期
那就简单得多了。任何想法都非常受欢迎！
您可以这样做：
WITH NewPrices AS（SELECT InvNo，Price ，日期（RTRIM（CHAR（EffYear））||'' - ''||
RTRIM（CHAR（EffMonth））||'' - ''|| RTRIM（CHAR（EffDay）） ）作为价格的EffDate）
SELECT Inventory.InvNo，Inventory.Description，NewPrices.Price
来自库存，NewPrices
WHERE EffDate =（ SELECT MAX（EffDate）FROM NewPrices WHERE NewPrices.InvNo =
Inventory.InvNo）
AND Inventory.InvNo = NewPrices.InvNo;
基本上，WITH子句构建一个临时表，其中包含Price表的所有
列，除了EffYear / EffMonth / EffDay字段
被转换为a单个DATE值将使您的查询中的比较逻辑
按照您希望的方式工作。
请注意，此查询不一定要进行快，特别是如果你的
价格表中有很多行。
如果你定期做这样的查询，你可能需要
想要查看使用更好的日期格式重建Price表格
（即使用单个DATE列），或创建MQT（物化查询表）
$ b在价格表上进行日期转换的$ b。
-
Matt Emmerton
DB2 OLTP性能
IBM多伦多实验室
" Susan M." <苏****** @ hotmail.com>在消息中写道
新闻：c5 ************************* @ posting.google.co m ... < blockquote class =post_quotes>我正在尝试进行连接两个表的查询。诀窍是我只希望它根据一定的标准返回行。
表1：库存
字段：库存＃，描述
表2：价格
字段：库存＃，价格，有效年份，有效月份，有效
我想返回一行给我库存＃，描述，以及
最新价格。我不在乎价格的生效日期是什么，但我需要它是最新的。
我在想这个：
SELECT Inventory.InvNo，Inventory.Description，Price.Price
来自库存，价格
WHERE EffYear =（选择MAX（EffYear）来自哪里有Price.InvNo =
库存.InvNo）
AND Inventory.InvNo = Price.InvNo
但复杂的是，同年可能会有两种不同的价格。如果他们没有在3个不同的领域设置日期
那就简单得多了。任何想法都非常受欢迎！
您可以这样做：
WITH NewPrices AS（SELECT InvNo，Price ，日期（RTRIM（CHAR（EffYear））||'' - ''||
RTRIM（CHAR（EffMonth））||'' - ''|| RTRIM（CHAR（EffDay）） ）作为价格的EffDate）
SELECT Inventory.InvNo，Inventory.Description，NewPrices.Price
来自库存，NewPrices
WHERE EffDate =（ SELECT MAX（EffDate）FROM NewPrices WHERE NewPrices.InvNo =
Inventory.InvNo）
AND Inventory.InvNo = NewPrices.InvNo;
基本上，WITH子句构建一个临时表，其中包含Price表的所有
列，除了EffYear / EffMonth / EffDay字段
被转换为a单个DATE值将使您的查询中的比较逻辑
按照您希望的方式工作。
请注意，此查询不一定要进行快，特别是如果你的
价格表中有很多行。
如果你定期做这样的查询，你可能需要
想要查看使用更好的日期格式重建Price表格
（即使用单个DATE列），或创建MQT（物化查询表）
$ b在价格表上进行日期转换的$ b。
-
Matt Emmerton
DB2 OLTP性能
IBM多伦多实验室
" Susan M." <苏****** @ hotmail.com>在消息中写道
新闻：c5 ************************* @ posting.google.co m ... < blockquote class =post_quotes>我正在尝试进行连接两个表的查询。诀窍是我只希望它根据一定的标准返回行。
表1：库存
字段：库存＃，描述
表2：价格
字段：库存＃，价格，有效年份，有效月份，有效
我想返回一行给我库存＃，描述，以及
最新价格。我不在乎价格的生效日期是什么，但我需要它是最新的。
试试
SELECT I.InvNo，I.Description，P.Price
FROM
）
SELECT I. *
，ROW_NUMBER（）OVER（由EffYe描述的InvNo ORDER，EffMonth Desc，
EffDay Desc）作为rn
来自库存I
）as
，Price P
WHERE I.rn = 1
AND I.InvNo = P .InvNo
BTW如果{库存号，有效年份，有效月份，有效日}是
不是钥匙，那么您可能想要添加价格进入ORDER BY使
查询确定性。
问候
Paul Vernon
商业智能，IBM全球服务
I''m trying to do a query that joins two tables. The trick is that I
only want it to return rows based on a certain criteria.
Table 1: Inventory
Fields: Inventory #, Description
Table 2: Prices
Fields: Inventory #, Price, Effective Year, Effective Month, Effective
Day
I want to return a row that gives me the Inventory #, Description, and
most current Price. I don''t care what the effective date for the price
is, but I need it to be the most current one.
I was thinking along the lines of this:
SELECT Inventory.InvNo, Inventory.Description, Prices.Price
FROM Inventory, Price
WHERE EffYear = (SELECT MAX(EffYear) FROM Prices WHERE Prices.InvNo =
Inventory.InvNo)
AND Inventory.InvNo = Prices.InvNo
But the complication is that there may be 2 different prices from the
same year. If they hadn''t set up the date to be in 3 different fields
it would be so much simpler. Any ideas are more than welcome!
"Susan M." <su******@hotmail.com> wrote in message
news:c5*************************@posting.google.co m...I''m trying to do a query that joins two tables. The trick is that I
only want it to return rows based on a certain criteria.
Table 1: Inventory
Fields: Inventory #, Description
Table 2: Prices
Fields: Inventory #, Price, Effective Year, Effective Month, Effective
Day
I want to return a row that gives me the Inventory #, Description, and
most current Price. I don''t care what the effective date for the price
is, but I need it to be the most current one.
I was thinking along the lines of this:
SELECT Inventory.InvNo, Inventory.Description, Prices.Price
FROM Inventory, Price
WHERE EffYear = (SELECT MAX(EffYear) FROM Prices WHERE Prices.InvNo =
Inventory.InvNo)
AND Inventory.InvNo = Prices.InvNo
But the complication is that there may be 2 different prices from the
same year. If they hadn''t set up the date to be in 3 different fields
it would be so much simpler. Any ideas are more than welcome!
You could do something like this:
WITH NewPrices AS ( SELECT InvNo, Price, DATE(RTRIM(CHAR(EffYear)) || ''-'' ||
RTRIM(CHAR(EffMonth)) || ''-'' || RTRIM(CHAR(EffDay))) as EffDate FROM Prices)
SELECT Inventory.InvNo, Inventory.Description, NewPrices.Price
FROM Inventory, NewPrices
WHERE EffDate = (SELECT MAX(EffDate) FROM NewPrices WHERE NewPrices.InvNo =
Inventory.InvNo)
AND Inventory.InvNo = NewPrices.InvNo;
Essentially, the WITH clauses builds a temporary table containing all the
columns of the Price table, except that the EffYear/EffMonth/EffDay fields
are converted into a single DATE value which will make the comparison logic
in your query work the way you would like it to.
Note that this query is not neccessarily going to fast, especially if your
Prices table has a lot of rows in it.
If you''re going to be doing queries like this on a regular basis, you might
want to look at rebuiding the Prices table with a better format for dates
(ie, using a single DATE column), or create a MQT (Materialized Query Table)
over the Prices table that does the date transformation.
--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab
"Susan M." <su******@hotmail.com> wrote in message
news:c5*************************@posting.google.co m...I''m trying to do a query that joins two tables. The trick is that I
only want it to return rows based on a certain criteria.
Table 1: Inventory
Fields: Inventory #, Description
Table 2: Prices
Fields: Inventory #, Price, Effective Year, Effective Month, Effective
Day
I want to return a row that gives me the Inventory #, Description, and
most current Price. I don''t care what the effective date for the price
is, but I need it to be the most current one.
I was thinking along the lines of this:
SELECT Inventory.InvNo, Inventory.Description, Prices.Price
FROM Inventory, Price
WHERE EffYear = (SELECT MAX(EffYear) FROM Prices WHERE Prices.InvNo =
Inventory.InvNo)
AND Inventory.InvNo = Prices.InvNo
But the complication is that there may be 2 different prices from the
same year. If they hadn''t set up the date to be in 3 different fields
it would be so much simpler. Any ideas are more than welcome!
You could do something like this:
WITH NewPrices AS ( SELECT InvNo, Price, DATE(RTRIM(CHAR(EffYear)) || ''-'' ||
RTRIM(CHAR(EffMonth)) || ''-'' || RTRIM(CHAR(EffDay))) as EffDate FROM Prices)
SELECT Inventory.InvNo, Inventory.Description, NewPrices.Price
FROM Inventory, NewPrices
WHERE EffDate = (SELECT MAX(EffDate) FROM NewPrices WHERE NewPrices.InvNo =
Inventory.InvNo)
AND Inventory.InvNo = NewPrices.InvNo;
Essentially, the WITH clauses builds a temporary table containing all the
columns of the Price table, except that the EffYear/EffMonth/EffDay fields
are converted into a single DATE value which will make the comparison logic
in your query work the way you would like it to.
Note that this query is not neccessarily going to fast, especially if your
Prices table has a lot of rows in it.
If you''re going to be doing queries like this on a regular basis, you might
want to look at rebuiding the Prices table with a better format for dates
(ie, using a single DATE column), or create a MQT (Materialized Query Table)
over the Prices table that does the date transformation.
--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab
"Susan M." <su******@hotmail.com> wrote in message
news:c5*************************@posting.google.co m...I''m trying to do a query that joins two tables. The trick is that I
only want it to return rows based on a certain criteria.
Table 1: Inventory
Fields: Inventory #, Description
Table 2: Prices
Fields: Inventory #, Price, Effective Year, Effective Month, Effective
Day
I want to return a row that gives me the Inventory #, Description, and
most current Price. I don''t care what the effective date for the price
is, but I need it to be the most current one.
Try
SELECT I.InvNo, I.Description, P.Price
FROM
)
SELECT I.*
, ROW_NUMBER() OVER(PARTITION BY InvNo ORDER BY EffYear Desc, EffMonth Desc,
EffDay Desc) as rn
FROM Inventory I
) as I
, Price P
WHERE I.rn = 1
AND I.InvNo = P.InvNo
BTW If {Inventory #, , Effective Year, Effective Month, Effective Day} is
not a key, then you might like to add Price into the ORDER BY to make the
query deterministic.
Regards
Paul Vernon
Business Intelligence, IBM Global Services
这篇关于请求帮助创建SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
