"select mat.material_no,mat.material_name_eng,mat.spec1,mat.spec2,
mat.spec3,mat.spec4,mat.spec5,mat.spec6,mat.spec7, mat.spec8,mat.latest_update
from material mat
inner join inv_bal inv on mat.material_no=inv.material_no
where mat.material_no like '1%' and mat.latest_update > '2010-01-01'
and mat.user_in_charge <> '780' and mat.user_in_charge <>'239' "
如果用DISTINCT刪除material_no的重複項?
mat.spec3,mat.spec4,mat.spec5,mat.spec6,mat.spec7, mat.spec8,mat.latest_update
from material mat
inner join inv_bal inv on mat.material_no=inv.material_no
where mat.material_no like '1%' and mat.latest_update > '2010-01-01'
and mat.user_in_charge <> '780' and mat.user_in_charge <>'239' "
如果用DISTINCT刪除material_no的重複項?
mat.spec3,mat.spec4,mat.spec5,mat.spec6,mat.spec7, mat.spec8,mat.latest_update
from material mat
inner join inv_bal inv on mat.material_no=inv.material_no
where mat.material_no like '1%' and mat.latest_update > '2010-01-01'
and mat.user_in_charge <> '780' and mat.user_in_charge <>'239'
The text data type cannot be selected as DISTINCT because it is not comparable.
mat.spec3,mat.spec4,mat.spec5,mat.spec6,mat.spec7, mat.spec8,mat.latest_update
from material mat
inner join inv_bal inv on mat.material_no=inv.material_no
where mat.material_no like '1%' and mat.latest_update > '2010-01-01'
and mat.user_in_charge <> '780' and mat.user_in_charge <>'239'
and not exists(select 1 from material where material_no=mat.material_no and latest_update>mat.latest_update)
mat.spec3,mat.spec4,mat.spec5,mat.spec6,mat.spec7, mat.spec8,mat.latest_update
from material mat
left join inv_bal inv on mat.material_no=inv.material_no
where mat.material_no like '1%' and mat.latest_update > '2010-01-01'
and mat.user_in_charge <> '780' and mat.user_in_charge <>'239' 執行結果:Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
Sql2005以上版本转为cast(text列 as varchar(max))如:
DECLARE @t TABLE(Col TEXT)
INSERT @t SELECT 'aa' SELECT DISTINCT CAST(col AS VARCHAR(max)) FROM @t