vớisoi kèo bóng đá truoctran (biểu thức bảng chung)#vớiCung cấp một cách để viết các câu lệnh phụ trợ để sử dụng trong một soi kèo bóng đá truoctran lớn hơn. Những câu lệnh này, thường được gọi là biểu thức bảng phổ biến hoặcCTEs, có thể được coi là xác định các bảng tạm thời tồn tại chỉ cho một soi kèo bóng đá truoctran. Mỗi câu lệnh phụ trợ trong mộtvớimệnh đề có thể là mộtChọn, Chèn, Cập nhật, XóahoặcHợp nhất; vàvớimệnh đề được gắn vào một câu lệnh chính cũng có thể là mộtChọn, Chèn, Cập nhật, XóahoặcHợp nhất.
Chọninvới #Giá trị cơ bản củaChọninvớilà chia nhỏ các soi kèo bóng đá truoctran phức tạp thành các phần đơn giản hơn. Một ví dụ là:
với khu vực_sales AS (
hiển thị tổng doanh số sản phẩm chỉ trong soi kèo bóng đá truoctran khu vực bán hàng hàng đầu. Thevớimệnh đề xác định hai câu lệnh phụ trợ có tênkhu vực_salesvàTop_Regions, trong đó đầu ra củakhu vực_salesđược sử dụng trongTop_Regionsvà đầu ra củaTop_Regionsđược sử dụng trong chínhChọnsoi kèo bóng đá truoctran. Ví dụ này có thể đã được viết mà không cóvới, nhưng chúng tôi đã cần hai cấp độ phụ-Chọns. Theo cách này dễ dàng hơn một chút.
Tùy chọnKiểu đệ quythay đổi công cụ sửa đổivớiTừ một sự thuận tiện của cú pháp thành một tính năng hoàn thành mọi thứ không thể có trong SQL tiêu chuẩn. Sử dụngđệ quy, Avớisoi kèo bóng đá truoctran có thể tham khảo đầu ra của chính nó. Một ví dụ rất đơn giản là soi kèo bóng đá truoctran này để tổng hợp các số nguyên từ 1 đến 100:
với đệ quy t (n) là (
Hình thức chung của một đệ quyvớisoi kèo bóng đá truoctran luôn làThuật ngữ không nhận được, sau đóUnion(hoặcLiên minh tất cả), sau đó AThuật ngữ đệ quy, trong đó chỉ có thuật ngữ đệ quy mới có thể chứa một tham chiếu đến đầu ra của soi kèo bóng đá truoctran. Một soi kèo bóng đá truoctran như vậy được thực thi như sau:
Đánh giá soi kèo bóng đá truoctran đệ quy
Đánh giá thuật ngữ không nhận được. VìUnion(nhưng khôngLiên minh tất cả), loại bỏ các hàng trùng lặp. Bao gồm tất cả các hàng còn lại trong kết quả của soi kèo bóng đá truoctran đệ quy và cũng đặt chúng vào tạm thờiBảng làm việc.
Miễn là bảng làm việc không trống, hãy lặp lại soi kèo bóng đá truoctran bước sau:
Đánh giá thuật ngữ đệ quy, thay thế soi kèo bóng đá truoctran nội dung hiện tại của bảng làm việc cho sự tự tham chiếu đệ quy. VìUnion(nhưng khôngLiên minh tất cả), loại bỏ các hàng và hàng trùng lặp sao chép bất kỳ hàng kết quả trước đó. Bao gồm tất cả các hàng còn lại trong kết quả của soi kèo bóng đá truoctran đệ quy và cũng đặt chúng vào tạm thờiBảng trung gian.
Thay thế nội dung của bảng làm việc bằng nội dung của bảng trung gian, sau đó làm trống bảng trung gian.
trong khiđệ quyCho phép các soi kèo bóng đá truoctran được chỉ định đệ quy, bên trong các soi kèo bóng đá truoctran như vậy được đánh giá lặp đi lặp lại.
Trong ví dụ trên, bảng làm việc chỉ có một hàng duy nhất trong mỗi bước và nó có soi kèo bóng đá truoctran giá trị từ 1 đến 100 trong soi kèo bóng đá truoctran bước liên tiếp. Trong bước thứ 100, không có đầu ra vìWHEREmệnh đề, và do đó, soi kèo bóng đá truoctran chấm dứt.
soi kèo bóng đá truoctran đệ quy thường được sử dụng để xử lý dữ liệu phân cấp hoặc có cấu trúc cây. Một ví dụ hữu ích là soi kèo bóng đá truoctran này để tìm tất cả các phần phụ trực tiếp và gián tiếp của sản phẩm, chỉ được cung cấp một bảng hiển thị các vùi ngay lập tức:
với đệ quy bao gồm_parts (sub_part, part, số lượng) là (
Khi tính toán một đường truyền bằng cây bằng soi kèo bóng đá truoctran đệ quy, bạn có thể muốn đặt hàng kết quả theo thứ tự đầu tiên hoặc chiều rộng đầu tiên. Điều này có thể được thực hiện bằng cách tính toán một cột đặt hàng cùng với các cột dữ liệu khác và sử dụng nó để sắp xếp kết quả ở cuối.
Để tạo một thứ tự đầu tiên, chúng tôi tính toán cho mỗi hàng kết quả Một mảng các hàng mà chúng tôi đã truy cập cho đến nay. Ví dụ: xem xét soi kèo bóng đá truoctran sau tìm kiếm một bảngcâySử dụng Alinktrường:
với search_tree đệ quy (id, liên kết, dữ liệu) là (
Để thêm thông tin đặt hàng đầu tiên, bạn có thể viết bài này:
với search_tree đệ quy (id, liên kết, dữ liệu,Path) như (mảng [t.id]Từ cây tĐường dẫn || T.IDTừ cây T, search_tree stĐặt hàng theo | Hàng (g.f1, g.f2);
Trong trường hợp chung trong đó cần sử dụng nhiều hơn một trường để xác định một hàng, hãy sử dụng một mảng hàng. Ví dụ: nếu chúng ta cần theo dõi soi kèo bóng đá truoctran trườngF1vàf2:
với search_tree đệ quy (id, liên kết, dữ liệu,Path) như (mảng [hàng (t.f1, t.f2)]Từ cây tĐường dẫn || Hàng (t.f1, t.f2)Từ cây T, search_tree stĐặt hàng theo | Hàng (g.f1, g.f2);
bỏ quaRow ()Cú pháp trong trường hợp phổ biến trong đó chỉ cần một trường cần được theo dõi. Điều này cho phép một mảng đơn giản thay vì sử dụng mảng loại tổng hợp, đạt được hiệu quả.
Để tạo thứ tự đầu tiên, bạn có thể thêm một cột theo dõi độ sâu của tìm kiếm, ví dụ:
với search_tree đệ quy (id, liên kết, dữ liệu,chiều sâu) như (0Từ cây tĐộ sâu + 1Từ cây T, search_tree stĐặt hàng theo chiều sâu;
Để có được sắp xếp ổn định, hãy thêm soi kèo bóng đá truoctran cột dữ liệu làm cột sắp xếp thứ cấp.
Thuật toán đánh giá soi kèo bóng đá truoctran đệ quy tạo ra đầu ra của nó theo thứ tự tìm kiếm đầu tiên trên chiều rộng. Tuy nhiên, đây là một chi tiết thực hiện và có lẽ không có căn cứ để dựa vào nó.
Có cú pháp tích hợp để tính toán cột sắp xếp độ sâu hoặc chiều rộng đầu tiên. Ví dụ:
với search_tree đệ quy (id, liên kết, dữ liệu) là (Tìm kiếm độ sâu đầu tiên bởi ID đặt OrderColChọn * từ search_tree đơn hàng theo OrderCol;Tìm kiếm chiều rộng đầu tiên bởi ID đặt OrderColChọn * từ search_tree đặt hàng theo OrderCol;
Cú pháp này được mở rộng bên trong một cái gì đó tương tự như soi kèo bóng đá truoctran hình thức viết tay trên. TheTìm kiếmĐiều khoản chỉ định xem tìm kiếm đầu tiên về độ sâu hoặc chiều rộng là muốn, danh sách soi kèo bóng đá truoctran cột để theo dõi để sắp xếp và tên cột sẽ chứa dữ liệu kết quả có thể được sử dụng để sắp xếp. Cột đó sẽ hoàn toàn được thêm vào soi kèo bóng đá truoctran hàng đầu ra của CTE.
Khi làm việc với các soi kèo bóng đá truoctran đệ quy, điều quan trọng là phải chắc chắn rằng phần đệ quy của soi kèo bóng đá truoctran cuối cùng sẽ không trả lời không, nếu không thì soi kèo bóng đá truoctran sẽ lặp lại vô thời hạn. Đôi khi, sử dụngUnionthay vìLiên minh tất cảCó thể thực hiện điều này bằng cách loại bỏ soi kèo bóng đá truoctran hàng trùng lặp soi kèo bóng đá truoctran hàng đầu ra trước đó. Tuy nhiên, thường thì một chu kỳ không liên quan đến soi kèo bóng đá truoctran hàng đầu ra hoàn toàn trùng lặp: có thể cần phải kiểm tra chỉ một hoặc một vài trường để xem liệu cùng một điểm đã đạt được trước đó.đồ thịSử dụng ALinktrường:
với search_graph (id, liên kết, dữ liệu, độ sâu) là (
soi kèo bóng đá truoctran này sẽ lặp lại nếulinkMối quan hệ chứa soi kèo bóng đá truoctran chu kỳ. Bởi vì chúng tôi yêu cầuDEPTHHồiđầu ra, chỉ thay đổiLiên minh tất cảđếnUnionsẽ không loại bỏ vòng lặp. Thay vào đó, chúng ta cần nhận ra liệu chúng ta có đạt được cùng một hàng một lần nữa trong khi đi theo một đường dẫn liên kết cụ thể hay không.IS_CYCLEvà| Hàng (g.f1, g.f2)đến soi kèo bóng đá truoctran dễ bị lặp lại:
với search_graph (id, liên kết, dữ liệu, độ sâu,IS_CYCLE, đường dẫn) như (Sai,Từ đồ thị gg.id = any (| G.ID),Từ đồ thị G, search_graph SGvà không is_cycle)
Ngoài soi kèo bóng đá truoctran chu kỳ ngăn chặn, giá trị mảng thường hữu ích theo cách riêng của nó như là đại diện chođường dẫnĐược thực hiện để đạt được bất kỳ hàng cụ thể nào.
Trong trường hợp chung trong đó cần kiểm tra nhiều hơn một trường để nhận ra một chu kỳ, hãy sử dụng một mảng hàng. Ví dụ: nếu chúng ta cần so sánh soi kèo bóng đá truoctran trườngF1vàF2:
với search_graph đệ quy (id, liên kết, dữ liệu, độ sâu,IS_CYCLE, đường dẫn) như (Sai,Từ đồ thị ghàng (g.f1, g.f2) = any (| Hàng (g.f1, g.f2)),Từ đồ thị G, search_graph SGvà không is_cycle)
bỏ quahàng ()Cú pháp trong trường hợp phổ biến trong đó chỉ cần kiểm tra một trường để nhận ra một chu kỳ. Điều này cho phép một mảng đơn giản thay vì sử dụng mảng loại tổng hợp, đạt được hiệu quả.
Có cú pháp tích hợp để đơn giản hóa phát hiện chu kỳ. soi kèo bóng đá truoctran trên cũng có thể được viết như thế này:
với search_graph (ID, liên kết, dữ liệu, độ sâu) là (ID chu kỳ đặt IS_CYCLE bằng đường dẫnChọn * từ search_graph;
Và nó sẽ được viết lại nội bộ theo mẫu trên. TheChu kỳĐiều khoản chỉ định đầu tiên danh sách soi kèo bóng đá truoctran cột theo dõi phát hiện chu kỳ, sau đó một tên cột sẽ hiển thị liệu một chu kỳ đã được phát hiện và cuối cùng là tên của một cột khác sẽ theo dõi | G.ID. soi kèo bóng đá truoctran cột chu kỳ và | G.ID sẽ hoàn toàn được thêm vào soi kèo bóng đá truoctran hàng đầu ra của CTE.
Cột đường dẫn chu kỳ được tính toán theo cách tương tự như cột thứ tự đầu tiên độ sâu hiển thị trong phần trước. soi kèo bóng đá truoctran có thể có cả ATìm kiếmvà AChu kỳ27600_27762Chu kỳmệnh đề và thứ tự theo cột đường dẫn. Nếu muốn thứ tự đầu tiên được muốn, thì chỉ định cảTìm kiếmvàChu kỳcó thể hữu ích.
Một thủ thuật hữu ích để kiểm tra các soi kèo bóng đá truoctran khi bạn không chắc chắn nếu chúng có thể lặp là đặt mộtgiới hạnTrong soi kèo bóng đá truoctran cha mẹ. Ví dụ, soi kèo bóng đá truoctran này sẽ lặp lại mãi mãi mà không cầngiới hạn:
với T (n) đệ quy là (Giới hạn 100;
Điều này hoạt động vìPostgreSQLS Triển khai chỉ đánh giá chỉ có nhiều hàng của Avớisoi kèo bóng đá truoctran như thực sự được tìm kiếm bởi soi kèo bóng đá truoctran cha mẹ. Sử dụng thủ thuật này trong sản xuất không được khuyến khích, bởi vì các hệ thống khác có thể hoạt động khác nhau.vớiđầu ra của soi kèo bóng đá truoctran.
Một thuộc tính hữu ích củavớisoi kèo bóng đá truoctran là chúng thường chỉ được đánh giá một lần mỗi lần thực hiện soi kèo bóng đá truoctran cha mẹ, ngay cả khi chúng được nhắc đến nhiều lần bởi soi kèo bóng đá truoctran cha mẹ hoặc anh chị emvớisoi kèo bóng đá truoctran. Do đó, các tính toán đắt tiền cần thiết ở nhiều nơi có thể được đặt trong mộtvớisoi kèo bóng đá truoctran để tránh công việc dự phòng. Một ứng dụng khả thi khác là ngăn chặn nhiều đánh giá không mong muốn về các chức năng với tác dụng phụ.vớisoi kèo bóng đá truoctran, vì điều đó có thể ảnh hưởng đến tất cả việc sử dụngvớiĐầu ra của soi kèo bóng đá truoctran khi nó chỉ ảnh hưởng đến một. Tham khảo nhânvớisoi kèo bóng đá truoctran sẽ được đánh giá là viết, mà không cần triệt tiêu các hàng mà soi kèo bóng đá truoctran cha mẹ có thể loại bỏ sau đó. (Nhưng, như đã đề cập ở trên, đánh giá có thể dừng sớm nếu (các) tham chiếu đến yêu cầu soi kèo bóng đá truoctran chỉ có một số lượng hạn chế.)
Tuy nhiên, nếu Avớisoi kèo bóng đá truoctran không được nhận và không có tác dụng phụ (nghĩa là, đó là mộtChọnKhông chứa các chức năng dễ bay hơi) sau đó nó có thể được gấp lại vào soi kèo bóng đá truoctran cha mẹ, cho phép tối ưu hóa khớp của hai mức soi kèo bóng đá truoctran. Theo mặc định, điều này xảy ra nếu soi kèo bóng đá truoctran cha mẹ tham chiếuvớisoi kèo bóng đá truoctran chỉ một lần, nhưng không phải nếu nó tham khảovớisoi kèo bóng đá truoctran nhiều lần. Bạn có thể ghi đè quyết định đó bằng cách chỉ địnhvật chất hóaĐể buộc tính toán riêng biệtvớisoi kèo bóng đá truoctran hoặc bằng cách chỉ địnhkhông được vật chất hóaĐể buộc nó được hợp nhất vào soi kèo bóng đá truoctran cha mẹ. Lựa chọn sau có nguy cơ tính toán trùng lặp củavớisoi kèo bóng đá truoctran, nhưng nó vẫn có thể tiết kiệm ròng nếu mỗi lần sử dụngvớisoi kèo bóng đá truoctran chỉ cần một phần nhỏ củavớiđầu ra đầy đủ của soi kèo bóng đá truoctran.
Một ví dụ đơn giản về soi kèo bóng đá truoctran quy tắc này là
Với W AS (
thisvớisoi kèo bóng đá truoctran sẽ được gấp lại, tạo ra cùng một kế hoạch thực thi như
Chọn * từ Big_Table WHERE Key = 123;
Đặc biệt, nếu có chỉ mục trênKey, nó có thể sẽ được sử dụng để tìm nạp soi kèo bóng đá truoctran hàng cókey = 123. Mặt khác, trong
Với W AS (
Thevớisoi kèo bóng đá truoctran sẽ được vật chất hóa, tạo ra một bản sao tạm thời củaBIG_TABLESau đó được kết hợp với chính nó - mà không có lợi ích của bất kỳ chỉ số nào. soi kèo bóng đá truoctran này sẽ được thực hiện hiệu quả hơn nhiều nếu được viết là
Với W không được vật chất hóa (
để có thể áp dụng các hạn chế của soi kèo bóng đá truoctran cha mẹ trực tiếp cho các lần quétBIG_TABLE.
Một ví dụ trong đókhông được vật chất hóacó thể không mong muốn là
Với W AS (
Ở đây, vật chất hóavớisoi kèo bóng đá truoctran đảm bảo rằngrấtchỉ được đánh giá một lần cho mỗi hàng bảng, không hai lần.
soi kèo bóng đá truoctran ví dụ trên chỉ hiển thịvớiđang được sử dụng vớiChọn, nhưng nó có thể được đính kèm theo cùng một cách vớiChèn, Cập nhật, XóahoặcMerge. Trong mỗi trường hợp, nó cung cấp (soi kèo bóng đá truoctran) bảng tạm thời có thể được đề cập trong lệnh chính.
với #Bạn có thể sử dụng soi kèo bóng đá truoctran câu lệnh biến đổi dữ liệu (Chèn, Cập nhật, XóahoặcHợp nhất) trongvới. Điều này cho phép bạn thực hiện một số hoạt động khác nhau trong cùng một soi kèo bóng đá truoctran.
với di chuyển_rows AS (
soi kèo bóng đá truoctran này di chuyển hiệu quả các hàng từSản phẩmđếnSản phẩm_log. TheXóainvớiXóa soi kèo bóng đá truoctran hàng được chỉ định khỏiSản phẩm, Trả lại nội dung của chúng bằng phương tiện của nóTrở vềmệnh đề; Và sau đó soi kèo bóng đá truoctran chính đọc đầu ra đó và chèn nó vàoSản phẩm_log.
Một điểm tốt của ví dụ trên làvớimệnh đề được gắn vàoChèn, không phải phụ-ChọntrongChèn. Điều này là cần thiết vì soi kèo bóng đá truoctran câu lệnh biến đổi dữ liệu chỉ được phép trongvớisoi kèo bóng đá truoctran điều khoản được gắn vào câu lệnh cấp cao nhất. Tuy nhiên, bình thườngvớiQuy tắc hiển thị áp dụng, vì vậy có thể tham khảovớiĐầu ra của câu lệnh từ Sub-Chọn.
Câu lệnh sửa đổi dữ liệu trongvớithường cóTrở vềmệnh đề (xemPhần 6.4), như được hiển thị trong ví dụ trên. Nó là đầu ra củaTrở vềmệnh đề,khôngBảng mục tiêu của câu lệnh biến đổi dữ liệu, tạo thành bảng tạm thời có thể được đề cập bởi phần còn lại của soi kèo bóng đá truoctran. Nếu câu lệnh biến đổi dữ liệu trongvớithiếu ATrở vềmệnh đề, sau đó nó tạo thành không có bảng tạm thời và không thể được gọi trong phần còn lại của soi kèo bóng đá truoctran. Một tuyên bố như vậy sẽ được thực hiện dù sao.
với t As (
Ví dụ này sẽ xóa tất cả soi kèo bóng đá truoctran hàng khỏi bảngfoovàBar. Số lượng soi kèo bóng đá truoctran hàng bị ảnh hưởng được báo cáo cho khách hàng sẽ chỉ bao gồm soi kèo bóng đá truoctran hàng bị xóa khỏiBar.
Không được phép tự giới thiệu trong soi kèo bóng đá truoctran câu lệnh biến đổi dữ liệu. Trong một số trường hợp, có thể làm việc xung quanh giới hạn này bằng cách tham khảo đầu ra của một đệ quyvớiVí dụ:
với đệ quy bao gồm_parts (sub_part, part) là (
soi kèo bóng đá truoctran này sẽ xóa tất cả các phần phụ trực tiếp và gián tiếp của sản phẩm.
Câu lệnh sửa đổi dữ liệu trongvớiđược thực hiện chính xác một lần và luôn luôn hoàn thành, độc lập với việc soi kèo bóng đá truoctran chính đọc tất cả (hoặc thực sự là bất kỳ) đầu ra của chúng. Lưu ý rằng điều này khác với quy tắc choChọninvới: Như đã nêu trong phần trước, thực hiện AChọnchỉ được thực hiện theo như soi kèo bóng đá truoctran chính yêu cầu đầu ra của nó.
soi kèo bóng đá truoctran tiểu thư trongvớiđược thực hiện đồng thời với nhau và với soi kèo bóng đá truoctran chính. Do đó, khi sử dụng các câu lệnh biến đổi dữ liệu trongvới, thứ tự soi kèo bóng đá truoctran bản cập nhật được chỉ định thực sự xảy ra là không thể đoán trước. Tất cả soi kèo bóng đá truoctran câu lệnh được thực thi với cùng mộtSnapshot(xemChương 13), vì vậy họ không thểxemHiệu ứng của nhau trên soi kèo bóng đá truoctran bảng đích. Điều này làm giảm bớt soi kèo bóng đá truoctran tác động của sự khó lường của thứ tự thực tế của soi kèo bóng đá truoctran bản cập nhật hàng và có nghĩa làTrở vềDữ liệu là cách duy nhất để soi kèo bóng đá truoctranền đạt các thay đổi giữa các khác nhauvớiTiểu phụ và soi kèo bóng đá truoctran chính. Một ví dụ về điều này là trong
với t As (
bên ngoàiChọnSẽ trả lại giá ban đầu trước khi hành động củaCập nhật, trong khi ở
với T AS (
bên ngoàiChọnSẽ trả về dữ liệu được cập nhật.
Cố gắng cập nhật cùng một hàng hai lần trong một câu không được hỗ trợ. Chỉ có một trong những sửa đổi diễn ra, nhưng nó không dễ dàng (và đôi khi không thể) để dự đoán một cách đáng tin cậy cái nào.vớisoi kèo bóng đá truoctran tiểu bang có thể ảnh hưởng đến cùng một hàng thay đổi bởi câu lệnh chính hoặc một câu chuyện con anh chị em. soi kèo bóng đá truoctran hiệu ứng của một tuyên bố như vậy sẽ không thể dự đoán được.
Hiện tại, bất kỳ bảng nào được sử dụng làm mục tiêu của câu lệnh biến đổi dữ liệu trongvớikhông được có quy tắc có điều kiện, cũng không phảicũngquy tắc, cũng không phảiThay vào đóQuy tắc mở rộng sang nhiều câu lệnh.