Mysql查询查找最小和最多2列,其中最少1列不得等于零

Everyone i have stuck on strange problem in mysql. I am a newbie in mysql, hope someone can help me.

I have 2 columns of price in a table.mrp_price and discounted_price :

mrp_price discounted price
 400        000
 500        300 
 600        400
 700        500
 800        600

I want my mysql query to return minimum as 300 (whatever is minimum in mrp and discounted price but not to consider 000 values of dicounted price) and maximum as 800 (whatever is maximum in mrp_price and discounted_price).

my output will looks like this:

     min(mrp_price) max(mrp_price)   min(discounted_price)  max(discounted_price)
       400                  800           300                      600

I want my final output as :

       min(price)    max(price)
          300             800

I want my final output with the help of query. I hop i am little bit clear now in making my frnds understand my problem.

Please help,

展开翻译

译文

每个人都坚持使用mysql中的奇怪问题。 我是mysql的新手,希望有人可以帮助我。</ p>

我在表格中有2列价格。 mrp_price </ code>和 discounted_price </ code >:</ p>

  mrp_price折扣价
400 000
500 300
600 400
700 500
800 600
</ code> </ pre> \ n

我希望我的mysql查询返回最小</ strong>为 300 </ code>( mrp </ code>和折扣价<< / code>但不考虑价格的000值)和最大值</ strong>为 800 </ code>( mrp_price </ code>和 discounted_price <中的最大值 /code>).


nn

my输出将如下所示:</ p>

  min(mrp_price)max(mrp_price)min(discounted_price)max  (discounted_price)
400 800 300 600
</ code> </ pre>

我希望我的最终输出为:</ strong> </ p>

  min(price)max(price)
300 800
</ code> </ pre>

我希望借助查询获得最终输出</ strong>。 我现在有点清楚,让我的朋友们理解我的问题。</ p>

请帮忙,</ p>
</ div>

drygauost253590142
drygauost253590142 是的兄弟价格总是大于折扣价格。
5 年多之前 回复
doumi1912
doumi1912 为什么你说“mrp_price和discounted_price中的最大值”不会mrp_price总是大于discounted_price?
5 年多之前 回复
doumingchen3628
doumingchen3628 OP很清楚。他还公布了预期产量。请参阅Notulysses的回答
5 年多之前 回复
dongzuo7166
dongzuo7166 举一些输出你想要的例子,因为你的问题不清楚,你做了什么?
5 年多之前 回复
douzhulan1815
douzhulan1815 什么是mrp_price和折扣价?
5 年多之前 回复

4个回答

SELECT MAX(`p`)
 , MIN(`p`) 
FROM (SELECT Min(`discounted_price`) as `p` FROM `table` WHERE discounted_price!=0)
   UNION
   (SELECT Max(`mrp_price`) as `p` FROM `table` )

展开翻译

译文



  SELECT MAX(p
,MIN(p
FROM(SELECT Min(discounted_price )作为p FROMtable WHERE discounted_price!= 0)
UNION
(SELECT Max(mrp_price)asp FROMtable
</ code> </ pre>
< / DIV>

I would suggest this approach:

select least(min(case when discounted_price > 0 then discounted_price else 10000 end),
             min(case when mrp_price > 0 then mrp_price else 10000 end)
            )
from table t;

展开翻译

译文



我建议采用这种方法:</ p>

  select least(min(discounted_price的情况)  &gt; 0然后折扣_价格其他10000结束),
min(mrp_price&gt; 0然后mrp_price其他10000结束的情况)

从表t;
</ code> </ pre>
</ div>

The logic is as follows : select two rows in one and then find maximum and minimum values.

SELECT MAX(`t`)
     , MIN(`t`) 
FROM ( SELECT `mrp_price` as `t` FROM `table`
       UNION
       SELECT `discounted price` as `t` FROM `table` ) 
WHERE `t` != 0

In case the first column is always greater than the second :

SELECT MAX(`mrp_price`)
     , MIN(`discounted price`) 
FROM `table`
WHERE `t` != 0

展开翻译

译文



逻辑如下:在一个中选择两行,然后找到最大值和最小值。</ p>

< pre> SELECT MAX(t
,MIN(t
FROM(SELECTmrp_price ast FROMtable
UNION
SELECT折扣价格t FROMtable
WHEREt!= 0
</ code> </ pre>

如果第一列总是大于第二列:</ p>

  SELECT MAX(mrp_price
,MIN(折扣价格
FROMtable
WHEREt!= 0
</ code> </ pre>
</ DIV>

Your question is not quite clear. Do you want to select the mrp_price where discount is maximum or you want you want mrp_price where both mrp_price and discount is maximum. Same case for minimum. For the earlier case, you can try following query

SELECT MAX(mrp_price) FROM *{TABLE NAME}* WHERE discounted price=(MAX(discounted price))

Similar query for minimum

SELECT MIN(mrp_price) FROM *{TABLE NAME}* WHERE discounted price=(MIN(discounted price)) AND discounted price!=0

展开翻译

译文



您的问题不太清楚。 您是否要选择折扣最大的mrp_price,或者您希望mrp_price,其中mrp_price和折扣均为最大值。 同样的情况至少。 对于早期的情况,您可以尝试以下查询</ p>

  SELECT MAX(mrp_price)FROM * {TABLE NAME} * WHERE折扣价=(MAX(折扣价))
< / code> </ pre>

类似查询最小</ p>

  SELECT MIN(mrp_price)FROM * {TABLE NAME} * WHERE折扣价=(MIN  (折扣价))和折扣价!= 0 
</ code> </ pre>
</ div>

dongxia026531
dongxia026531 先生,最低mrp价格是400,最低折扣价格是300.所以我希望最低价格为300.且最高价格相同。
5 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!