我们刚刚在jOOQ中添加了对EVERY()
聚合函数( #1391 )的支持 ,并希望借此机会让您知道可以在EVERY(now and then)
都派上用场的真正的SQL gem(双关语EVERY(now and then)
)。
假设我们的桌子上有四本书:
INSERT INTO book VALUES (1, 1, '1984');
INSERT INTO book VALUES (2, 1, 'Animal Farm');
INSERT INTO book VALUES (3, 2, 'O Alquimista');
INSERT INTO book VALUES (4, 2, 'Brida');
现在的问题是:
EVERY()
ID是否小于10?
我们会问:
SELECT EVERY(id < 10)
FROM book
答案是:
every
-----
true
每个作者的
EVERY()
书是否都以字母'a'结尾?
我们会问:
SELECT author_id, EVERY(title LIKE '%a')
FROM book
GROUP BY author_id
答案是:
author_id every
-----------------
1 false
2 true
精彩!
与所有聚合函数一样, 我们甚至可以将它们用作窗口函数 !
SELECT
book.*,
EVERY(title LIKE '%a') OVER (PARTITION BY author_id)
FROM book
会产生:
id author_id title every
------------------------------------
1 1 1984 false
2 1 Animal Farm false
3 2 O Alquimista true
4 2 Brida true
谁支持
好吧,SQL标准有它:
10.9 <aggregate function> <aggregate function> ::= COUNT <left paren> <asterisk> <right paren> [ <filter clause> ] | <general set function> [ <filter clause> ] | <binary set function> [ <filter clause> ] | <ordered set function> [ <filter clause> ] | <array aggregate function> [ <filter clause> ] <general set function> ::= <set function type> <left paren> [ <set quantifier> ] <value expression> <right paren> <set function type> ::= <computational operation> <computational operation> ::= AVG | MAX | MIN | SUM | EVERY <-- yes, here! EVERY! | ANY | SOME | COUNT | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP | COLLECT | FUSION | INTERSECTION
但是,如果您的数据库不是PostgreSQL,请不要担心。 EVERY()
可以上模拟EVERY()
使用数据库SUM()
和CASE
表达式。 这是模拟第一个查询的方法:
-- SELECT EVERY(id < 10)
-- FROM book
SELECT CASE SUM(CASE WHEN id < 10 THEN 0 ELSE 1 END)
WHEN 0 THEN 1
ELSE 0
END
FROM book;
或作为窗口功能
-- SELECT
-- book.*,
-- EVERY(title LIKE '%a') OVER (PARTITION BY author_id)
-- FROM book
SELECT
book.*,
CASE SUM(CASE WHEN title LIKE '%a' THEN 0 ELSE 1 END)
OVER(PARTITION BY author_id)
WHEN 0 THEN 1
ELSE 0
END
FROM book;
而且,像本博客一样,我们很高兴得出结论, 即将推出的jOOQ 3.6现在将为您处理EVERY(emulation)
,因此您可以编写:
DSL.using(configuration)
.select(BOOK.fields())
.select(every(BOOK.TITLE.like("%a"))
.over(partitionBy(BOOK.AUTHOR_ID)))
.from(BOOK)
.fetch();
玩这个新功能吧!