SELECT P1.* FROM PurchasePrices AS P1 INNER JOIN ( SELECT MIN(LastPrice)LASTPRICE,SKU FROM PurchasePrices GROUP BY SKU) P2 ON P1.SKU=P2.SKU AND P2.LastPrice=P1.LASTPRICE ORDER BY LastUpdatedOn DESC在这里先解释一下 SELECT MIN(LastPrice)LASTPRICE,SKU FROM PurchasePrices GROUP BY SKU 是根据SKU分组求出每组最小的价格外面 其实是根据上面这个条件查出里面的所有字段而已, 因为 里面的有GROUP BY 只能求一个SKU分组,所有只有这样嵌套查询, 问题出来了
SELECT MIN(LastPrice)LASTPRICE,SKU FROM PurchasePrices GROUP BY SKU 比如查询出来结果是10条记录,
而
SELECT P1.* FROM PurchasePrices AS P1 INNER JOIN ( SELECT MIN(LastPrice)LASTPRICE,SKU FROM PurchasePrices GROUP BY SKU) P2 ON P1.SKU=P2.SKU AND P2.LastPrice=P1.LASTPRICE ORDER BY LastUpdatedOn DESC
查询出来则为出现一个问题就是如果当表中SKU 和 LastPrice P2中和P1中都相等的话则会出来 多条也就是>10的记录,如果这二个条件都不相同这里指的是P1和P1中的那结果就是对的,
举例说明 第一条查询出来数据如下I41214G V1001 4.00 2.00 1 NULL drte admin 2011-03-01 10:58:05.407 admin
MC0046X V1003 0.00 6.00 0 NULL jjjjjjjjjjjjjjjjjj admin 2011-03-01 10:55:15.047 第二种语句查询如下
I41214G V1001 4.00 2.00 1 NULL drte admin 2011-03-01 10:58:05.407 admin
I41214G V1001 4.00 2.00 1 NULL drte admin 2011-03-01 10:58:05.407 admin
MC0046X V1003 0.00 6.00 0 NULL jjjjjjjjjjjjjjjjjj admin 2011-03-01 10:55:15.047因为第二种结果的第一条和第二条I41214G相同,并且 价格也相同,所有就会出来重复,如果随便改下不一样就正常了,请问这个问题怎么处理,其实我这样想的就在这个查询语法上面再套一个去掉重复的,然后给个创建时间的条件这样就不会了,但是不知道怎么去拼这个SQL了,请高手指教。
INNER JOIN
( SELECT MIN(LastPrice)LASTPRICE,SKU FROM PurchasePrices GROUP BY SKU) P2
ON P1.SKU=P2.SKU AND P2.LastPrice=P1.LASTPRICE
ORDER BY LastUpdatedOn DESC
(SELECT MIN(LastPrice)LASTPRICE, SKU FROM PurchasePrices GROUP BY SKU) P2
cross join
(SELECT top(1) * FROM PurchasePrices where SKU = P2.SKU and LastPrice = P2.LASTPRICE) P1
--如果需要取特定记录在P1中加order by
from PurchasePrices a
where not exists(select 1 from PurchasePrices where SKU=a.SKU and LastPrice<a.LastPrice)