获取迄今为止尚未销售的产品的详细信息。 [英] Get the details of products which has not been sold till today.

问题描述

我没有在此查询中获得足够的输出



我尝试过的事情：

am i not getting the sufficient output on this query

What I have tried:

select * from  product_table p join order_table o  on p.p_id=o.p_id  where order_date <> curdate();

推荐答案

当然应该是：

Surely it should be:

select * from  product_table p join order_table o  on p.p_id=o.p_id where order_date >= curdate();

一般情况下：你必须得到所有销售的产品，并将它们排除在所有产品之外。

但我不知道你的数据库是怎样的，桌子之间会发生什么样的关系。



例如：

In general: you have to get all sold products and exclude them from all products.
But i don't know how your database look like and what relationships happen between tables.

For example:

SELECT product_id
FROM Products
WHERE NOT IN(
   SELECT DISTINCT product_id
   FROM Orders);
-- you can add your logic about date of sale:
--   WHERE dateofsale<=curdate()
-- into subquery

详情请见：

MySQL :: MySQL 8.0参考手册:: 13.2.11.6具有EXISTS或NOT EXISTS的子查询 [ ^ ]

MySQL :: MySQL 8.0参考手册:: 12.3.2比较函数和运算符：IN（） [ ^ ]

MySQL :: MySQL 8.0参考手册:: 12.3.2比较函数和运算符：NOT IN（） [ ^ ]

For further details, please see:
MySQL :: MySQL 8.0 Reference Manual :: 13.2.11.6 Subqueries with EXISTS or NOT EXISTS[^]
MySQL :: MySQL 8.0 Reference Manual :: 12.3.2 Comparison Functions and Operators: IN()[^]
MySQL :: MySQL 8.0 Reference Manual :: 12.3.2 Comparison Functions and Operators: NOT IN()[^]

查询中的子句其中order_date <> curdate（）将返回除今天以外的所有产品和订单详细信息，因为不等于。

答案是为了使它大于或等于。

The clause in your query where order_date <> curdate() is going to return all the product and order details except for today's because of the not equals.
The answer to this is to make that a greater-than-or-equal-to.

SELECT  *
FROM    product_table  p
JOIN    order_table    o  on p.p_id = o.p_id
WHERE   order_date >= curdate()             -- products and orders from today

这将;但是，仍然包含过去销售的产品。接下来要做的就是添加一个子查询来删除过去的产品。从过去的销售中获取产品ID将是<$​​ c $ c> SELECT p_ID FROM order_table WHERE order_date< curdate（）

这将被添加到原始查询中并带有 NOT IN 子句以删除它们

This will; however, still contain products that were sold in the past. So the next thing to do will be to add in a sub-query to remove the products from the past. To get the Product IDs from past sales will be SELECT p_ID FROM order_table WHERE order_date < curdate()
and this will be added into the original query with a NOT IN clause to remove them

SELECT  *
FROM    product_table  p
JOIN    order_table    o  on p.p_id = o.p_id
WHERE   order_date >= curdate()             -- Products and orders from today 
AND     p.p_id NOT IN (                     -- remove products from the past
            SELECT  p_ID
            FROM    order_table
            WHERE   order_date < curdate()
       )

现在这个查询将为您提供所有产品详细信息，但它也将包含所有订单详细信息。如果同一产品有多个购买者，他们将为同一产品返回多行。如果我们想减少这个，我们将删除JOIN语句并将其作为子查询使用。

Now this query is going to give you all the product details but it is also going to contain all the order details as well. And if there were multiple purchasers of the same product they will return multiple rows for the same product. If we want to reduce this we will remove the JOIN statement and work it in as a sub-query.

SELECT *
FROM   product_table p     -- Products only, no order information
WHERE  p.p_id IN (         -- Get Products sold today
         SELECT p_ID
         FROM   order_table
         WHERE  order_date >= curdate()
)
AND    p.p_id NOT IN (       -- Remove products from the past
         SELECT p_ID
         FROM   order_table
         WHERE  order_date < curdate()
)

这篇关于获取迄今为止尚未销售的产品的详细信息。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！