Question

I have the folowing sql query:

SELECT DISTINCT(tbl_products.product_id), tbl_products.product_title,
            tbl_brands.brand_name, tbl_reviews.review_date_added, 
            NOW() AS time_now
            FROM tbl_products, tbl_reviews, tbl_brands
            WHERE tbl_products.product_id = tbl_reviews.product_id AND
            tbl_products.brand_id = tbl_brands.brand_id
            ORDER BY tbl_reviews.review_date_added DESC

That needs to filter out any duplicate product_id s unfortunatly selecting tbl_reviews.review_date_added makes each record unique which means DISTINCT will not work anymore.

是否有其他途径来做这种问询,使产品_id仍然独一无二?

我是按组别做的,问题在于我在网站上显示“审查——日期”,并选择最老的日期。我需要最新的日期。

关于

Answer 1

整个行各业的杰出作品。括号只是在实地:

distinct (a), b, c  ===  distinct a, b, c

直截了当的解决办法是group by 。您可使用<代码>min选定最老的日期。

select  tbl_products.product_id
,       min(tbl_products.product_title)
,       min(tbl_brands.brand_name)
,       min(tbl_reviews.review_date_added)
,       NOW() AS time_now
FROM    tbl_products, tbl_reviews, tbl_brands
WHERE   tbl_products.product_id = tbl_reviews.product_id AND
        tbl_products.brand_id = tbl_brands.brand_id
GROUP BY
        tbl_products.product_id
ORDER BY 
        min(tbl_reviews.review_date_added) DESC

值得注意的是,如果产品有多种品牌,就会达到最低点。

Answer 2

说明很难确定,但如果<代码>review_date_plus/code>是唯一的问题,则。和你一样,你希望获得该日期的票价?

如果以下情况没有帮助,请举例说明数据,例如产出,并说明你如何想创造产出?

SELECT tbl_products.product_id, tbl_products.product_title, tbl_brands.brand_name, MAX(tbl_reviews.review_date_added) AS review_date_added, NOW() AS time_now FROM tbl_products INNER JOIN tbl_reviews ON tbl_products.product_id = tbl_reviews.product_id INNER JOIN tbl_brands ON tbl_products.brand_id = tbl_brands.brand_id GROUP BY tbl_products.product_id, tbl_products.product_title, tbl_brands.brand_name ORDER BY MAX(tbl_reviews.review_date_added) DESC

Answer 3

Try this:

SELECT pr.product_id, pr.product_title,
       bd.brand_name, 
      (SELECT MAX(rev.review_date_added) FROM tbl_reviews rev
       WHERE pr.product_id = rev.product_id) AS maxdate, 
       NOW() AS time_now
FROM tbl_products pr INNER JOIN tbl_reviews re 
    ON pr.product_id = re.product_id
INNER JOIN tbl_brands bd
    ON pr.brand_id = bd.brand_id
GROUP BY pr.product_id
ORDER BY re.review_date_added DESC

或(按照@Hogan的建议)

SELECT pr.product_id, pr.product_title,
       bd.brand_name, md.maxdate
       NOW() AS time_now
FROM tbl_products pr INNER JOIN tbl_reviews re 
    ON pr.product_id = re.product_id
INNER JOIN tbl_brands bd
    ON pr.brand_id = bd.brand_id
INNER JOIN (SELECT product_id, MAX(review_date_added) AS maxdate 
            FROM tbl_reviews rev GROUP BY product_id) md
    ON pr.product_id = md.product_id
GROUP BY pr.product_id
ORDER BY re.review_date_added DESC

Answer 4

我把安多马尔的答案与你在此看到的一些变化结合起来。

SELECT tbl_products.product_id, tbl_products.product_title,
                    tbl_products.product_date_added, tbl_brands.brand_name,
                    MAX(tbl_reviews.review_date_added) AS review_date_added, NOW() AS time_now
            FROM tbl_products, tbl_reviews, tbl_brands
            WHERE tbl_products.product_id = tbl_reviews.product_id AND
                    tbl_products.brand_id = tbl_brands.brand_id
            GROUP BY tbl_products.product_id
            ORDER BY MAX(tbl_reviews.review_date_added) DESC

工作做得好,显示时间最晚,时间最短。

关于

友情链接