MySQL获取行，但更喜欢一个列值 [英] MySQL get rows but prefer one column value over another

问题描述

有点奇怪，我想编写一个MySQL查询，该查询将从表中获取结果，但更喜欢列的一个值而不是另一个值，即

A bit of a strange one, I want to write a MySQL query that will get results from a table, but prefer one value of a column over another, ie

id   name    value   prioirty
1    name1   value1  NULL
2    name1   value1  1
3    name2   value2  NULL
4    name3   value3  NULL

所以这里name1有两个条目，但其中一个的优先级为1.我想从表中获取所有值，但是更喜欢优先考虑的值.

So here name1 has two entries, but one has a prioirty of 1. I want to get all the values from the table, but prefer the values with whatever priorty I'm after.

我想要的结果是

id   name    value   prioirty
2    name1   value1  1
3    name2   value2  NULL
4    name3   value3  NULL

一种等效的说法是从表中获取所有行，但优先使用优先级为x的行".

An equivalent way of saying it would be 'get all rows from the table, but prefer rows with a priority of x'.

推荐答案

您需要先重新设计表格.

You need to redesign your table first.

应该是:

YourTable (Id, Name, Value)
YourTablePriority (PriorityId, Priority, Id)

更新:

select * from YourTable a 
where a.Id not in 
   (select b.Id from YourTablePriority b)

这应该在sql server中起作用，您可能需要做一些改动才能使其在mysql中起作用.

This should work in sql server, you may need a little change to make it work in mysql.

这篇关于MySQL获取行，但更喜欢一个列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！