SELECT 嵌套查询有关问题-MSSQL教程-爱易网页

SELECT 嵌套查询有关问题

日期：2014-05-18　浏览次数：20852 次

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

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

SELECT 嵌套查询有关问题

相关资料更多>

推荐阅读更多>