如何在mysql的where子句中传递min函数 [英] how to pass min function in where clause in mysql

问题描述

我有以下表格。

 supplier_info表：
 +   -    --- + ----------------------------- ----- +  
 | sid | sname | 
 +   -    --- + --------------- ------------------- +  
 |  1  | Shree Mangireesh印刷机| 
 |  2  | SAI INDUSTRIES | 
 |  3  | GLOBE GLASS CONTAINERS | 
 |  4  | SONIC BIOCHEM EXTRACTIONS LTD | 
 |  5  | SPACE AGE PLASTIC INDUSTRIES | 
 +   -    --- + --------------- ------------------- +  

 rm_price表：
 
 +   -    ------- + ------------ + ----------- + ---------- +  
 | rmname | inv_date | unitprice |数量| 
 +   -    ------- + ----------- -  + ----------- + ---------- +  
 | Aerosil | 2013-02-12 |  4250  |  1  | 
 | Aerosil | 2013-03-03 |  4050  |  1  | 
 | Aerosil | 2013-03-29 |  5050  |  1  | 
 | Aerosil | 2013-04-20 |  5950  |  1  | 
 | Aerosil | 2013-05-03 |  5150  |  1  | 
 | Aerosil | 2013-05-13 |  4250  |  1  | 
 | Aerosil | 2013-05-25 |  4050  |  1  | 
 | Aerosil | 2013-06-01 |  3550  |  1  | 
 | Aerosil | 2013-06-30 |  5055  |  1  | 
 | Aerosil | 2013-07-15 |  5250  |  1  | 
 | Aerosil | 2013-08-03 |  5500  |  1  | 
 | Aerosil | 2013-09-12 |  5000  |  1  | 
 | Aerosil | 2013-10-15 |  5350  |  1  | 
 | Aerosil | 2013-11-18 |  5900  |  1  | 
 | Aerosil | 2013-12-02 |  5200  |  1  | 
 +   -    ------- + ----------- -  + ----------- + ---------- +  
< pre> 
 
i需要结果喜欢 sname，min（unitprice）和各自的inv_date，max （unitprice）和各自的inv_date，数量，当前 unitprice。 
 
结果 ：
< pre lang =  < span class =code-string> SQL> 
 +   -    ------------------- + ---------------- + ---------- + ---------------- + ---- ------ +  
 | rmname | min（unitprice）| min_date | max（unitprice）| max_date | 
 +   -    ------------------- + ---------------- + ---------- + ---------------- + ---- ------ +  
 | Aerosil |  3550  | 2013-06-01 |  5950  | 2013-04-20 | 
 +   -    ------------------- + ---------------- + ---------- + ---------------- + ---- ------ +  

i尝试了以下查询，但对于inv_date，它返回零

 mysql> 选择 rmname，min（unitprice），inv_date  （选择 inv_date 来自 rm_price wh 
 ere unitprice =（ select  min（unitprice）< span class =code-keyword> from  rm_price）） as  min_date，max（unitprice），
 inv_date  in （选择 inv_date 来自 rm_price  where  unitprice =（ select  max（unitprice）
 来自 rm_price） ） as  max_date 来自 rm_price  group   by  rmname; 

我的查询结果是：

 +   -    ------------------- + ---------------- +  - --------- + ---------------- + ---------- +  
 | rmname | min（unitprice）| min_date | max（unitprice）| max_date | 
 +   -    ------------------- + ---------------- + ---------- + ---------------- + ---- ------ +  
 | Aerosil |  3550  |  0  |  5950  |  0  | 
 +   -    ------------------- + ---------------- + ---------- + ---------------- + ---- ------ +  

执行查询需要更多时间（导致放置多个子串）。有没有办法获得上述要求的记录。请帮帮我。



先谢谢。

解决方案

如果你想搜索基于的数据库聚合函数的结果，你应该使用 HAVING子句 [ ^ ]！



但在你的情况下，我会建议使用变量 [ ^ ]：

  SELECT   @ minprice  = MIN（unitprice） FROM  rm_price; 
  SELECT   @ maxprice  = MAX（unitprice） FROM  rm_price; 
 
  SELECT  rmname， @minprice   as  MinPrice，（ SELECT  inv_date  FROM  rm_price  WHERE  unitprice =  @ minprice   AND  rmname = t.rmname） AS  MinDate，
  @ maxprice   AS  MaxPrice，（  SELECT  inv_date  FROM  rm_price  WHERE  unitprice = < span class =code-sdkkeyword> @ minprice   AND  rmname = t.rmname） AS  MaxDate 
  FROM  rm_price  AS  t; 

