我有一个包含 13 列的表格(一个用于行 ID #,另外 12 个代表一年中的每个月)。每列将只包含数字,我想编写几个查询来检查每行的某些条件并返回匹配的任何行的 ID。到目前为止,我已经很好地编写了所有基本的 SELECT 查询,但现在我在编写 SELECT 查询以一次检查多个条件而没有每次编写一百万行代码时有点卡住了。我正在编写的当前查询需要检查每一对连续月份(例如 Jan/Feb、Feb/Mar 等)以查看它们之间的差异有多大,并且它需要返回连续 2 个月份具有的任何行a > 20% 它们之间的差异,其余对都 例如,如果一月是 1000,二月是 1300,那么有 30% 的差异,所以这就是 1 手。那么如果说 4 月是 1500,5 月是 2100,那是 40% 的差异,所以是第二批。然后只要每隔一对(二月/三月、三月/四月、...、十一月/十二月)的差异都小于 10%,则需要返回该行。不幸的是,我可以让它工作的唯一方法是手动检查每一种可能性(有效),但对于编写类似的查询不是很可重用。这是我到目前为止所获得的内容的缩写版本:SELECT pkIDFROM dbo.tblMonthDataWHERE((colFeb > colJan * 1.2 AND colMar > colFeb * 1.2) AND (colApr < colMar * 1.1 AND colMay < colApr * 1.1 AND colJun < colMay * 1.1 AND colJul < colJun * 1.1 AND colAug < colJul * 1.1 AND colSep < colAug * 1.1 AND colOct < colSep * 1.1 AND colNov < colOct * 1.1 AND colDec < colNov * 1.1))OR ((colFeb > colJan * 1.2 AND colApr > colMar * 1.2) AND (colMar < colFeb * 1.1 AND colMay < colApr * 1.1 AND colJun < colMay * 1.1 AND colJul < colJun * 1.1 AND colAug < colJul * 1.1 AND colSep < colAug * 1.1 AND colOct < colSep * 1.1 AND colNov < colOct * 1.1 AND colDec < colNov * 1.1))OR ((colFeb > colJan * 1.2 AND colMay > colApr * 1.2) AND (colMar < colFeb * 1.1 AND colApr < colMar * 1.1 AND colJun < colMay * 1.1 AND colJul < colJun * 1.1 AND colAug < colJul * 1.1 AND colSep < colAug * 1.1 AND colOct < colSep * 1.1 AND colNov < colOct * 1.1 AND colDec < colNov * 1.1))...OR ((colNov > colOct * 1.2 AND colDec > colNov * 1.2) AND (colFeb < colJan * 1.1 AND colMar < colFeb * 1.1 AND colApr < colMar * 1.1 AND colMay < colApr * 1.1 AND colJun < colMay * 1.1 AND colJul < colJun * 1.1 AND colAug < colJul * 1.1 AND colSep < colAug * 1.1 AND colOct < colSep * 1.1))总共有大约 55 行 OR 语句来检查每个可能的组合。如果我然后去查询类似的东西(例如,返回所有行 ID #,其中 2 对大于 50%,4 对小于 10%),这涉及从头开始编写另一个长查询,检查所有其他可能的组合。所以我想知道如何在更短的版本中重新编写它,以便对类似的查询更可重用? 最佳答案 因此,作为@Pieter 答案的替代答案并说明更简单的数据结构如何使您的任务更容易,我建议如下:create view tblEasy as (select pkID, 1 as colMonth, colJan as colValue from tblMonthDataUNIONselect pkID, 2 as colMonth, colFeb as colValue from tblMonthDataUNIONselect pkID, 3 as colMonth, colMar as colValue from tblMonthDataUNIONselect pkID, 4 as colMonth, colApr as colValue from tblMonthDataUNIONselect pkID, 5 as colMonth, colMay as colValue from tblMonthDataUNIONselect pkID, 6 as colMonth, colJun as colValue from tblMonthDataUNIONselect pkID, 7 as colMonth, colJul as colValue from tblMonthDataUNIONselect pkID, 8 as colMonth, colAug as colValue from tblMonthDataUNIONselect pkID, 9 as colMonth, colSep as colValue from tblMonthDataUNIONselect pkID, 10 as colMonth, colOct as colValue from tblMonthDataUNIONselect pkID, 11 as colMonth, colNov as colValue from tblMonthDataUNIONselect pkID, 12 as colMonth, colDec as colValue from tblMonthData);这使 View 看起来像我最初构造表格的方式。然后通过将 colMonth 上的值与 colMonth + 1 上的值进行比较,很容易创建对。我做了一个 fiddle 来说明如何在 View 中进行比较,然后查询本身是否相当明显。 http://sqlfiddle.com/#!3/600f6/4请注意,由于初始表结构,性能不是很好。 更新 由于这被接受为答案,我将嵌入 sqlfiddle 中的额外细节。预先计算连续月份之间差异的额外 View :create view tblPairs as ( select t1.pkId , t1.colMonth as colStart, (t2.colValue * 100 / t1.colValue) as colPercentage from tblEasy as t1 inner join tblEasy as t2 on t1.pkId = t2.pkId and t1.colMonth = t2.colMonth - 1);查询其中 2 个月的增幅超过 20% 而其他 9 个月的增幅低于 10%:select distinct pkidfrom tblPairs as t1where 2 = ( select count(*) from tblPairs as t2 where t2.pkid = t1.pkid and colPercentage >= 120)and 9 = ( select count(*) from tblPairs as t2 where t2.pkid = t1.pkid and colPercentage <= 110);关于sql - 如何在没有一百万个 AND/OR 的情况下缩短此 SQL 查询?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/25353670/