English 中文(简体)
MySQL DISTINCT 不予排除
原标题:MySQL DISTINCT not Filtering out
  • 时间:2011-11-08 11:52:44
  •  标签:
  • mysql
  • sql

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仍然独一无二?

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

关于

最佳回答

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

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

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

问题回答

说明很难确定,但如果<代码>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

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

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

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

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

关于





相关问题
SQL SubQuery getting particular column

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...

please can anyone check this while loop and if condition

<?php $con=mysql_connect("localhost","mts","mts"); if(!con) { die( unable to connect . mysql_error()); } mysql_select_db("mts",$con); /* date_default_timezone_set ("Asia/Calcutta"); $date = ...

php return a specific row from query

Is it possible in php to return a specific row of data from a mysql query? None of the fetch statements that I ve found return a 2 dimensional array to access specific rows. I want to be able to ...

Character Encodings in PHP and MySQL

Our website was developed with a meta tag set to... <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> This works fine for M-dashes and special quotes, etc. However, I ...

Pagination Strategies for Complex (slow) Datasets

What are some of the strategies being used for pagination of data sets that involve complex queries? count(*) takes ~1.5 sec so we don t want to hit the DB for every page view. Currently there are ~...

Averaging a total in mySQL

My table looks like person_id | car_id | miles ------------------------------ 1 | 1 | 100 1 | 2 | 200 2 | 3 | 1000 2 | 4 | 500 I need to ...