sql - ntext 数据类型不能选择为 DISTINCT，因为它不具有可比性

Question

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

1. The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
2. The ntext data type cannot be selected as DISTINCT because it is not comparable.

表结构为:

查询是这样的:

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

请帮帮我。非常感谢您

Answer 1

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

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

可能的解决方案:

1. 识别使用这些数据类型的列

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))

的连接条件相同