我怎样才能加入? [英] How can I get this join?

问题描述

嗨
打扰一下.我的英语说得不好.

我的数据库中有2个表.

Master_table具有以下字段:product_name，receive_date，price.

明细表(每日信息)具有以下字段:product_name，sell_date.

我想获得一份已售产品的报告.
由于某天产品的价格不同，因此SQL命令必须使用Master_table中的相关记录，该记录中的master表中的日期与detail表中的日期相关.

我可以在ACCESS中使用"first"一词来做到这一点，但在SQL Server中却不能.

怎么可能?

非常感谢

Hi
Excuse me. I can’t speak English well.

I have 2 tables in my database.

Master_table has these fields : product_name, receive_date, price.

Detail table (daily information) has these fields : product_name, sell_date.

I wants to get a report of products that have been sold.
Since the price of a product is different in some days, the SQL command must use related record in Master_table which the date in master table be related to date in detail table.

I can do this in ACCESS by use of word "first", but in SQL Server I can’t.

How is it possible?

Thanks very much

推荐答案

这应该给您产品在销售日期的价格.

This should give you the price of the products on their sell date.

SELECT D.product_name,
       D.sell_date,
       (SELECT TOP 1 M.price
        FROM Master_table M
        WHERE M.product_name = D.Product_name AND M.receive_date >= D.sell_date
        ORDER BY M.receive_date DESC)
FROM Detail_table D

select top 1 rerceive_date , price from master_table order by receive_date desc

Select * 
from Master_table m, Detail_table d
where m.product_name = d.product_name; 

此查询适用于oracle 9i.

this query is for oracle 9i.

这篇关于我怎样才能加入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！