日期:2014-05-18 浏览次数:20935 次
select DISTINCT CASE WHEN RechargeCenters.CenterNo IS NOT NULL THEN RechargeCenters.CenterNo ELSE '无' END AS 售卡充值点, '2012-03-01' + '至' + '2012-03-28' AS 日期, CASE WHEN Free0051.Counts IS NOT NULL THEN Free0051.Counts ELSE '0' END AS 普通卡自然损坏数量, --CASE WHEN UnFree0051.Counts IS NOT NULL THEN UnFree0051.Counts ELSE '0' END AS 普通卡人为损坏数量, --CASE WHEN UnFree0051.Deposit IS NOT NULL THEN CAST(round(UnFree0051.Deposit,2) AS Decimal(18,2)) ELSE '0' END AS 普通卡人为损坏售卡金额, CASE WHEN Free0051.Counts IS NOT NULL THEN Free0051.Counts ELSE '0' END + CASE WHEN UnFree0051.Counts IS NOT NULL THEN UnFree0051e.Counts ELSE '0' END AS 合计数量, CASE WHEN UnFree0051.Deposit IS NOT NULL THEN CAST(round(UnFree0051.Deposit,2) AS Decimal(18,2)) ELSE '0' END AS 合计金额 from RechargeCenters LEFT JOIN ( SELECT CenterNo,Counts,Deposit FROM (SELECT ArchievedIssueRecords.IssueCenter as CenterNo, count(*) as Counts,sum(CardDeposit) as Deposit from ArchievedIssueRecords RIGHT JOIN CardBadRecords ON ArchievedIssueRecords.CardNo = CardBadRecords.NewCardNo WHERE ArchievedIssueRecords.CardType = '0051' and ArchievedIssueRecords.CardDeposit = '0' AND (CONVERT(varchar(10), ArchievedIssueRecords.IssueDate, 23)) BETWEEN '2011-01-01' AND '2012-12-31' group by ArchievedIssueRecords.IssueCenter) ) --消息 102,级别 15,状态 1,第 17 行 这里报的错误 AS Free0051 on CenterNo = RechargeCenters.CenterNo
select DISTINCT CASE WHEN RechargeCenters.CenterNo IS NOT NULL THEN RechargeCenters.CenterNo ELSE '无' END AS 售卡充值点, '2012-03-01' + '至' + '2012-03-28' AS 日期, CASE WHEN Free0051.Counts IS NOT NULL THEN Free0051.Counts ELSE '0' END AS 普通卡自然损坏数量, --CASE WHEN UnFree0051.Counts IS NOT NULL THEN UnFree0051.Counts ELSE '0' END AS 普通卡人为损坏数量, --CASE WHEN UnFree0051.Deposit IS NOT NULL THEN CAST(round(UnFree0051.Deposit,2) AS Decimal(18,2)) ELSE '0' END AS 普通卡人为损坏售卡金额, CASE WHEN Free0051.Counts IS NOT NULL THEN Free0051.Counts ELSE '0' END + CASE WHEN UnFree0051.Counts IS NOT NULL THEN UnFree0051e.Counts ELSE '0' END AS 合计数量, CASE WHEN UnFree0051.Deposit IS NOT NULL THEN CAST(round(UnFree0051.Deposit,2) AS Decimal(18,2)) ELSE '0' END AS 合计金额 from RechargeCenters LEFT JOIN ( SELECT CenterNo,Counts,Deposit FROM ( SELECT ArchievedIssueRecords.IssueCenter as CenterNo, count(*) as Counts,sum(CardDeposit) as Deposit from ArchievedIssueRecords RIGHT JOIN CardBadRecords ON ArchievedIssueRecords.CardNo = CardBadRecords.NewCardNo WHERE ArchievedIssueRecords.CardType = '0051' and ArchievedIssueRecords.CardDeposit = '0' AND (CONVERT(varchar(10), ArchievedIssueRecords.IssueDate, 23)) BETWEEN '2011-01-01' AND '2012-12-31' group by ArchievedIssueRecords.IssueCenter )as t ) --消息 102,级别 15,状态 1,第 17 行 这里报的错误 AS Free0051 on CenterNo = RechargeCenters.CenterNo
------解决方案--------------------
select DISTINCT CASE WHEN RechargeCenters.CenterNo IS NOT NULL THEN RechargeCenters.CenterNo ELSE '无' END AS 售卡充值点, '2012-03-01' + '至' + '2012-03-28' AS 日期, CASE WHEN Free0051.Counts IS NOT NULL THEN Free0051.Counts ELSE '0' END AS 普通卡自然损坏数量, --CASE WHEN UnFree0051.Counts IS NOT NULL THEN UnFree0051.Counts ELSE '0' END AS 普通卡人为损坏数量, --CASE WHEN UnFree0051.Deposit IS NOT NULL THEN CAST(round(UnFree0051.Deposit,2) AS Decimal(18,2)) ELSE '0' END AS 普通卡人为损坏售卡金额, CASE WHEN Free0051.Counts IS NOT NULL THEN Free0051.Counts ELSE '0' END + CASE