sql查询，奇怪的WHERE条件 [英] sql query, weird WHERE condition

问题描述

这是我第一次遇到这个问题。让我们看一下桌面设计如何：



产品（存储产品信息，如售价）

 prt_ID prt_price 
  1   2  
  2   5  

prtMonitor （用于存储当天产品的余额）

 pm_ID ID_prt pm_closing pm_date 
  1   1   7  2013-09-01 
  2   2   4  2013-09- 01 
  3   1   5  2013-09-02 
  4   2   3  2013-09-02 
  5   1   2  2013-09-03 
  6   2   1  2013-09-03 

让我们说如果我想在2&上看到结果2013年9月3日。

预期结果：

 prt_ID打开结算已售出TotalPrice  date  
  1   7   5   2   4  2013-09-02 
  2   4   3   1   10  2013-09-03 

*实际上我只想要TotalPrice 和日期，我建立一个易于理解的完整表格

*打开，关闭和销售是产品数量;已售出=开幕 - 结束

* TotalPrice = prt_price *已售出



这是我目前为止的工作查询，但它只能获得1日期，我如何获得上述日期列表？我的想法是使用BETWEEN，但在这种情况下，怎么样？

  SELECT  SUM（prt_price *（Opening-Closing）） AS  TotalPrice，pm_date  FROM （
  SELECT  id_prt， 0   AS 打开，pm_closing  AS 结束，pm_date  FROM  prtMonitor  WHERE  pm_date = '  2013-09-02' 
  UNION   ALL  
  SELECT  id_prt，pm_closing  AS 打开， 0   AS 结束，pm_date  FROM  prtMonitor  WHERE  pm_date = '  2013-09-01'） AS  A 
  LEFT   JOIN 产品 ON  id_prt = prt_id 

解决方案

嗨melberry，



您的查询问题在于您正在使用UNION而不是JOIN。 UNION允许您添加一个查询的结果（第1行和第2行）以添加到另一个查询的结果（第3行和第4行），以便在结果中获得总共4行。另一方面，JOIN会将第一个子查询的列添加到第二个子查询的列中（导致2行具有更大的列集）。

尝试以下方法：

  SELECT  
 product.prt_ID，
 openMonitor.pm_closing  AS 打开，
 closeMonitor.pm_closing  AS 结算，
 ... 
  FROM  prtMonitor  AS  openMonitor 
  JOIN  prtMonitor  AS  closeMonitor 
  ON  closeMonitor.ID_prt = openMonitor.ID_prt 
  AND  closeMonitor.pm_Date = DATEADD（day， 1 ，openMonitor.pm_Date）
  JOIN  product 
  ON  product.prt_ID = openMonitor.ID_prt 
  WHERE  openMonitor.pm_Date = '  2013- 09-02'  

this is my first time i face this problem. Let us see how is the table design:

product (to store the product information, such as selling price)

prt_ID    prt_price
  1          2
  2          5

prtMonitor (to store the products' balance on that day)

pm_ID    ID_prt      pm_closing     pm_date
  1        1            7        2013-09-01
  2        2            4        2013-09-01
  3        1            5        2013-09-02
  4        2            3        2013-09-02
  5        1            2        2013-09-03
  6        2            1        2013-09-03

let's say if i want to see the result on 2 & 3 of Sept 2013.
Expected result:

prt_ID   Opening     Closing       Sold     TotalPrice        date
  1         7           5           2           4          2013-09-02
  2         4           3           1          10          2013-09-03

* Actually i only want the "TotalPrice" And "Date", i build a full table for easy to understand
* Opening, Closing, and Sold is the product quantity; Sold = Opening - Closing
* TotalPrice = prt_price * Sold

This is my working query so far, but it's only able to get 1 date, how can i get the list of date as above? my idea is to use BETWEEN, but in this case, how?

SELECT SUM(prt_price * (Opening-Closing)) AS TotalPrice, pm_date FROM(
SELECT id_prt, 0 AS Opening, pm_closing AS Closing, pm_date FROM prtMonitor WHERE pm_date = '2013-09-02'
UNION ALL
SELECT id_prt, pm_closing AS Opening, 0 AS Closing, pm_date FROM prtMonitor WHERE pm_date = '2013-09-01') AS A
LEFT JOIN product ON id_prt = prt_id

解决方案

Hi melberry,

The problem with your query is that you're doing UNION instead of JOIN. UNION allows you to add results of one query (rows 1 and 2) to add to results of another query (rows 3 and 4) to get 4 row in total in result. JOIN, on the other hand, will add columns of first subquery to columns of second subquery (resulting to 2 rows with bigger set of columns).
Try following approach:

SELECT 
	product.prt_ID,
	openMonitor.pm_closing AS Opening,
	closeMonitor.pm_closing AS Closing,
	...
FROM prtMonitor AS openMonitor
JOIN prtMonitor AS closeMonitor
     ON closeMonitor.ID_prt = openMonitor.ID_prt
     AND closeMonitor.pm_Date = DATEADD(day, 1, openMonitor.pm_Date)
JOIN product 
     ON product.prt_ID = openMonitor.ID_prt
WHERE openMonitor.pm_Date = '2013-09-02'

这篇关于sql查询，奇怪的WHERE条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！