报价 - 用于表达的SQL [英] Offer price - SQL to obatin it

问题描述

嗨！如果提供产品，我无法获得正确的价格。

表结构（我们每个SKU只需要1个优惠）：

表产品：

SKU，RegularPrice，OfferStartDate，OfferEndDate， OfferPrice

1,100,201-01-01 00：00,2017-12-31 23：59,90

2,200,2017-01-01 00 ：00,2017-10-30 23：59,190

3,300



我尝试了以下SQL来提取它但是我似乎没有把它改正，好像我把SKU 1的OfferEndDate更改为昨天，它仍然显示了报价（应该显示为0，因为它已过期）：

Hi! I'm having trouble to obtain the correct price if a product is on offer.
Table structure (we only need 1 offer at any time per SKU):
Table Products:
SKU, RegularPrice, OfferStartDate, OfferEndDate, OfferPrice
1, 100, 2017-01-01 00:00, 2017-12-31 23:59, 90
2, 200, 2017-01-01 00:00, 2017-10-30 23:59, 190
3, 300

I tried the following SQL to extract it but I can't seem to have it right as if I change SKU 1 OfferEndDate to yesterday, it still shows the offer price (should show 0 as it'd be expired):

SELECT SKU , RegularPrice, (SELECT ISNULL((SELECT OfferPrice FROM Products aaa WHERE SKU = aaa.SKU AND OfferStartDate < getdate() and OfferEndDate > getdate()),0) as OfferPrice) FROM Products 

1.我在这里返回2列，我需要找出OfferPrice> 0然后它提供。我需要返回两个价格来显示它们。



2.对于不同的查询，我怎么能想出一个只返回一个价格列的SQL语句合适的价格（即优惠价格的常规价格）？



感谢您对此SQL问题的帮助！

Sergio



我尝试了什么：



尝试上述SQL语句。

1. I'm returning 2 columns here and I'd need to find out if OfferPrice > 0 then it's on offer. I need to return both prices to show them.

2. For a different query, how could I come up with a SQL statement that returns just one price column with the right price (ie Regular price of Offer price)?

Thanks for your help on this SQL issue!
Sergio

What I have tried:

Tried the SQL statement described above.

推荐答案

不确定我是否理解正确...

似乎你想获得OfferPrice，但是当它为null时，你想要返回RegularPrice。所以，你必须使用 COALESCE（）函数 [ ^ ]。< br $>

Not sure i understand you correctly...
Seems you want to obtain OfferPrice, but when it's null, then you want to return RegularPrice. So, you have to use COALESCE() function[^].

SELECT COALESCE(OfferPrice, RegularPrice) AS ActualPrice
FROM Products

根据您的需要，您必须使用左连接 [ ^ ]。



检查：

As to your needs, you have to use left join[^].

Check this:

DECLARE @tmp TABLE(SKU INT IDENTITY(1,1) NOT NULL, RegularPrice FLOAT, OfferStartDate DATETIME, OfferEndDate DATETIME, OfferPrice FLOAT)
DECLARE @d DATETIME = GETDATE()

INSERT INTO @tmp(RegularPrice, OfferStartDate, OfferEndDate, OfferPrice)
VALUES(100, '2017-01-01 00:00', '2017-12-31 23:59', 90), 
(200, '2017-02-01 00:00', '2017-10-30 23:59', 190),
(300, '2017-03-01 00:00', '2017-10-15 23:59', 270),
(420, '2017-04-01 00:00', '2017-09-30 23:59', 390),
(280, '2017-05-01 00:00', '2017-11-30 23:59', 250)

SELECT A.SKU, COALESCE(B.OfferPrice, A.RegularPrice) AS CurrentPrice
FROM @tmp AS A
LEFT JOIN (
	SELECT *
	FROM @tmp
	WHERE OfferStartDate<= @d AND OfferEndDate>=@d
) AS B ON A.SKU = B.SKU

结果：

Result:

SKU	CurrentPrice
1	90
2	190
3	300 --regular price, an offer expired
4	420 --regular price, an offer expired
5	250

欲了解更多详情，请参阅：决定在SQL Server中的COALESCE和ISNULL之间 [ ^ ]

SQL连接的可视化表示 [ ^ ]

这篇关于报价 - 用于表达的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！