SELECT 嵌套查询有关问题

SELECT 嵌套查询问题

SQL code

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

报错:
消息 102，级别 15，状态 1，第 17 行
')' 附近有语法错误。


[解决办法]
CASE WHEN UnFree0051.Deposit IS NOT NULL THEN CAST(round(UnFree0051.Deposit,2) AS Decimal(18,2)) ELSE '0' 

看见没有CAST(round(UnFree0051.Deposit,2) 少一个括号
[解决办法]

SQL code

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
[解决办法]
SQL code
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 别名 --消息 102，级别 15，状态 1，第 17 行 这里报的错误AS  Free0051 on CenterNo = RechargeCenters.CenterNo表表达式作为输入表时必须取别名 
  

 

查看更多 下一篇