有一张桌子

code_1
value_1
code_2
value_2
...
code_10
value_10

而不是有3个表

Code, value和code_value

你永远不知道什么时候你可能需要10对以上的代码，价值。

如果只需要一对，就不会浪费磁盘空间。

select some_column, ...
from some_table
group by some_column

假设结果将按some_column排序。我在Sybase上看到过这种情况，其中假设成立(目前)。

编写查询的开发人员没有很好地了解SQL应用程序(包括单个查询和多用户系统)的快慢。这包括对以下方面的无知:

physical I/O minimization strategies, given that most queries' bottleneck is I/O not CPU perf impact of different kinds of physical storage access (e.g. lots of sequential I/O will be faster than lots of small random I/O, although less so if your physical storage is an SSD!) how to hand-tune a query if the DBMS produces a poor query plan how to diagnose poor database performance, how to "debug" a slow query, and how to read a query plan (or EXPLAIN, depending on your DBMS of choice) locking strategies to optimize throughput and avoid deadlocks in multi-user applications importance of batching and other tricks to handle processing of data sets table and index design to best balance space and performance (e.g. covering indexes, keeping indexes small where possible, reducing data types to minimum size needed, etc.)

同一查询中的相同子查询。

也许这不是一个反模式，但它惹恼了我，当某些数据库的DBA(好吧，我在这里说的是Oracle)用Oracle风格和代码约定编写SQL Server代码，当它运行如此糟糕时抱怨。受够了游标Oracle的人!SQL是基于设置的。

没有使用With子句或适当的连接并依赖子查询。

反模式:

select 
 ...
from data
where RECORD.STATE IN (
          SELECT STATEID
            FROM STATE
           WHERE NAME IN
                    ('Published to test',
                     'Approved for public',
                     'Published to public',
                     'Archived'
                    ))

好: 我喜欢使用with子句使我的意图更易于阅读。

with valid_states as (
          SELECT STATEID
            FROM STATE
           WHERE NAME IN
                    ('Published to test',
                     'Approved for public',
                     'Published to public',
                     'Archived'
                    )
select  ... from data, valid_states
where data.state = valid_states.state

最好的:

select 
  ... 
from data join states using (state)
where 
states.state in  ('Published to test',
                     'Approved for public',
                     'Published to public',
                     'Archived'
                    )