以特定的顺序从数据库中获取数据

i have a table named vendor here is a view of it

我有一个名为vendor的表这是它的视图

id  vendorname   staffpick   totalfav
1      V1           yes        1
2      V2           yes        3
3      V3           yes        4
4      V4                      2
5      V5                      5
6      D
7      A

I wish to create a list of vendors in the following order (no repetition of vendors) First those vendors should come whose value of staffpick is yes and these staffpick vendors should be arranged in dec order of totalfav value. Then those vendors should come who have totalfav but staffpick value is not yes. The last part comes where those vendors will be displayed who are neither staffpick nor totalfav and these vendors should be arranged in alphabatic order

我希望按以下顺序创建一个供应商列表(不重复供应商)首先，这些供应商的员工选择价值为yes，并且这些员工选择供应商应该按照12月的完全收藏价值排序。那么那些有完全收藏的供应商应该来，但是员工选择的价值不是肯定的。最后一部分是展示那些既不是员工选择也不是完全收藏的供应商，这些供应商应该按照字母顺序排列

The list should be like this

列表应该是这样的。

V3
V2
V1
V5
V4
A
D

if i try to create an array for every part like this i get a repetition

如果我尝试为每个部分创建一个数组，就会得到重复

$sql = "SELECT * FROM  vendors where staffpick='yes' and favvendor!=''";
$result = mysql_query($sql);
$row = mysql_fetch_array($result)

can anyone tell how i can do the sorting

谁能告诉我怎么分类吗

3 个解决方案

#1

Try something like this

这样的尝试

SELECT * 
FROM  vendors
ORDER BY staffpick ='YES' AND totalfav DESC,
         staffpick IS NULL AND totalfav DESC,
         staffpick IS NULL AND totalfav IS NULL AND vendorname ASC

#2

You need to add the order by clause in the query. The query will be like this

您需要在查询中添加order by子句。查询将是这样的

SELECT * FROM  vendors where staffpick='yes' and favvendor!='' order by totalfav desc;

#3

Try to sqlserver

尝试sqlserver

SELECT * 
FROM  vendors
ORDER BY case when staffpick ='YES' then totalfav end DESC,
         case when staffpick IS NULL then totalfav end DESC,
         case when staffpick IS NULL AND totalfav IS NULL then vendorname end ASC

#1