以下SQL查询有什么问题

本文介绍了以下SQL查询有什么问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 SELECT * FROM ratecard ratecard INNER JOIN RateCardStagingTable UPDATEDATA ON ratecard.ratetableid = @ RateTableID WHERE （ratecard.ratecategoryid1 = UPDATEDATA.ratecategoryid1 OR （ratecard.ratecategoryid1 IS NULL AND UPDATEDATA.ratecategoryid1 IS NULL ）） AND （ratecard.ratecategoryid2 = UPDATEDATA.ratecategoryid2 OR （ratecard.ratecategoryid2 IS NULL AND UPDATEDATA.ratecategoryid2 IS NULL ）） AND （ratecard.ratecategoryid3 = UPDATEDATA.ratecategoryid3 OR （ratecard.ratecategoryid3 IS NULL AND UPDATEDATA.ratecategoryid3 IS NULL ）） AND （ratecard.ratecategoryid4 = UPDATEDATA .ratecategoryid4 OR （ratecard.ratecategoryid4 IS NULL AND UPDATEDATA.ratecategoryid4 IS NULL ） ） AND （ratecard.ratecategoryid5 = UPDATEDATA.ratecategoryid5 OR （ratecard.ratecategoryid5 IS NULL AND UPDATEDATA.ratecategoryid5 IS NULL ）） AND （ratecard.ratecategoryid6 = UPDATEDATA.ratecategoryid6 OR （ ratecard.ratecategoryid6 IS NULL AND UPDATEDATA。 ratecategoryid6 IS NULL ）） AND （ratecard.ratecategoryid7 = UPDATEDATA.ratecategoryid7 OR （ratecard.ratecategoryid7 IS NULL AND UPDATEDATA.ratecategoryid7 IS NULL ）） AND （ratecard.ratecategoryid8 = UPDATEDATA.ratecategoryid8 OR （ratecard.ratecategoryid8 IS NULL AND UPDATEDATA.ratecategoryid8 IS NULL ）） AND （ratecard.ratecategoryid9 = UPDATEDATA .ratecategoryid9 OR （ratecard.ratecategoryid9 IS NULL AND UPDATEDATA.ratecategoryid9 IS NULL ） ） AND （ratecard.ratecategoryid10 = UPDATEDATA.ratecategoryid10 OR （ratecard.ratecategoryid10 IS NULL AND UPDATEDATA.ratecategoryid10 IS NULL ）） GROUP BY UPDATEDATA.ID，UPDATEDATA.RateCategoryID1，UPDATEDATA .RateCategoryID2，UPDATEDATA.RateCategoryID3，UPDATEDATA.RateCategoryID4，UPDATEDATA.RateCategoryID5，UPDATEDATA.RateCategoryID6，UPDATEDATA.RateCategoryID7，UPDATEDATA.RateCategoryID8，UPDATEDATA.RateCategoryID9，UPDATEDATA.RateCategoryID10，UPDATEDATA.low，UPDATEDATA。 target ，UPDATEDATA.high，UPDATEDATA.CustomFields，UPDATEDATA.DataPointsCount SELECT * FROM （RateCardStagingTable DI LEFT JOIN （ SELECT UPDATEDATA .ID，UPDATEDATA.RateCategoryID1，UPDATEDATA.RateCategoryID2，UPDATEDATA.RateCategoryID3，UPDATEDATA.RateCategoryID4，UPDATEDATA.RateCategoryID5，UPDATEDATA.RateCategoryID6，UPDATEDATA.RateCategoryID7，UPDATEDATA.RateCategoryID8，UPDATEDATA.RateCategoryID9，UPDATEDATA.RateCategoryID10，UPDATEDATA.low，UPDATEDATA。 target ，UPDATEDATA.high，UPDATEDATA.CustomFields，UPDATEDATA.DataPointsCount FROM ratecard ratecard INNER JOIN RateCardStagingTable UPDATEDATA ON ratecard.ratetableid = @ RateTableID WHERE （ratecard.ratecategoryid1 = UPDATEDATA.ratecategoryid1 OR （ratecard.ratecategory id1 IS NULL AND UPDATEDATA.ratecategoryid1 IS NULL ）） AND （ratecard.ratecategoryid2 = UPDATEDATA.ratecategoryid2 OR （ratecard.ratecategoryid2 IS NULL AND UPDATEDATA.ratecategoryid2 IS NULL ）） AND （ratecard.ratecategoryid3 = UPDATEDATA.ratecategoryid3 OR （ ratecard.ratecategoryid3 IS NULL AND UPDATEDATA。 ratecategoryid3 IS NULL ）） AND （ratecard.ratecategoryid4 = UPDATEDATA.ratecategoryid4 OR （ratecard.ratecategoryid4 IS NULL AND UPDATEDATA.ratecategoryid4 IS NULL ）） AND （ratecard.ratecategoryid5 = UPDATEDATA.ratecategoryid5 OR （ratecard.ratecategoryid5 IS NULL AND UPDATEDATA.ratecategoryid5 IS NULL ）） AND （ratecard.ratecategoryid6 = UPDATEDATA.ratecategoryid6 OR （ratecard.ratecategory id6 IS NULL AND UPDATEDATA.ratecategoryid6 IS NULL ）） AND （ratecard.ratecategoryid7 = UPDATEDATA.ratecategoryid7 OR （ratecard.ratecategoryid7 IS NULL AND UPDATEDATA.ratecategoryid7 IS NULL ）） AND （ratecard.ratecategoryid8 = UPDATEDATA.ratecategoryid8 OR （ ratecard.ratecategoryid8 IS NULL AND UPDATEDATA。 ratecategoryid8 IS NULL ）） AND （ratecard.ratecategoryid9 = UPDATEDATA.ratecategoryid9 OR （ratecard.ratecategoryid9 IS NULL AND UPDATEDATA.ratecategoryid9 IS NULL ）） AND （ratecard.ratecategoryid10 = UPDATEDATA.ratecategoryid10 OR （ratecard.ratecategoryid10 IS NULL AND UPDATEDATA.ratecategoryid10 IS NULL ）） GROUP BY UPDATEDATA.ID， UPDATEDATA.RateCategoryID1，UPDATEDATA.RateCategoryID2，UPDATEDATA.RateC ategoryID3，UPDATEDATA.RateCategoryID4，UPDATEDATA.RateCategoryID5，UPDATEDATA.RateCategoryID6，UPDATEDATA.RateCategoryID7，UPDATEDATA.RateCategoryID8，UPDATEDATA.RateCategoryID9，UPDATEDATA.RateCategoryID10，UPDATEDATA.low，UPDATEDATA。 target ，UPDATEDATA.high，UPDATEDATA.CustomFields，UPDATEDATA.DataPointsCount）UD ON DI.id = UD.id） - WHERE UD.id IS NULL SET @ UpdatedRecords =案例 WHEN @ UpdatedRecords IS NULL 那么 0 ELSE @ UpdatedRecords END SET @ InsertedCount = 案例 WHEN @ InsertedCount IS NULL 那么 0 ELSE @ InsertedCount END SET @ UpdateRecordsnumber = @ UpdatedRecords SET @ InsertedRecordsNumber = @ InsertedCount 我得到这个错误我可以;弄清楚问题 以下是错误 消息305，级别16，状态1，过程GetImportRateTableDetails，行233 除非使用IS NULL运算符，否则无法比较或排序XML数据类型。 消息305，级别16，状态1，过程GetImportRateTableDetails，行251 除非使用IS NULL运算符，否则无法比较或排序XML数据类型。 我尝试了什么： 我在这个查询中做错了什么解决方案 我在group by子句中添加xml数据字段遇到了这样的问题。 当然我不推荐使用xml group by子句中的字段。 我克服的方法是将xml字段转换为varchar，让sql进行排序。 通过将GroupBy字段中的xml列转换为以下CONVERT（ VARCHAR（最大），xml_data_field_name）。 SELECT * FROM ratecard ratecard INNER JOIN RateCardStagingTable UPDATEDATA ON ratecard.ratetableid = @RateTableIDWHERE ( ratecard.ratecategoryid1 = UPDATEDATA.ratecategoryid1 OR ( ratecard.ratecategoryid1 IS NULL AND UPDATEDATA.ratecategoryid1 IS NULL ) ) AND ( ratecard.ratecategoryid2 = UPDATEDATA.ratecategoryid2 OR ( ratecard.ratecategoryid2 IS NULL AND UPDATEDATA.ratecategoryid2 IS NULL ) ) AND ( ratecard.ratecategoryid3 = UPDATEDATA.ratecategoryid3 OR ( ratecard.ratecategoryid3 IS NULL AND UPDATEDATA.ratecategoryid3 IS NULL ) ) AND ( ratecard.ratecategoryid4 = UPDATEDATA.ratecategoryid4 OR ( ratecard.ratecategoryid4 IS NULL AND UPDATEDATA.ratecategoryid4 IS NULL ) ) AND ( ratecard.ratecategoryid5 = UPDATEDATA.ratecategoryid5 OR ( ratecard.ratecategoryid5 IS NULL AND UPDATEDATA.ratecategoryid5 IS NULL ) ) AND ( ratecard.ratecategoryid6 = UPDATEDATA.ratecategoryid6 OR ( ratecard.ratecategoryid6 IS NULL AND UPDATEDATA.ratecategoryid6 IS NULL ) ) AND ( ratecard.ratecategoryid7 = UPDATEDATA.ratecategoryid7 OR ( ratecard.ratecategoryid7 IS NULL AND UPDATEDATA.ratecategoryid7 IS NULL ) ) AND ( ratecard.ratecategoryid8 = UPDATEDATA.ratecategoryid8 OR ( ratecard.ratecategoryid8 IS NULL AND UPDATEDATA.ratecategoryid8 IS NULL ) ) AND ( ratecard.ratecategoryid9 = UPDATEDATA.ratecategoryid9 OR ( ratecard.ratecategoryid9 IS NULL AND UPDATEDATA.ratecategoryid9 IS NULL ) ) AND ( ratecard.ratecategoryid10 = UPDATEDATA.ratecategoryid10 OR ( ratecard.ratecategoryid10 IS NULL AND UPDATEDATA.ratecategoryid10 IS NULL ) )GROUP BY UPDATEDATA.ID , UPDATEDATA.RateCategoryID1,UPDATEDATA.RateCategoryID2,UPDATEDATA.RateCategoryID3,UPDATEDATA.RateCategoryID4,UPDATEDATA.RateCategoryID5,UPDATEDATA.RateCategoryID6,UPDATEDATA.RateCategoryID7,UPDATEDATA.RateCategoryID8,UPDATEDATA.RateCategoryID9,UPDATEDATA.RateCategoryID10,UPDATEDATA.low,UPDATEDATA.target,UPDATEDATA.high,UPDATEDATA.CustomFields,UPDATEDATA.DataPointsCountSELECT * FROM( RateCardStagingTable DI LEFT JOIN(SELECT UPDATEDATA.ID , UPDATEDATA.RateCategoryID1,UPDATEDATA.RateCategoryID2,UPDATEDATA.RateCategoryID3,UPDATEDATA.RateCategoryID4,UPDATEDATA.RateCategoryID5,UPDATEDATA.RateCategoryID6,UPDATEDATA.RateCategoryID7,UPDATEDATA.RateCategoryID8,UPDATEDATA.RateCategoryID9,UPDATEDATA.RateCategoryID10,UPDATEDATA.low,UPDATEDATA.target,UPDATEDATA.high,UPDATEDATA.CustomFields,UPDATEDATA.DataPointsCountFROM ratecard ratecard INNER JOIN RateCardStagingTable UPDATEDATA ON ratecard.ratetableid = @RateTableIDWHERE ( ratecard.ratecategoryid1 = UPDATEDATA.ratecategoryid1 OR ( ratecard.ratecategoryid1 IS NULL AND UPDATEDATA.ratecategoryid1 IS NULL ) ) AND ( ratecard.ratecategoryid2 = UPDATEDATA.ratecategoryid2 OR ( ratecard.ratecategoryid2 IS NULL AND UPDATEDATA.ratecategoryid2 IS NULL ) ) AND ( ratecard.ratecategoryid3 = UPDATEDATA.ratecategoryid3 OR ( ratecard.ratecategoryid3 IS NULL AND UPDATEDATA.ratecategoryid3 IS NULL ) ) AND ( ratecard.ratecategoryid4 = UPDATEDATA.ratecategoryid4 OR ( ratecard.ratecategoryid4 IS NULL AND UPDATEDATA.ratecategoryid4 IS NULL ) ) AND ( ratecard.ratecategoryid5 = UPDATEDATA.ratecategoryid5 OR ( ratecard.ratecategoryid5 IS NULL AND UPDATEDATA.ratecategoryid5 IS NULL ) ) AND ( ratecard.ratecategoryid6 = UPDATEDATA.ratecategoryid6 OR ( ratecard.ratecategoryid6 IS NULL AND UPDATEDATA.ratecategoryid6 IS NULL ) ) AND ( ratecard.ratecategoryid7 = UPDATEDATA.ratecategoryid7 OR ( ratecard.ratecategoryid7 IS NULL AND UPDATEDATA.ratecategoryid7 IS NULL ) ) AND ( ratecard.ratecategoryid8 = UPDATEDATA.ratecategoryid8 OR ( ratecard.ratecategoryid8 IS NULL AND UPDATEDATA.ratecategoryid8 IS NULL ) ) AND ( ratecard.ratecategoryid9 = UPDATEDATA.ratecategoryid9 OR ( ratecard.ratecategoryid9 IS NULL AND UPDATEDATA.ratecategoryid9 IS NULL ) ) AND ( ratecard.ratecategoryid10 = UPDATEDATA.ratecategoryid10 OR ( ratecard.ratecategoryid10 IS NULL AND UPDATEDATA.ratecategoryid10 IS NULL ) )GROUP BY UPDATEDATA.ID , UPDATEDATA.RateCategoryID1,UPDATEDATA.RateCategoryID2,UPDATEDATA.RateCategoryID3,UPDATEDATA.RateCategoryID4,UPDATEDATA.RateCategoryID5,UPDATEDATA.RateCategoryID6,UPDATEDATA.RateCategoryID7,UPDATEDATA.RateCategoryID8,UPDATEDATA.RateCategoryID9,UPDATEDATA.RateCategoryID10,UPDATEDATA.low,UPDATEDATA.target,UPDATEDATA.high,UPDATEDATA.CustomFields,UPDATEDATA.DataPointsCount )UD ON DI.id=UD.id)-- WHERE UD.id IS NULLSET @UpdatedRecords=Case WHEN @UpdatedRecords IS NULL THEN 0 ELSE @UpdatedRecords ENDSET @InsertedCount= Case WHEN @InsertedCount IS NULL THEN 0 ELSE @InsertedCount ENDSET @UpdateRecordsnumber =@UpdatedRecordsSET @InsertedRecordsNumber =@InsertedCountI am getting this error i can;t figure out the problem below is the error Msg 305, Level 16, State 1, Procedure GetImportRateTableDetails, Line 233The XML data type cannot be compared or sorted, except when using the IS NULL operator.Msg 305, Level 16, State 1, Procedure GetImportRateTableDetails, Line 251The XML data type cannot be compared or sorted, except when using the IS NULL operator.What I have tried:What is wrong i am doing in this query 解决方案 I have come across such problem in adding xml data fields in group by clause.Of course I don't recommend using xml fields in group by clause. The way I overcome is by converting the xml field to varchar that way letting sql to sort.Try following by converting the xml column in the GroupBy field as following CONVERT(VARCHAR(max),xml_data_field_name). 这篇关于以下SQL查询有什么问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！