tailieunhanh - Advanced SQL Database Programmer phần 2

Chương này tập trung vào chất lượng sản phẩm. Chúng tôi đề cập đến chất lượng thông tin trong một ý nghĩa rộng như chất lượng bao gồm hệ thống DB và chất lượng trình bày dữ liệu (xem hình 14,2). Trong thực tế, điều quan trọng là dữ liệu trong DB một cách chính xác phản ánh thế giới thực, đó là, các dữ liệu được chính xác. Nó cũng quan trọng đối với | FROM Foobar GROUP BY coll col2 col3 AS F1 col1 col2 col3 col4 WHERE 0 Using the assumption which is not given anywhere in the specification Tony decided that col4 has a constraint -- . col4 INTEGER NOT NULL CHECK col4 IN 0 1 Notice how doing this INSERT INTO statement would ruin his answer INSERT INTO Foobar col1 col2 col3 col4 VALUES 4 5 6 1 4 5 6 0 4 5 6 -1 But there is another problem. This is a procedural approach to the query even though it looks like SQL The innermost query builds groups based on the first three columns and gives you the summation of the fourth column within each group. That result named F1 is then passed to the containing query which then keeps only groups with all zeros under his assumption about the data. Now students what do we use to select groups from a grouped table The HAVING clause Mark Soukup noticed this was a redundant construction and offered this answer SELECT col1 col2 col3 0 AS col4zero FROM Foobar GROUP BY col1 col2 col3 HAVING SUM col4 0 Why is this an improvement The HAVING clause does not have to wait for the entire subquery to be built before it can go to work. In fact with a good optimizer it does not have to wait for an entire group to be built before dropping it from the results. 3 oracle However there is still that assumption about the values in col4. Roy Harvey came up with answer that gets round that problem SELECT coll col2 col3 0 AS col4zero FROM Foobar GROUP BY col1 col2 col3 HAVING COUNT SUM CASE WHEN col4 0 THEN 1 ELSE 0 END Using the CASE expression inside an aggregation function this way is a handy trick. The idea is that you count the number of rows in each group and count the number of zeros in col4 of each group and if they are the same then the group is one we want in the answer. However when most SQL compilers see an expression inside an aggregate function like SUM they have trouble optimizing the code. I came up with two approaches. Here is the first SELECT col1 col2 col3 .