如何在sql中对查询结果进行排序(how to sort query result in sql)

如何在sql中对查询结果进行排序(how to sort query result in sql)

下面是我的查询，我想按描述排序此查询，即使用字段ld.descrip。 在下面的查询中使用order by来根据描述对数据进行排序。

Select ld.fact_code as costFactorID,ld.fact_code + ' ' + ld.descrip +' ' +'(' + convert(varchar,cast(ld.factor as money),1)+ '/' + ld.unit + ')' as costFactor, ap.alpha_code, ap.code, ld.neighborhd,ap.code from lndfact ld inner join app_lookup ap on ap.code like ld.neighborhd inner join parcel p on p.neighborhd = ap.alpha_code inner join assessments assmt on assmt.parcel_no = p.parcel_no where assmt.assesmt_no = @0 and ld.value_type = @1 union All Select ld.fact_code as costFactorID,ld.fact_code + ' ' + ld.descrip +' ' +'(' + convert(varchar,cast(ld.factor as money),1)+ '/' + ld.unit + ')' as costFactor, ap.alpha_code, ap.code, ld.neighborhd,ap.code from lndfact ld inner join app_lookup ap on ap.code like ld.neighborhd and ap.code = 77

Below is my query and i want to sort this query by description i.e with field ld.descrip. where to use order by in below query to sort data according to description.

Select ld.fact_code as costFactorID,ld.fact_code + ' ' + ld.descrip +' ' +'(' + convert(varchar,cast(ld.factor as money),1)+ '/' + ld.unit + ')' as costFactor, ap.alpha_code, ap.code, ld.neighborhd,ap.code from lndfact ld inner join app_lookup ap on ap.code like ld.neighborhd inner join parcel p on p.neighborhd = ap.alpha_code inner join assessments assmt on assmt.parcel_no = p.parcel_no where assmt.assesmt_no = @0 and ld.value_type = @1 union All Select ld.fact_code as costFactorID,ld.fact_code + ' ' + ld.descrip +' ' +'(' + convert(varchar,cast(ld.factor as money),1)+ '/' + ld.unit + ')' as costFactor, ap.alpha_code, ap.code, ld.neighborhd,ap.code from lndfact ld inner join app_lookup ap on ap.code like ld.neighborhd and ap.code = 77

最满意答案

仅在firts查询中的联合查询别名的末尾排序，（相同类型的对应类型列）

Select ld.fact_code as costFactorID ,ld.fact_code + ' ' + ld.descrip +' ' +'(' + convert(varchar,cast(ld.factor as money),1)+ '/' + ld.unit + ')' as costFactor , ap.alpha_code , ap.code , ld.neighborhd ,ap.code from lndfact ld inner join app_lookup ap on ap.code like ld.neighborhd inner join parcel p on p.neighborhd = ap.alpha_code inner join assessments assmt on assmt.parcel_no = p.parcel_no where assmt.assesmt_no = @0 and ld.value_type = @1 union All Select ld.fact_code ,ld.fact_code + ' ' + ld.descrip +' ' +'(' + convert(varchar,cast(ld.factor as money),1)+ '/' + ld.unit + ')' , ap.alpha_code , ap.code , ld.neighborhd ,ap.code from lndfact ld inner join app_lookup ap on ap.code like ld.neighborhd and ap.code = 77 ORDER BY alpha_code, code /* eg: */

Order by at the end of the united query alias in firts query only , (same number of corresponing type column)

Select ld.fact_code as costFactorID ,ld.fact_code + ' ' + ld.descrip +' ' +'(' + convert(varchar,cast(ld.factor as money),1)+ '/' + ld.unit + ')' as costFactor , ap.alpha_code , ap.code , ld.neighborhd ,ap.code from lndfact ld inner join app_lookup ap on ap.code like ld.neighborhd inner join parcel p on p.neighborhd = ap.alpha_code inner join assessments assmt on assmt.parcel_no = p.parcel_no where assmt.assesmt_no = @0 and ld.value_type = @1 union All Select ld.fact_code ,ld.fact_code + ' ' + ld.descrip +' ' +'(' + convert(varchar,cast(ld.factor as money),1)+ '/' + ld.unit + ')' , ap.alpha_code , ap.code , ld.neighborhd ,ap.code from lndfact ld inner join app_lookup ap on ap.code like ld.neighborhd and ap.code = 77 ORDER BY alpha_code, code /* eg: */