1. 爱易网页
  2. MSSQL教程
  3. SELECT 嵌套查询有关问题

SELECT 嵌套查询有关问题

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

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

相关资料更多>

  1. 小弟我的数据库丢失了
  2. SQL 2005 数据加密 －－来自微软视频教程解决方法
  3. 用web service适宜吗
  4. 怎么清除登录SQL SERVER 的时候登录名选项
  5. update set 的有关问题
  6. 求数据交叉显示的储存过程,该怎么处理
  7. sqlserver2008本机执行OSQL有关问题,求大神解决!
  8. 几个计算成员的有关问题
  9. SQL 嵌套查询,该如何处理