SQL如何从每个加入的表中获取最新的结果?

我想通过每组产品的MAX(ID)(productToken),即产品的唯一名称,从所有加入的表中获取最新的产品表。加入的表是 – 产品(商店),可用性(状态),描述(产品),和产品的价格。所有这些表都包含了唯一的productToken,并且这些表可以通过添加新的记录来改变(独立),所以我的目的是通过从每个表中检索最新的记录来组成一个大表(包含产品的实际信息)。我的代码是这样的。第一次添加的产品工作得很好,但在任何一个表中添加新记录后,事情变得很奇怪(查询没有检索到任何结果)。

SELECT *
FROM products
JOIN productsStore ON products.productToken = productStore.productToken
JOIN productsStatus ON products.productToken = productsStatus.productToken
JOIN productsPrice ON products.produstToken = productsPrice.productToken
JOIN categories ON products.categoryToken = categories.categoryToken
WHERE products.shopToken = '$shopToken' 
    AND products.productID IN 
        (SELECT MAX(productID) 
        FROM products 
        GROUP BY productToken)
    AND productsPrice.productPriceID IN 
        (SELECT MAX(productPriceID) 
        FROM productsPrice
        GROUP BY produktToken)
    AND productsStatus.productStatusID IN 
        (SELECT MAX(productStatusID) 
        FROM productsStatus
        GROUP BY productToken)
    AND produktyStore.productStoreID IN 
        (SELECT MAX(productStoreID) 
        FROM productsStore
        GROUP BY productToken)
    AND categories.categoryID IN 
        (SELECT MAX(categoryID) 
        FROM categories
        GROUP BY categoryToken)
ORDER BY categories.categoryID DESC

解决方案:

我想从所有加入的表中,用最新的行来检索一个大的产品表。

我想,你需要的是带有相关子查询的平等条件。where 句,而不是 in 条件与集合查询。这可以让你用给定的 “最新 “记录过滤每个连接表的 productToken.

SELECT *
FROM products p
JOIN productsStore  psr ON psr.productToken = p.productToken
JOIN productsStatus psu ON psu.productToken = p.productToken
JOIN productsPrice  ppr ON ppr.produstToken = p.productToken
JOIN categories     c   ON c.categoryToken  = p.categoryToken
WHERE 
    p.shopToken = '$shopToken' 
    AND p.productID         = (SELECT MAX(p1.productID)         FROM products      p1   WHERE p1.productToken   = p.productToken)
    AND psr.productStoreID  = (SELECT MAX(psr1.productStoreID)  FROM productsStore psr1 WHERE psr1.productToken = p.productToken)
    AND psu.productStatusID = (SELECT MAX(psu1.productStatusID) FROM productStatus psu1 WHERE psu1.productToken = p.productToken)
    AND ppr.productPriceID  = (SELECT MAX(ppr1.productPriceID)  FROM productsPrice ppr1 WHERE ppr1.productToken = p.productToken)
    AND c.categoryID        = (SELECT MAX(c1.categoryID)        FROM category      c1   WHERE c1.productToken   = p.productToken)

如果你正在运行MySQL 8.0(或MariaDB 10.3或更高),你可以使用 ROW_NUMBER() 在子查询中代替。

SELECT *
FROM (
    SELECT p.*, ROW_NUMBER() OVER(PARTITION BY productToken ORDER BY productID DESC) rn
    FROM products p
) p
INNER JOIN (
    SELECT psr.*, ROW_NUMBER() OVER(PARTITION BY productToken ORDER BY productStoreID DESC) rn
    FROM productsStore psr
) psr ON psr.productToken = p.productToken AND psr.rn = 1
INNER JOIN (
    SELECT psu.*, ROW_NUMBER() OVER(PARTITION BY productToken ORDER BY productStatusID DESC) rn
    FROM productsStatus psu
) psu ON psu.productToken = p.productToken AND psu.rn = 1
INNER JOIN (
    SELECT ppr.*, ROW_NUMBER() OVER(PARTITION BY productToken ORDER BY productsPriceID DESC) rn
    FROM productsPrice ppr
) ppr ON ppr.productToken = p.productToken AND ppr.rn = 1
INNER JOIN (
    SELECT c.*, ROW_NUMBER() OVER(PARTITION BY productToken ORDER BY categoryID DESC) rn
    FROM categories c
) c ON c.productToken = p.productToken AND c.rn = 1
WHERE p.shopToken = '$shopToken' AND p.rn = 1

本文来自投稿,不代表实战宝典立场,如若转载,请注明出处:https://www.shizhanbaodian.com/20523.html

(0)
上一篇 1天前
下一篇 1天前

相关推荐

发表评论

登录后才能评论