sql - 排序规则冲突

Question

我总是收到消息“无法解决等于操作中“SQL_Latin1_General_CP1_CI_AS”和“Latin1_General_CI_AI”之间的排序规则冲突。”当我运行来自 MSSQL 2005 服务器的脚本时。顺便说一句，我使用的是 MSSQL 2008。

这是脚本

USE [database1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec rsp_SOA '', '', '', '', '', '', '40050000', '40050000', '05/01/2010', '05/31/2010', '000-00','Dont Show Note'

ALTER procedure [dbo].[rsp_SOA]
    @cCompName VARCHAR(100) ,
    @cAddress1 VARCHAR(200) ,
    @cAddress2 VARCHAR(200),
    @cReportTitle VARCHAR(200),
    @cCriteria1 VARCHAR(200),
    @cCriteria VARCHAR(200),

    @cFrom VARCHAR(25),
    @cTo VARCHAR(25),
    @dDateFrom VARCHAR(10),
    @dDateTo VARCHAR(10),
    @cCompID VARCHAR(10),
    @cFilter VARCHAR(30)
as

declare @cSQL VARCHAR(200),
        @cSQL1 VARCHAR(200),
        @cmd VARCHAR(8000),
        @cmd1 VARCHAR(8000),
        @cTempTable varchar (50),
        @cTempTable1 varchar (50),
        @nInterval VARCHAR(3),
        @nCurrent INTEGER, 
        @nInterval1 varchar(3), 
        @nInterval2 varchar(3), 
        @nInterval3 varchar(3), 
        @nInterval4 varchar(3), 
        @nInterval5 varchar(3), 
        @dd INTEGER,
        @cValue1 VARCHAR(100),
        @cValue2 VARCHAR(100),
        @cValue3 VARCHAR(100),
        @cValue4 VARCHAR(100),
        @cValue5 VARCHAR(100)

set @nInterval = 30
set @nInterval1 = cast(@nInterval * 1 as varchar(3))
set @nInterval2 = cast(@nInterval * 2 as varchar(3))
set @nInterval3 = cast(@nInterval * 3 as varchar(3))
set @nInterval4 = cast(@nInterval * 4 as varchar(3))
set @nInterval5 = cast(@nInterval * 5  as varchar(3))

SET @cValue1 = CONVERT(VARCHAR(10),1,110) + ' - ' + CONVERT(VARCHAR(10),@nInterval1,110) + ' Days'
SET @cValue2 = CONVERT(VARCHAR(10),@nInterval1 + 1,110) + ' - ' + CONVERT(VARCHAR(10),@nInterval2,110) + ' Days'
SET @cValue3 = CONVERT(VARCHAR(10),@nInterval2 + 1,110) + ' - ' + CONVERT(VARCHAR(10),@nInterval3,110) + ' Days'
SET @cValue4 = CONVERT(VARCHAR(10),@nInterval3 + 1,110) + ' - ' + CONVERT(VARCHAR(10),@nInterval4,110) + ' Days'
SET @cValue5 = 'Above ' + CONVERT(VARCHAR(10),@nInterval4,110) + ' Days'

create table ##Interval
(
    cCompID varchar(20),
    nOrder int,
    cInterval varchar(20),
    cCode varchar(20)
)


---------------------------
--get all the clients
---------------------------
SELECT * INTO ##temp1 FROM 
(
    select cCode from client_customer where cCode = @cFrom union all
    select cGroupCode from client_customer where cCode = @cFrom union all
    select cBillingCompany from client_customer where cCode = @cFrom union all
    select cArea from client_customer where cCode = @cFrom
)a

---------------------------
--determining the balance of the invoices
---------------------------
SELECT * INTO ##temp2 FROM 
(
    (
        select a.cCompanyID, a.cInvNo, sum(a.nGross) as nSales, 0 as nPaid, 0 as nDebit, 0 as nCredit, 0 as nReturns 
        from sales a 
        where a.lCancelled = 0 and a.cPayType<>'Cash' 
            and a.cCode in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, a.cInvNo
    )
    union all
    (
        select a.cCompanyID, a.cInvNo, 0 as nSales, sum(a.nApplied) as nPaid, 0 as nDebit, 0 as nCredit, 0 as nReturns
        from pr_t a 
        left outer join pr b on a.cTranNo = b.cTranNo and a.cCompanyID = b.cCompanyID
        left outer join sales c on a.cInvNo = c.cInvNo and a.cCompanyID = c.cCompanyID
        where b.lCancelled = 0 
            and c.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, a.cInvNo
    )
    union all
    (
        select a.cCompanyID, a.cInvNo, 0 as nSales, 0 as nPaid, sum(a.nDebit) as nDebit, 0 as nCredit, 0 as nReturns
        from ar_t a 
        left outer join ar b on a.cTranNo = b.cTranNo and a.cCompanyID = b.cCompanyID
        where b.cType = 'Debit' and b.lCancelled = 0 and b.lApproved = 1
            and b.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, a.cInvNo
    )
    union all
    (
        select a.cCompanyID, a.cInvNo, 0 as nSales, 0 as nPaid, 0 as nDebit, sum(a.nCredit) as nCredit, 0 as nReturns
        from ar_t a 
        left outer join ar b on a.cTranNo = b.cTranNo and a.cCompanyID = b.cCompanyID
        where b.cType = 'Credit' and b.lSalesReturn = 0 and b.lCancelled = 0 and b.lApproved = 1
            and b.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, a.cInvNo
    )
    union all
    (
        select a.cCompanyID, a.cInvNo, 
        0 as nSales, 0 as nPaid, 0 as nDebit, 0 as nCredit, sum(a.nCredit) as nReturns
        from ar_t a 
        left outer join ar b on a.cTranNo = b.cTranNo and a.cCompanyID = b.cCompanyID
        where b.cType = 'Credit' and b.lSalesReturn = 1 and b.lCancelled = 0 and b.lApproved = 1 
            and b.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, a.cInvNo
    )
)a


-----------------------
--main script for creating the temp ##AR
-----------------------
select a.*, b.cRefNo, b.dRefDate, b.nBeg, b.nEnd, b.nConsumed, b.nConsumedKG, b.nPrice, b.nAmount, b.nVat, b.nWHT, b.nNet, 
            case when a.nDue <=0 then 'Current'
                 when a.nDue > 0 and a.nDue <= @nInterval1 then @cValue1
                 when a.nDue > @nInterval1 and a.nDue <= @nInterval2 then @cValue2
                 when a.nDue > @nInterval2 and a.nDue <= @nInterval3 then @cValue3
                 when a.nDue > @nInterval3 and a.nDue <= @nInterval4 then @cValue4
                 when a.nDue > @nInterval4 then @cValue5
            end as cInterval,b.cTenantName into ##AR
    from
    (
        select a.cCompanyID, b.cCode, a.cInvNo, (isnull(sum(a.nSales), 0) - isnull(sum(a.nPaid), 0) + isnull(sum(a.nDebit), 0) - isnull(sum(a.nCredit), 0) - isnull(sum(a.nReturns), 0)) as nBalance,
                b.dDate, b.cSMan, b.nGross, b.cTerm, c.cValue, (cast((cast(convert(varchar(20),getdate(),101) as datetime) - b.dDate) as integer) - cast(c.cValue as integer)) as nDue, dateadd(d,cast(c.cValue as integer),b.dDate) as dDueDate
        from 
        (
            select * FROM ##temp2 --determining the balance of the invoices
        ) a 
        left outer join sales b on a.cInvNo = b.cInvNo and  a.cCompanyID = b.cCompanyID
        left outer join parameter_user c on b.cTerm = c.cParamName and c.cType = 'TERMS'
        where b.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
        group by a.cCompanyID, b.cCode, a.cInvNo, b.dDate, b.cSMan, b.nGross, b.cTerm, c.cValue
        having (isnull(sum(a.nSales), 0) - isnull(sum(a.nPaid), 0) + isnull(sum(a.nDebit), 0) - isnull(sum(a.nCredit), 0) - isnull(sum(a.nReturns), 0)) > 0
    )a
    left outer join 
    (
        select a.cCompanyID, a.cInvNo, b.cRefNo, d.dDate as dRefDate, 
            case when a.cType = 'Meter' then b.nMeterIn
                 when a.cType = 'Weight' then b.nWeightOut
            else 0 end as nBeg,
            case when a.cType = 'Meter' then b.nMeterOut
                 when a.cType = 'Weight' then b.nWeightIn
            else 0 end as nEnd, b.nConsumed, 
            case when a.cType = 'Meter' then b.nConsumedKG
                 when a.cType = 'Weight' then (b.nWeightOut - b.nWeightIn)
            else 0 end as nConsumedKG, b.nPrice, (b.nAmount / 1.12) as nAmount,
                 (b.nAmount - (b.nAmount / 1.12)) as nVat,
                 (CASE WHEN e.lEWT=1 THEN ((b.nAmount / 1.12) * e.nEWT/100) ELSE 0 END ) as nWHT, b.nAmount as nNet,f.cTenantName


        from sales a left outer join sales_t b on a.cInvNo = b.cInvNo and a.cCompanyID = b.cCompanyID
            left outer join item c on b.cItemNo = c.cItemNo and a.cCompanyID = c.cCompanyID
            left outer join dr d on d.cDRNo = b.cRefNo  
            left outer JOIN CLIENT_CUSTOMER e ON a.cCode = e.cCode
            left outer JOIN METER_READING_T f ON b.cMRNo = f.cTransNo AND b.nMeterIn = f.nMeterIn
        where c.cType <> 'CYLINDER' and a.cType <> 'Invoice' and a.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID

        union all

        select a.cCompanyID, a.cInvNo, d.cDRNo as cRefNo, d.dDate as dRefDate, 0 as nBeg,
            0 as nEnd, b.nConsumed, 0 as nConsumedKG, b.nPrice, b.nAmount, 0 as nVat, 0 as nWHT, 0 as nNet,'' as cTenantName
        from sales a left outer join sales_t b on a.cInvNo = b.cInvNo and a.cCompanyID = b.cCompanyID
            left outer join item c on b.cItemNo = c.cItemNo and a.cCompanyID = c.cCompanyID
            left outer join dr d on a.cInvNo = d.cInvNo         
        where c.cType <> 'CYLINDER' and a.cType = 'Invoice' and d.cDRNo is null and a.cCode  in (select * FROM ##temp1) --get all the clients
            and a.cCompanyID = @cCompID
    )b on a.cInvNo = b.cInvNo and a.cCompanyID = b.cCompanyID


-----------------------
--main script for creating the temp ##Interval
-----------------------
insert into ##Interval
select distinct @cCompID, 1, 'Above 120 Days', cCode from ##AR
insert into ##Interval
select distinct @cCompID, 2, '91 - 120 Days', cCode from ##AR
insert into ##Interval
select distinct @cCompID, 3, '61 - 90 Days', cCode from ##AR
insert into ##Interval
select distinct @cCompID, 4, '31 - 60 Days', cCode from ##AR
insert into ##Interval
select distinct @cCompID, 5, '1 - 30 Days', cCode from ##AR
insert into ##Interval
select distinct @cCompID, 6, 'Current', cCode from ##AR




--------------------------------------
--displaying the result
--------------------------------------

select a.nOrder, a.cInterval, a.cCode, c.cName, (c.cFirstName + ' ' + case when isnull(c.cMiddleInitial,'')='' then '' else c.cMiddleInitial + ' ' end + c.cLastName) as cCustomerName, (case when isnull(c.cBusinessName,'')='' then c.cName else c.cBusinessName end) as cBusinessName,
        c.cAddress, c.cLastName, c.cJobTitle, d.cCompanyName, d.cAddress1, d.cAddress2, d.cPhone, d.cfax, d.cEmail, c.cTerm, b.cInvNo, b.nBalance, b.dDate, b.nGross, b.cRefNo, b.dRefDate,  b.nBeg, b.nEnd, b.nConsumed, b.nCOnsumedKG, 
        b.nPrice, b.nAmount, b.nVat, b.nWHT, b.nNet, isnull(e.nTotalPDC,0) as nTotalPDC, f.nTotalAR, f.nTotalPastDue, c.nLimit, @dDateFrom as dStartDate, @dDateTo as dEndDate,--g.cTenantName,
        b.cTenantName,(CASE WHEN @cFilter = 'Show Note' THEN 1 ELSE 0 END) AS lNote
from ##Interval a
left outer join ##AR b on a.cInterval = b.cInterval and a.cCode = b.cCode
left outer join client_customer c on a.cCode = c.cCode
left outer join company d on a.cCompID = d.cCompanyID
left outer join
(
    select a.cCode, sum(a.nAmount) as nTotalPDC 
    from checks a
    where a.lDeposited=0 and a.cTransType = 'COL' and a.cCode = @cFrom and a.cCompanyID = @cCompID
    group by a.cCode
)e on a.cCode=e.cCode
left outer join
(
    --select a.cCode, sum(a.nBalance) as nTotalAR, sum(case when a.cInterval <> 'Current' then isnull(a.nBalance,0) else 0 end) as nTotalPastDue
    --from ##AR a
    --group by a.cCode

    SELECT a.cCode,SUM(nTotalAR) AS nTotalAR,SUM(nTotalPastDue) AS nTotalPastDue
    FROM 
        (select distinct a.cCode, (a.nBalance) as nTotalAR, 
            (case when a.cInterval <> 'Current' then isnull(a.nBalance,0) else 0 end) as nTotalPastDue
        from ##AR a) a
    GROUP BY a.cCode

)f on a.cCode=f.cCode
order by a.nOrder, a.cCode


 drop table ##temp1
 drop table ##temp2
 drop table ##Interval
 drop table ##AR


/*
  select * from ##temp1
  select * from ##temp2
  select * from ##Interval
  select * from ##AR
*/

sql server 总是将错误指向这一行

--------------------------------------
--displaying the result
--------------------------------------

select a.nOrder, a.cInterval, a.cCode, c.cName, (c.cFirstName + ' ' + case when isnull(c.cMiddleInitial,'')='' then '' else c.cMiddleInitial + ' ' end + c.cLastName) as cCustomerName, (case when isnull(c.cBusinessName,'')='' then c.cName else c.cBusinessName end) as cBusinessName,
        c.cAddress, c.cLastName, c.cJobTitle, d.cCompanyName, d.cAddress1, d.cAddress2, d.cPhone, d.cfax, d.cEmail, c.cTerm, b.cInvNo, b.nBalance, b.dDate, b.nGross, b.cRefNo, b.dRefDate,  b.nBeg, b.nEnd, b.nConsumed, b.nCOnsumedKG, 
        b.nPrice, b.nAmount, b.nVat, b.nWHT, b.nNet, isnull(e.nTotalPDC,0) as nTotalPDC, f.nTotalAR, f.nTotalPastDue, c.nLimit, @dDateFrom as dStartDate, @dDateTo as dEndDate,--g.cTenantName,
        b.cTenantName,(CASE WHEN @cFilter = 'Show Note' THEN 1 ELSE 0 END) AS lNote

Answer 1

当您比较/聚合具有不同排序规则的列时，您必须使用 COLLATE 将其中至少一个排序规则与您想要的排序规则保持一致。表达。例如，假设您有一个列 c1有排序规则Latin1_General_CI_AI在表中Table1和一列c2有排序规则SQL_Latin1_General_CP1_CI_AS在表中Table2并且您想将两者结合起来:

SELECT c1 COLLATE SQL_Latin1_General_CP1_CI_AS
FROM Table1
UNION ALL
SELECT c2 
FROM Table2;