在联合查询中排序错误 [英] sorting error in union query

问题描述

  SELECT DISTINCT rt。 d_rev_id，rt。 d_rev_code，rt。 d_reason，rt。 d_rev_status，rt。 d_apb，rt。 d_cb，pt。 d_partid，pt。 d_part_no，pt。 d_ab，pt。 d_abd，pt。 d_status，rt。 d_part_name，rt。 d_part_desc，rt。 d_part_type，pnv。 d_pn_val，pnv。 d_pn_id，cfv。 d_optionname，rt。 d_projectid，rt。 d_abd，rt。 d_apbd 
 FROM 
（
（
 design_parts pt 
 INNER JOIN design_part_number_val pnv 
 USING（d_partid）
 INNER JOIN design_revision_temp rt 
使用（d_partid）
）
 LEFT JOIN design_pn_custom_field_options cfv 
 ON d_optionvalue = d_pn_val 
）
 WHERE rt。 d_projectid = 1 
 AND 1 
 GROUP BY d_rev_id 
 UNION ALL 
 SELECT DISTINCT dr。 d_rev_id，博士。 d_rev_code，博士。 d_reason，博士。 d_part_name，dr。 d_apb，博士。 d_cb，pt。 d_partid，pt。 d_part_no，pt。 d_ab，pt。 d_abd，pt。 d_status，博士。 d_part_name，dr。 d_part_desc，博士。 d_part_type，pnv。 d_pn_val，pnv。 d_pn_id，cfv。 d_optionname，博士。 d_projectid，博士。 d_apbd，博士。 d_cbd 
 FROM 
（
（
 design_parts pt 
 INNER JOIN design_part_number_val pnv 
 USING（d_partid）
 INNER JOIN design_revision dr 
 USING（d_partid）
）
 LEFT JOIN design_pn_custom_field_options cfv 
 ON d_optionvalue = d_pn_val 
）
 WHERE dr。 d_projectid = 1 
 AND 1 
 GROUP BY d_partid 
 ORDER BY`rt.d_abd` ASC 
  

这个查询中有什么问题，因为我对所选的字段进行了排序。我收到错误 ＃1054 - '订单子句'中的未知列'rt.d_abd' 。
请帮助我

解决方案

试试这个查询：

<$ p_p> （SELECT DISTINCT rt。d_rev_id，rt。d_rev_code，rt。d_reason，rt。d_rev_status，rt。d_apb，rt。d_cb，pt。d_partid，pt。d_part_no，pt。d_ab，pt 。d_abd，pt。d_status，rt。d_part_name，rt。d_part_desc，rt。d_part_type，pnv。d_pn_val，pnv。d_pn_id，cfv。d_optionname，rt。d_projectid，rt。d_abd，rt。d_apbd
FROM
design_parts pt
INNER JOIN design_part_number_val pnv
USING（d_partid）
INNER JOIN design_revision_temp rt
USING（d_partid）
LEFT JOIN design_pn_custom_field_options cfv
ON d_optionvalue = d_pn_val
WHERE rt。d_projectid = 1
GROUP BY d_rev_id）
UNION ALL
（SELECT DISTINCT dr。d_rev_id，dr。d_rev_code，dr。d_reason，dr。d_part_name，dr。 d_apb，dr。d_cb，pt。d_partid，pt。d_part_no，pt。d_ab，pt。d_abd，pt。d_status， dr。d_part_name，dr。 d_part_desc，博士。 d_part_type，pnv。 d_pn_val，pnv。 d_pn_id，cfv。 d_optionname，博士。 d_projectid，博士。 d_apbd，博士。 d_cbd
FROM
design_parts pt
INNER JOIN design_part_number_val pnv
USING（d_partid）
INNER JOIN design_revision dr
USING（d_partid）
LEFT JOIN design_pn_custom_field_options cfv
ON d_optionvalue = d_pn_val
WHERE dr。 d_projectid = 1
GROUP BY d_partid）
ORDER BY`d_abd` ASC


