SELECT TaxPayerID, TaxPayerName, iYear, BankName, TaxDeptName,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '1' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Jan1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '1' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Jan0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '2' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Feb1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '2' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Feb0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '3' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Mar1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '3' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Mar0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '4' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Apr1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '4' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Apr0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '5' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS May1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '5' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS May0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '6' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Jun1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '6' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Jun0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '7' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Jul1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '7' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Jul0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '8' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Aug1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '8' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Aug0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '9' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Sep1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '9' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Sep0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '10' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Oct1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '10' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Oct0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '11' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Nov1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '11' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Nov0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '12' AND CancelRep1.iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Dec1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '12' AND CancelRep1.iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Dec0
FROM dbo.CancelRep1 a
GROUP BY iYear, TaxPayerID, TaxPayerName, BankName, TaxDeptName
报错为
(所影响的行数为 13 行)服务器: 消息 512,级别 16,状态 1,行 1
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。麻烦谁能帮助一下解决这个问题
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '1' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Jan1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '1' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Jan0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '2' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Feb1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '2' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Feb0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '3' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Mar1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '3' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Mar0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '4' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Apr1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '4' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Apr0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '5' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS May1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '5' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS May0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '6' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Jun1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '6' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Jun0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '7' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Jul1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '7' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Jul0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '8' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Aug1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '8' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Aug0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '9' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Sep1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '9' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Sep0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '10' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Oct1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '10' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Oct0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '11' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Nov1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '11' AND iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Nov0,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '12' AND CancelRep1.iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID AND CancelOrNot = 1) AS Dec1,
(SELECT TaxMoney
FROM CancelRep1
WHERE iMonth = '12' AND CancelRep1.iYear = a.iYear AND
CancelRep1.TaxPayerID = a.TaxPayerID) AS Dec0
FROM dbo.CancelRep1 a
GROUP BY iYear, TaxPayerID, TaxPayerName, BankName, TaxDeptName
报错为
(所影响的行数为 13 行)服务器: 消息 512,级别 16,状态 1,行 1
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。麻烦谁能帮助一下解决这个问题
, (case when imonth='1' and then taxmoney else 0 end) as Jan0 ......
from cancelrep1
, (case when imonth='1' then taxmoney else 0 end) as Jan0 ......
from cancelrep1
A.col1,
A.col2,
x=(Select Top 1 colx From B Where B.id=A.id),
From A或者:
Select
A.col1,
A.col2,
x=(Select SUM(colx) From B Where B.id=A.id),
From A
子查询(第2个Select语句)返回的是多行记录,是不允许的。