MySQL:表别名不工作

Question

我想重写这个查询，这样它就可以使用表和列别名，但是我无法让它工作。经过一些研究，我发现我们不能在WHERE子句中使用table别名，我试图将其更改为有，但仍然没有工作.

有人有更好的主意吗？

这是一个查询：

SELECT   pos.pat_id+1000 as Barcode
        ,pat_sex as Sex 
        ,pat_dob as DoB
        ,Test 
        ,Required_diagnosis
        ,Sample_date
        ,Location 
        ,DQ_list

FROM    (SELECT  icv_pat_id as pat_id
            ,icv_test_name as Test
            ,'Trichomonas' as Required_diagnosis
            ,lrr_request_date as Sample_date
            ,d.Location
            ,d.DQ_List          
        FROM investigation_component_values
        INNER JOIN lab_result_records l on l.lrr_rrc_id = icv_rrc_id
        LEFT JOIN lookups.dq_lists d ON d.lrr_ordering_location_name = l.lrr_ordering_location_name
        WHERE lrr_request_date BETWEEN '2015-08-01' AND '2015-08-31'
        AND icv_pat_id NOT IN (SELECT pat_id FROM cnwlreports.test_patients)
        AND icv_ist_id = 83882
        AND icv_non_quantifiable_result = 'Trichomonas vaginalis ISOLATED') as pos
LEFT JOIN trungnguyen.patients pat on  pos.pat_id = pat.pat_id

WHERE pos.pat_id NOT IN (SELECT DISTINCT p.pat_id
                        FROM pos p
                        LEFT JOIN (SELECT    rrc_pat_id as pat_id
                                            ,rrc_clinic_date as diag_date                                               
                                    FROM referral_records 
                                    WHERE rrc_status = 'approved'
                                    AND rrc_answered_id = 17380
                                    AND rrc_clinic_date BETWEEN DATE_SUB('2015-08-01',INTERVAL 7 DAY) 
                                    AND DATE_ADD('2015-08-31',INTERVAL 7 DAY) 
                                    AND rrc_pat_id IN (SELECT pat_id FROM pos)
                                    GROUP BY    rrc_pat_id 
                                                ,rrc_clinic_Date) d ON p.pat_id = d.pat_id
                        WHERE ABS(datediff(sample_date,diag_date)) < 7
                        )
;

Answer 1

您混淆了表别名和列别名。列别名是在SELECT中定义的，不能在该SELECT的WHERE子句中使用。

表别名在FROM子句中定义。它可以--而且应该--在使用表中的列时使用。这种具有表别名的列名称为限定列。

例如，从子查询中：

SELECT  icv.icv_pat_id as pat_id,
        icv.icv_test_name as Test,
        'Trichomonas' as Required_diagnosis,
        lrr.lrr_request_date as Sample_date
        . . .
FROM investigation_component_values icv INNER JOIN
     lab_result_records lrr
     on lrr.lrr_rrc_id = icv.icv_rrc_id

WHERE lrr.lrr_request_date BETWEEN '2015-08-01' AND '2015-08-31' . . .

pat_id、test、Required_diagnosis和Sample_date都是列别名。icv和lrr是表别名。在WHERE子句中，您可以拥有：

WHERE lrr.lrr_request_date BETWEEN '2015-08-01' AND '2015-08-31' . . .

但你不能：

WHERE Sample_Date BETWEEN '2015-08-01' AND '2015-08-31' . . .

Answer 2

您可以使用真正的列名，因此在“WHERE pos.pat_id”(例如，在WHERE)中，您应该使用：‘WHER.icV_pat_id’。

Answer 3

通常，您可以在where子句中使用table别名；通常，我会：

Select * from Table T, Table2 T2 WHERE T.key = T2.key;

例如，尝试使用此语法显式内部联接。