select DISTINCT
'2011-01-01' + '至' + '2012-12-31' AS 日期,
CASE WHEN SystemOperators.OperatorId IS NOT NULL THEN SystemOperators.OperatorId ELSE '无' END AS 操作员,
CASE WHEN ConsumeCardType.CardTag IS NOT NULL THEN ConsumeCardType.CardTag ELSE '无' END AS 卡类型,
CASE WHEN Oper10.FreeBadCounts IS NOT NULL THEN Oper10.FreeBadCounts ELSE '0' END AS 自然损坏张数from SystemOperators,ConsumeCardTypeLEFT JOIN
(
SELECT OperId,CardType,FreeBadCounts,Deposit FROM
(
SELECT ArchievedIssueRecords.Issuer as OperId,ArchievedIssueRecords.CardType AS CardType ,count(*) as FreeBadCounts,sum(CardDeposit) as Deposit
from ArchievedIssueRecords
RIGHT JOIN CardBadRecords ON ArchievedIssueRecords.CardNo = CardBadRecords.NewCardNo
WHERE ArchievedIssueRecords.CardDeposit = '0' AND Issuer = '10' AND (CONVERT(varchar(10), ArchievedIssueRecords.IssueDate, 23)) BETWEEN '2011-01-01' AND '2012-12-31'
group by ArchievedIssueRecords.Issuer,ArchievedIssueRecords.CardType
) t
) AS Oper10 on Oper10.CardType = ConsumeCardType.CardTag AND Oper10.OperId = SystemOperators.OperatorId
'2011-01-01' + '至' + '2012-12-31' AS 日期,
CASE WHEN SystemOperators.OperatorId IS NOT NULL THEN SystemOperators.OperatorId ELSE '无' END AS 操作员,
CASE WHEN ConsumeCardType.CardTag IS NOT NULL THEN ConsumeCardType.CardTag ELSE '无' END AS 卡类型,
CASE WHEN Oper10.FreeBadCounts IS NOT NULL THEN Oper10.FreeBadCounts ELSE '0' END AS 自然损坏张数from SystemOperators,ConsumeCardTypeLEFT JOIN
(
SELECT OperId,CardType,FreeBadCounts,Deposit FROM
(
SELECT ArchievedIssueRecords.Issuer as OperId,ArchievedIssueRecords.CardType AS CardType ,count(*) as FreeBadCounts,sum(CardDeposit) as Deposit
from ArchievedIssueRecords
RIGHT JOIN CardBadRecords ON ArchievedIssueRecords.CardNo = CardBadRecords.NewCardNo
WHERE ArchievedIssueRecords.CardDeposit = '0' AND Issuer = '10' AND (CONVERT(varchar(10), ArchievedIssueRecords.IssueDate, 23)) BETWEEN '2011-01-01' AND '2012-12-31'
group by ArchievedIssueRecords.Issuer,ArchievedIssueRecords.CardType
) t
) AS Oper10 on Oper10.CardType = ConsumeCardType.CardTag AND Oper10.OperId = SystemOperators.OperatorId
'2011-01-01' + '至' + '2012-12-31' AS 日期,
CASE WHEN SystemOperators.OperatorId IS NOT NULL THEN SystemOperators.OperatorId ELSE '无' END AS 操作员,
CASE WHEN ConsumeCardType.CardTag IS NOT NULL THEN ConsumeCardType.CardTag ELSE '无' END AS 卡类型,
CASE WHEN Oper10.FreeBadCounts IS NOT NULL THEN Oper10.FreeBadCounts ELSE '0' END AS 自然损坏张数from SystemOperators
inner join ConsumeCardType on ConsumeCardType.CardTag=SystemOperators.OperatorIdLEFT JOIN
(
SELECT OperId,CardType,FreeBadCounts,Deposit FROM
(
SELECT ArchievedIssueRecords.Issuer as OperId,ArchievedIssueRecords.CardType AS CardType ,count(*) as FreeBadCounts,sum(CardDeposit) as Deposit
from ArchievedIssueRecords
RIGHT JOIN CardBadRecords ON ArchievedIssueRecords.CardNo = CardBadRecords.NewCardNo
WHERE ArchievedIssueRecords.CardDeposit = '0' AND Issuer = '10' AND (CONVERT(varchar(10), ArchievedIssueRecords.IssueDate, 23)) BETWEEN '2011-01-01' AND '2012-12-31'
group by ArchievedIssueRecords.Issuer,ArchievedIssueRecords.CardType
) t
) AS Oper10 on Oper10.CardType = ConsumeCardType.CardTag试试
select DISTINCT
'2011-01-01' + '至' + '2012-12-31' AS 日期,
CASE WHEN a.OperatorId IS NOT NULL
THEN SystemOperators.OperatorId ELSE '无' END AS 操作员,
CASE WHEN b.CardTag IS NOT NULL
THEN b.CardTag ELSE '无' END AS 卡类型,
CASE WHEN [Oper10].FreeBadCounts IS NOT NULL
THEN [Oper10].FreeBadCounts ELSE '0' END AS 自然损坏张数
from SystemOperators a,ConsumeCardType b
LEFT JOIN
(
SELECT OperId,CardType,FreeBadCounts,Deposit FROM
(
SELECT ArchievedIssueRecords.Issuer as OperId,
ArchievedIssueRecords.CardType AS CardType ,
count(*) as FreeBadCounts,sum(CardDeposit) as Deposit
from ArchievedIssueRecords
RIGHT JOIN CardBadRecords
ON ArchievedIssueRecords.CardNo = CardBadRecords.NewCardNo
WHERE ArchievedIssueRecords.CardDeposit = '0' AND Issuer = '10'
AND (CONVERT(varchar(10), ArchievedIssueRecords.IssueDate, 23))
BETWEEN '2011-01-01' AND '2012-12-31'
group by ArchievedIssueRecords.Issuer,ArchievedIssueRecords.CardType
) t
) AS [Oper10] on [Oper10].CardType = ConsumeCardType.CardTag
AND Oper10.OperId = [SystemOperators].OperatorId 由于你的嵌套子查询也使用了跟外面的查询相同的表名,所以你需要加别名区分开,不然无法识别
select DISTINCT
'2011-01-01' + '至' + '2012-12-31' AS 日期,
CASE WHEN a.OperatorId IS NOT NULL
THEN SystemOperators.OperatorId ELSE '无' END AS 操作员,
CASE WHEN b.CardTag IS NOT NULL
THEN b.CardTag ELSE '无' END AS 卡类型,
CASE WHEN [Oper10].FreeBadCounts IS NOT NULL
THEN [Oper10].FreeBadCounts ELSE '0' END AS 自然损坏张数
from SystemOperators a,ConsumeCardType b
LEFT JOIN
(
SELECT OperId,CardType,FreeBadCounts,Deposit FROM
(
SELECT ArchievedIssueRecords.Issuer as OperId,
ArchievedIssueRecords.CardType AS CardType ,
count(*) as FreeBadCounts,sum(CardDeposit) as Deposit
from ArchievedIssueRecords
RIGHT JOIN CardBadRecords
ON ArchievedIssueRecords.CardNo = CardBadRecords.NewCardNo
WHERE ArchievedIssueRecords.CardDeposit = '0' AND Issuer = '10'
AND (CONVERT(varchar(10), ArchievedIssueRecords.IssueDate, 23))
BETWEEN '2011-01-01' AND '2012-12-31'
group by ArchievedIssueRecords.Issuer,ArchievedIssueRecords.CardType
) t
) AS [Oper10] on [Oper10].CardType = ConsumeCardType.CardTag
AND [Oper10].OperId = a.OperatorId 你的语句好乱
消息 4104,级别 16,状态 1,第 2 行
无法绑定由多个部分组成的标识符 "ConsumeCardType.CardTag"。
消息 4104,级别 16,状态 1,第 2 行
无法绑定由多个部分组成的标识符 "a.OperatorId"。
消息 4104,级别 16,状态 1,第 2 行
无法绑定由多个部分组成的标识符 "SystemOperators.OperatorId"。
select DISTINCT
'2011-01-01' + '至' + '2012-12-31' AS 日期,
CASE WHEN a.OperatorId IS NOT NULL
THEN SystemOperators.OperatorId ELSE '无' END AS 操作员,
CASE WHEN b.CardTag IS NOT NULL
THEN b.CardTag ELSE '无' END AS 卡类型,
CASE WHEN [Oper10].FreeBadCounts IS NOT NULL
THEN [Oper10].FreeBadCounts ELSE '0' END AS 自然损坏张数
from SystemOperators a,ConsumeCardType b
LEFT JOIN--上面这一段你改写成连接查询,不要用你的这种方式,没见过这样写的