查询错误:列名ICUSTAY_ID不明确。在BigQuery中使用多个子查询

Question

您好，我收到以下查询错误“查询错误:列名ICUSTAY_ID不明确”，该错误指向倒数第三行的代码(请参阅以下代码)。你能帮帮我吗？非常感谢!

我是一个SQL初学者..

WITH t AS 
(
SELECT 
*
FROM
    (
    SELECT *,
DATETIME_DIFF(CHARTTIME, INTIME, MINUTE) AS pi_recorded 
FROM 
        (
        SELECT 
        * 
        FROM 
            (
            SELECT * FROM
                (SELECT i.SUBJECT_ID, p.dob, i.hadm_id, p.GENDER, a.ETHNICITY, a.ADMITTIME, a.INSURANCE, i.ICUSTAY_ID,
                i.DBSOURCE, i.INTIME, DATETIME_DIFF(a.ADMITTIME, p.DOB, DAY) AS age, 
                    CASE 
                WHEN DATETIME_DIFF(a.ADMITTIME, p.DOB, DAY) <= 32485  
                    THEN 'adult' 
                WHEN DATETIME_DIFF(a.ADMITTIME, p.DOB, DAY) > 32485  
                    then '>89'
                END AS age_group
                FROM `project.mimic3.ICUSTAYS` AS i
                INNER JOIN `project.mimic3.PATIENTS` AS p ON i.SUBJECT_ID = p.SUBJECT_ID  
                INNER JOIN `project.mimic3.ADMISSIONS` AS a ON i.HADM_ID = a.HADM_ID)
                WHERE age >= 6570
            ) AS t1
                LEFT JOIN 
                (
                SELECT ITEMID, ICUSTAY_ID, CHARTTIME, VALUE, FROM `project.mimic3.CHARTEVENTS` 
                WHERE ITEMID = 551 OR ITEMID = 552 OR ITEMID = 553 OR ITEMID = 224631 
                OR ITEMID = 224965 OR ITEMID = 224966
                ) AS t2
                ON t1.ICUSTAY_ID = t2.ICUSTAY_ID
        )
    ) 
WHERE ITEMID IN (552, 553, 224965, 224966) AND pi_recorded <= 1440
)
SELECT ICUSTAY_ID      #### Query error: Column name ICUSTAY_ID is ambiguous 
FROM t  
GROUP BY ICUSTAY_ID;

Answer 1

t1和t2都有一个名为ICUSTAY_ID的列，当你将它们连接到一个单独的数据集时，你最终会得到两个同名的列--这显然是行不通的，因为没有办法唯一地标识每一列。

您需要在代码中为这些列添加别名，或者如果不需要这两个列，则不要同时包含其中一个