这样做有效:
select top 100 cti.tradingitemid, pe.pricingDate, pe.priceClose, pe.adjustmentFactor, cc.countryId, cc.companyId
from ciqcompany cc
join ciqfinperiod cfp on cfp.companyid=cc.companyid
join ciqfininstance cfi on cfi.financialperiodid=cfp.financialperiodid
join ciqsecurity cs on cs.companyid = cc.companyid
join ciqtradingitem cti on cti.securityid=cs.securityid
join ciqPriceEquity pe on pe.tradingItemId=cti.tradingItemId
where pe.pricingDate >= '1974-12-31 00:00:00.000' and pe.pricingDate <'1975-02-01 00:00:00.000' and cc.countryId = 213
order by cc.companyId
问题是同一个公司的ID有几百行。我只希望每个公司有一个,第一个可以。
我四处看看,这是我能想到的最好的办法:
select *
FROM (
Select cti.tradingitemid,
pe.pricingDate,
pe.priceClose,
pe.adjustmentFactor,
cc.countryId
Row_number() OVER (PARTITION BY cc.companyId ORDER BY pe.pricingDate) RN
from ciqcompany cc
join ciqfinperiod cfp on cfp.companyid=cc.companyid
join ciqfininstance cfi on cfi.financialperiodid=cfp.financialperiodid
join ciqsecurity cs on cs.companyid = cc.companyid
join ciqtradingitem cti on cti.securityid=cs.securityid
join ciqPriceEquity pe on pe.tradingItemId=cti.tradingItemId
where pe.pricingDate >= '1974-12-31 00:00:00.000' and pe.pricingDate <'1975-02-01 00:00:00.000' and cc.countryId = 213
order by cc.companyId
)
Where rn = 1
语法在 Row_number()
但在我看来,这就像我看到的其他例子。
任何想法都将不胜感激。
2条答案
按热度按时间jdgnovmf1#
这样行吗?
c9x0cxw02#
三件事:
子查询需要表别名。
子查询不能包含
order by
.你需要一个逗号
row_number()
.所以:
我从日期中删除了时间部分。逻辑不需要它。