gpt4 book ai didi

php - 根据给定的排名和顺序显示mysql记录

转载 作者:行者123 更新时间:2023-11-30 23:00:26 24 4
gpt4 key购买 nike

我正在写一个查询,我应该根据给定的排名位置以及价格顺序和按站点名称分组来显示记录

shopdetails

id | productid | productname | sitename | siteid | site_priority | price | color
1 555 xyz a 1 0 10 blue
2 555 xyz b 12 1 50 blue
3 555 xyz a 1 0 12 red
4 555 xyz c 3 4 9 red
5 555 xyz e 15 5 19 blue
6 555 xyz e 15 5 21 red
7 555 xyz b 12 1 42 red
8 555 xyz c 3 4 56 blue

我必须满足的三个条件才能获得预期的输出条件

  1. 如果 site_priority !=0 则将该记录放在该位置。说例如。如果 site_priority = 5 。将所有站点名称分组后显示该记录排在第五位
  2. 如果 site_priority = 0 则显示记录价​​格 asc
  3. 按站点名称对记录进行分组并按价格排序

最终预期输出

id | productid | productname | sitename | siteid | site_priority | price | color
7 555 xyz b 12 1 42 red
2 555 xyz b 12 1 50 blue

############ the above two records are kept in the First Position since site_priority = 1 and ordered by price asc
Now check for site_priority 2 is there if not show site_priority = 0 by price asc ,
Now 2nd records would be

1 555 xyz a 1 0 10 blue
3 555 xyz a 1 0 12 blue

Now check for site_priority 3 is there if not check for site_priority 0 ,
is not then make the priority one level minus .
move site_priority 4 to 3 , 5 to 4 .

4 555 xyz c 3 4 9 red
8 555 xyz c 3 4 56 blue
5 555 xyz e 15 5 19 blue
6 555 xyz e 15 5 21 red

有没有最好的方法来完成这个复杂的查询。

我厌倦了这样做,但它并没有像我预期的那样出现。

select 
productid,
productname,
sitename,
site_priority,
price,
colorname,
(select
count(*)
from
shopdetails b
where
productid = 1250 and b.site_priority > a.site_priority order by price asc)+1 as rnk
from
shopdetails a
where
productid = 1250
having site_priority > 0
order by rnk

我的第一个条件很容易理解

sitename | priority
a 1
b 2
c 3
d 0
e 0
f 0
g 0
h 0
i 5



outpt

a 1 ==> position 1
b 2 ==> position 2
c 3 ==> position 3
d 0 ==> position 4
i 5 ==> position 5
e 0 ==> position 6
f 0 ==> position 7
g 0 ==> position 8
h 0 ==> position 9

最佳答案

这是一个有趣的问题。我不确定我是否正确理解了你,但这是我想出的(有点古怪)。不过,它与您的示例输出匹配。

SELECT * FROM test
ORDER BY CASE WHEN site_priority = 0 THEN (
SELECT k.outp FROM (
SELECT @rownum:= @rownum + 1 outp, t.site_priority outp2 FROM (
SELECT DISTINCT site_priority FROM test ORDER BY site_priority ASC) t,
(SELECT @rownum := 0) r
WHERE t.site_priority != @rownum) k
WHERE k.outp != k.outp2 limit 1)
ELSE site_priority END, sitename, price;

关于php - 根据给定的排名和顺序显示mysql记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/24320753/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com