ntext数据类型不能被选为DISTINCT,因为它不具有可比性

我有一个查询,它由多个查询和多个表连接,如果我运行此查询我收到如下错误:

除非使用IS NULL或LIKE运算符,否则无法比较或排序text,ntext和image数据类型.

ntext数据类型不能被选为DISTINCT,因为它不具有可比性.

表结构是: 表和数据类型的设计

并且查询是这样的:

SELECT p.Proj_uid, p.proj_name,p.Agency,p.District,p.Division,p.ProjStatus,Civilbill80.billcount as Civilbill80, 
Civilbill20.billcount as Civilbill20 ,Civilbillpay.billcount as FinalCivilBill,CivilWorkslip.billcount as CivilWorkslip,
 Electribill80.billcount as Electricbill80, Electribill20.billcount as Electricbill20, Electribillpay.billcount as FinalElectriBill,
 ElectriWorkslip.billcount as ElectriWorkslip  FROM tempproj p
  LEFT JOIN (
             SELECT distinct(Proj_name),BillType,COUNT(1) as billcount  FROM payment_80   where BillType='CIVIL'
              GROUP BY Proj_name, BillType ) Civilbill80 ON Civilbill80.Proj_name = p.proj_name 
 LEFT JOIN (
             SELECT distinct(Proj_name),billtype,COUNT(1) as billcount FROM Payment_20    where billtype='CIVIL'
              GROUP BY Proj_name, billtype ) Civilbill20 ON Civilbill20.Proj_name = p.proj_name 
 LEFT JOIN (
             SELECT distinct(Proj_name),BillType, COUNT(1) as billcount  FROM payment_80  where BillType='Electric'
              GROUP BY Proj_name, BillType  ) Electribill80 ON Electribill80.Proj_name = p.proj_name 
 LEFT JOIN (
             SELECT distinct(Proj_name),billtype, COUNT(1) as billcount  FROM Payment_20  where billtype='Electric'
              GROUP BY Proj_name, billtype ) Electribill20 ON Electribill20.Proj_name = p.proj_name 
 LEFT JOIN (
             SELECT distinct(Proj_name),billtype, COUNT(1) as billcount   FROM Payment  where billtype='CIVIL'
              GROUP BY Proj_name, billtype ) Civilbillpay ON Civilbillpay.Proj_name = p.proj_name 
 LEFT JOIN (
             SELECT distinct(Proj_name),billtype, COUNT(1) as billcount  FROM Payment  where billtype='CIVIL'
              GROUP BY Proj_name, billtype  ) Electribillpay ON Electribillpay.Proj_name = p.proj_name
LEFT JOIN (
             SELECT distinct(proj_uid),item_type, COUNT(1) as billcount  FROM WorkSlipAmounts where item_type='WorkSlip'
              GROUP BY proj_uid, item_type ) CivilWorkslip ON CivilWorkslip.proj_uid = p.proj_uid
 LEFT JOIN (
             SELECT distinct(proj_uid),item_type, COUNT(1) as billcount  FROM WorkSlipAmounts  where item_type='ElecWorkSlip'
              GROUP BY proj_uid, item_type) ElectriWorkslip ON ElectriWorkslip.proj_uid = p.proj_uid

在此输入图像描述

请帮帮我.非常感谢

SQL Server的ntext,text和image数据类型已过时:

将来的Microsoft SQL Server版本中将删除ntext,text和image数据类型.避免在新的开发工作中使用这些数据类型,并计划修改当前使用它们的应用程序.请改用nvarchar(max),varchar(max)和varbinary(max).

可能的方法:

标识使用这些数据类型的列

2A.(正确的方法)更改表并将数据类型更改为NVARCHAR/VARBINARY

2B.(解决方法)或在SELECT DISTINCT使用中:CAST(col_name AS NVARCHAR(MAX)),对于连接条件相同CAST(p.proj_name AS NVARCHAR(MAX)) = CAST(Civilbill20.proj_name AS NVARCHAR(MAX))