SELECT DISTINCT rt . d_rev_id , rt . d_rev_code , rt . d_reason , rt . d_rev_status , rt . d_apb , rt . d_cb , pt . d_partid , pt . d_part_no , pt . d_ab , pt . d_abd , pt . d_status , rt . d_part_name , rt . d_part_desc , rt . d_part_type , pnv . d_pn_val , pnv . d_pn_id , cfv . d_optionname , rt . d_projectid , rt . d_abd , rt . d_apbd 
FROM 
    ( 
        ( 
        design_parts pt 
        INNER JOIN design_part_number_val pnv 
        USING ( d_partid ) 
        INNER JOIN design_revision_temp rt 
        USING ( d_partid ) 
        ) 
    LEFT JOIN design_pn_custom_field_options cfv 
    ON d_optionvalue = d_pn_val 
    )
WHERE rt . d_projectid = 1 
AND 1 
GROUP BY d_rev_id 
UNION ALL 
SELECT DISTINCT dr . d_rev_id , dr . d_rev_code , dr . d_reason , dr . d_part_name , dr . d_apb , dr . d_cb , pt . d_partid , pt . d_part_no , pt . d_ab , pt . d_abd , pt . d_status , dr . d_part_name , dr . d_part_desc , dr . d_part_type , pnv . d_pn_val , pnv . d_pn_id , cfv . d_optionname , dr . d_projectid , dr . d_apbd , dr . d_cbd 
FROM 
    ( 
        ( 
        design_parts pt 
        INNER JOIN design_part_number_val pnv 
        USING ( d_partid ) 
        INNER JOIN design_revision dr 
        USING ( d_partid ) 
        ) 
    LEFT JOIN design_pn_custom_field_options cfv 
    ON d_optionvalue = d_pn_val 
    ) 
WHERE dr . d_projectid = 1 
AND 1 
GROUP BY d_partid  
ORDER BY `rt.d_abd` ASC

What is wrong in this query as I ordering the field that are selected. I am getting an Error "#1054 - Unknown column 'rt.d_abd' in 'order clause' ". Please help me

解决方案

Try this query:

(SELECT DISTINCT rt . d_rev_id , rt . d_rev_code , rt . d_reason , rt . d_rev_status , rt . d_apb , rt . d_cb , pt . d_partid , pt . d_part_no , pt . d_ab , pt . d_abd , pt . d_status , rt . d_part_name , rt . d_part_desc , rt . d_part_type , pnv . d_pn_val , pnv . d_pn_id , cfv . d_optionname , rt . d_projectid , rt . d_abd , rt . d_apbd 
FROM 
    design_parts pt 
    INNER JOIN design_part_number_val pnv 
    USING ( d_partid ) 
    INNER JOIN design_revision_temp rt 
    USING ( d_partid ) 
    LEFT JOIN design_pn_custom_field_options cfv 
    ON d_optionvalue = d_pn_val 
WHERE rt . d_projectid = 1 
GROUP BY d_rev_id)
UNION ALL 
(SELECT DISTINCT dr . d_rev_id , dr . d_rev_code , dr . d_reason , dr . d_part_name , dr . d_apb , dr . d_cb , pt . d_partid , pt . d_part_no , pt . d_ab , pt . d_abd , pt . d_status , dr . d_part_name , dr . d_part_desc , dr . d_part_type , pnv . d_pn_val , pnv . d_pn_id , cfv . d_optionname , dr . d_projectid , dr . d_apbd , dr . d_cbd 
FROM 
    design_parts pt 
    INNER JOIN design_part_number_val pnv 
    USING ( d_partid ) 
    INNER JOIN design_revision dr 
    USING ( d_partid ) 
    LEFT JOIN design_pn_custom_field_options cfv 
    ON d_optionvalue = d_pn_val 
WHERE dr . d_projectid = 1 
GROUP BY d_partid)
ORDER BY `d_abd` ASC

这篇关于在联合查询中排序错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！