sql查询，奇怪的WHERE条件

本文介绍了sql查询，奇怪的WHERE条件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

 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

解决方案

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

解决方案

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

SQL查询

sql查询，奇怪的WHERE条件

问题描述