SQL:如果字段不为空，如何按字段排序，否则使用另一个字段 [英] SQL: how do i order by a field if its not null else use another field

问题描述

如果我的Posts不为null(后修改b4)，我想按dtModified(修改日期时间)对我的Posts进行排序，否则，按dtPosted(发布日期时间)进行排序

i want to sort my Posts by dtModified (datetime modified) if its not null (post modified b4), else sort by dtPosted (datetime posted)

推荐答案

似乎我每周在这里就此提出3次建议，也许我应该放弃并放任它:-)不，我不要这样认为:

It seems like I'm giving this advice three times a week here on SO, maybe I should just give up and let it go :-) Nah, I don't think so:

不要在列计算(或order by子句)中使用逐行函数.您应该针对特定情况检查性能(测量，不要猜测)，但是读取数据库时进行计算通常会影响您的扩展能力(这无关紧要)用于您的地址簿数据库，但是我工作的商店有大量数据).

Don't use per-row functions in your column calculations (or order by clauses) if you want your database to scale well. You should check performance for your particular case (measure, don't guess) but doing calculations when reading the database will generally affect your ability to scale (this won't matter for your address book database but the shops I work in have huge amounts of data).

如何使我的数据库运行得更快?"的数量问题远远超过我如何使用更少的空间?"那些.这是一条牺牲磁盘空间以提高性能的好方法.

The number of "how do I get my DB to go faster?" questions far outweighs the number of "how do I use less space?" ones. It's a well-trodden path to sacrifice disk space for performance.

正确的时间是在数据更改时.这样，更改的成本将在所有读取中摊销.

The right time to do calculations is when the data changes. That way the cost of the changes is amortised across all reads.

我的建议是创建另一个列，例如dtLastAction来包含排序值，然后使用插入/更新触发器将其设置为与dtModified相同(如果不为null)，或者与dtPosted相同(如果dtModified为空值.从技术上讲，这违反了3NF，但是如果您知道自己在做什么就可以了，并且触发器可以确保这种情况下的数据一致性.

My advice is to create another column such as dtLastAction to contain the ordering value then use an insert/update trigger to set it to the same as dtModified if not null, or dtPosted if dtModified is null. Technically, this violates 3NF but that's okay if you know what you're doing, and the triggers guarantee data consistency in that case.

然后在dtLastAction列上进行索引，以提高查询速度，而在插入和更新过程中以(较少的)额外工作量(较少)为代价.我之所以这么说是因为， vast 大多数数据库表的读取比写入的频率要高(显然，如果您的特殊情况是非常罕见的例外之一，则此方法无用).

Then index on the dtLastAction column and see the speed of your queries improve at the (lesser) cost of some extra work during inserts and updates. I say lesser because the vast majority of database tables are read more often than written (obviously this method is useless if your particular situation is one of the very rare exceptions).

或者，对于这种特殊情况，可以在创建条目时将dtModified和dtPosted设置为相同的值，这意味着dtModified永远不会为 为null.您仍然可以检测到由于这两个日期时间值相同而从未被修改过的帖子.

Alternatively, for this particular case, you could set dtModified and dtPosted to the same value when creating the entry, meaning that dtModified will never be null. You can still detect posts that have never been modified by the fact that these two datetime values are identical.

这篇关于SQL:如果字段不为空，如何按字段排序，否则使用另一个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！