它应该执行q快速。

HI您应该尝试如下简单查询

 选择 T.rmname，R.unitprice 为 Min，R.inv_date，M.unitprice  as  Max，M.inv_date 来自 rm_price R  inner   join  
（ select  rmname，min（unitprice）最小值，最大值（unitprice）最大值来自 rm_Price）T 
   R.unitprice = T.Min 
 内部  join  rm_Price M  on  M.unitprice = T.Max 

i have the follwing tables.

supplier_info table:
+-----+----------------------------------+
| sid | sname                            |
+-----+----------------------------------+
|   1 | Shree Mangireesh Printing Press  |
|   2 | SAI INDUSTRIES                   |
|   3 | GLOBE GLASS CONTAINERS           |
|   4 | SONIC BIOCHEM EXTRACTIONS LTD    |
|   5 | SPACE AGE PLASTIC INDUSTRIES     |
+-----+----------------------------------+

rm_price table:

+---------+------------+-----------+----------+
| rmname  | inv_date   | unitprice | quantity |
+---------+------------+-----------+----------+
| Aerosil | 2013-02-12 | 4250      | 1        |
| Aerosil | 2013-03-03 | 4050      | 1        |
| Aerosil | 2013-03-29 | 5050      | 1        |
| Aerosil | 2013-04-20 | 5950      | 1        |
| Aerosil | 2013-05-03 | 5150      | 1        |
| Aerosil | 2013-05-13 | 4250      | 1        |
| Aerosil | 2013-05-25 | 4050      | 1        |
| Aerosil | 2013-06-01 | 3550      | 1        |
| Aerosil | 2013-06-30 | 5055      | 1        |
| Aerosil | 2013-07-15 | 5250      | 1        |
| Aerosil | 2013-08-03 | 5500      | 1        |
| Aerosil | 2013-09-12 | 5000      | 1        |
| Aerosil | 2013-10-15 | 5350      | 1        |
| Aerosil | 2013-11-18 | 5900      | 1        |
| Aerosil | 2013-12-02 | 5200      | 1        |
+---------+------------+-----------+----------+
<pre>

i need result like  sname,min(unitprice)and respective inv_date,max(unitprice) and respective inv_date,quantity,current unitprice.

result is :
<pre lang="SQL">
+---------------------+----------------+----------+----------------+----------+
| rmname              | min(unitprice) | min_date | max(unitprice) | max_date |
+---------------------+----------------+----------+----------------+----------+
| Aerosil             | 3550           |2013-06-01| 5950           |2013-04-20| 
+---------------------+----------------+----------+----------------+----------+

i have tried the follwing query but it returns zero for inv_date

mysql> select rmname,min(unitprice),inv_date in(select inv_date from rm_price wh
ere unitprice=(select min(unitprice) from rm_price)) as min_date,max(unitprice),
inv_date in(select inv_date from rm_price where unitprice=(select max(unitprice)
 from rm_price))as max_date from rm_price group by rmname;

My query result is:

+---------------------+----------------+----------+----------------+----------+
| rmname              | min(unitprice) | min_date | max(unitprice) | max_date |
+---------------------+----------------+----------+----------------+----------+
| Aerosil             | 3550           |        0 | 5950           |        0 |
+---------------------+----------------+----------+----------------+----------+

It takes more time to execute the query(cause place multiple substrings). is there any way to get the records for the above requirement. please help me out.

Thanks in Advance.

解决方案

If you want to search database based on result of aggregate function, you should use HAVING clause[^]!

But in your case i would suggest to use variables[^]:

SELECT @minprice = MIN(unitprice) FROM rm_price;
SELECT @maxprice = MAX(unitprice) FROM rm_price;

SELECT rmname, @minprice as MinPrice, (SELECT inv_date FROM rm_price WHERE unitprice = @minprice AND rmname = t.rmname) AS MinDate,
    @maxprice AS MaxPrice, (SELECT inv_date FROM rm_price WHERE unitprice = @minprice AND rmname = t.rmname) AS MaxDate
FROM rm_price AS t;

It should execute quite fast.

HI You should try simple query like following

select T.rmname,R.unitprice as Min,R.inv_date,M.unitprice as Max,M.inv_date from rm_price R inner join 
(select rmname,min(unitprice) Min,max(unitprice) Max from rm_Price )T
on R.unitprice=T.Min
inner join rm_Price M on M.unitprice=T.Max

这篇关于如何在mysql的where子句中传递min函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！