使用WITH子句定义的多个(子)查询

我在理解WITH子句的工作方式时遇到了一些麻烦。以下是我所面临问题的一​​个最小示例。

Suppose I have a table called main_table with two columns col1 and col2 both taking binary text values, say A and B for col1 and C and D for col2. I want to create a query that filters the rows with A in col1 and another that further filters the rows taking value C in col2, because I might want to count the ratio of (A, C) over (A, D). I know there are many easier ways to do that, but say I want to do it with a WITH clause.

WITH query1 (is_A, col2) AS (
    SELECT col1, col2
    FROM main_table
    WHERE col1 = 'A'
),

query2 (is_AC) AS (
    SELECT
        CASE
            WHEN col2 = 'C' THEN 1
            ELSE 0
        END
    FROM query1
)

我的问题是:为什么我要完成此代码与

SELECT query1.col2, query2.is_AC
FROM queryI, queryJ;

I的结果J = 1,2与I的结果J = 2,1不同?为何在两种情况下都发生覆盖?

我很确定这篇文章是重复的,但是我找不到解决此现象的主题。

评论