刚才的问题
现有四个sql语句:
select Left(BankCode,6),sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='a'
Group by left(BankCode,6) having Left(BankCode,6) not in ('080313','080402')
order by left(BankCode,6)select BankCode,sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='a'
and Left(BankCode,6) in ('080313','080402')
Group by BankCode
order by BankCodeselect Left(BankCode,6),sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='b'
Group by left(BankCode,6) having Left(BankCode,6) not in ('080313','080402')
order by left(BankCode,6)select BankCode,sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='b'
and Left(BankCode,6) in ('080313','080402')
Group by BankCode
order by BankCode生成四个数据集,现在要只生成一个数据集
如下:(例如)
bankcode tag sum(TxnNum)
01234 a 22
01234 b 33
0803131 a 23
0803131 b 34
0803132 a 55
0803132 b 53
现有四个sql语句:
select Left(BankCode,6),sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='a'
Group by left(BankCode,6) having Left(BankCode,6) not in ('080313','080402')
order by left(BankCode,6)select BankCode,sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='a'
and Left(BankCode,6) in ('080313','080402')
Group by BankCode
order by BankCodeselect Left(BankCode,6),sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='b'
Group by left(BankCode,6) having Left(BankCode,6) not in ('080313','080402')
order by left(BankCode,6)select BankCode,sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='b'
and Left(BankCode,6) in ('080313','080402')
Group by BankCode
order by BankCode生成四个数据集,现在要只生成一个数据集
如下:(例如)
bankcode tag sum(TxnNum)
01234 a 22
01234 b 33
0803131 a 23
0803131 b 34
0803132 a 55
0803132 b 53
where tag='a'
Group by left(BankCode,6) having Left(BankCode,6) not in ('080313','080402')
order by left(BankCode,6)select BankCode,sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='a'
and Left(BankCode,6) in ('080313','080402')
Group by BankCode
order by BankCode
union all
select Left(BankCode,6),sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='b'
Group by left(BankCode,6) having Left(BankCode,6) not in ('080313','080402')
order by left(BankCode,6)
union all
select BankCode,sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='b'
and Left(BankCode,6) in ('080313','080402')
Group by BankCode
order by BankCode
union all
where tag='a'
Group by left(BankCode,6) having Left(BankCode,6) not in ('080313','080402')
order by left(BankCode,6)
union all
select BankCode,sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='a'
and Left(BankCode,6) in ('080313','080402')
Group by BankCode
order by BankCode
union all
select Left(BankCode,6),sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='b'
Group by left(BankCode,6) having Left(BankCode,6) not in ('080313','080402')
order by left(BankCode,6)
union all
select BankCode,sum(TxnNum) from BasicDataPerDayOnBank_II
where tag='b'
and Left(BankCode,6) in ('080313','080402')
Group by BankCode
order by BankCode
then BankCode else Left(BankCode,6) end BankCode,
InExCode,sum(TxnNum)
from BasicDataPerDayOnBank_II
Where OBcbFlag='Rcv'
and OalId='A2'
and Rspcode='14'
and Datemsg like '200301%'
group by
case when left(BankCode,6) in ('080313','080402')
then BankCode else Left(BankCode,6) end,
InExCode
order by case when left(BankCode,6) in ('080313','080402')
then 1 else 0 end,left(BankCode,6),InExCode
then BankCode else Left(BankCode,6) end BankCode,
InExCode,sum(TxnNum)
from BasicDataPerDayOnBank_II
Where OBcbFlag='Rcv'
and OalId='A2'
and Rspcode='14'
and Datemsg like '200301%'
group by
case when left(BankCode,6) in ('080313','080402')
then BankCode else Left(BankCode,6) end BankCode, ------
InExCode
order by case when left(BankCode,6) in ('080313','080402')
then 1 else 0 end,left(BankCode,6),InExCode
报错
第 57 行: 'bankcode' 附近有语法错误。
then BankCode else Left(BankCode,6) end BankCode,
InExCode,sum(TxnNum)
from BasicDataPerDayOnBank_II
Where OBcbFlag='Rcv'
and OalId='A2'
and Rspcode='14'
and Datemsg like '200301%'
group by
case when left(BankCode,6) in ('080313','080402')
then BankCode else Left(BankCode,6) end, ------
InExCode
order by case when left(BankCode,6) in ('080313','080402')
then 1 else 0 end,left(BankCode,6),InExCode
报错
列名 'BasicDataPerDayOnBank_II.BankCode' 在 ORDER BY 子句中无效,因
为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
select case when left(BankCode,6) in ('080313','080402')
then BankCode else Left(BankCode,6) end BankCode,
InExCode,sum(TxnNum)
from BasicDataPerDayOnBank_II
Where OBcbFlag='Rcv'
and OalId='A2'
and Rspcode='14'
and Datemsg like '200301%'
group by
case when left(BankCode,6) in ('080313','080402')
then BankCode else Left(BankCode,6) end, ------
InExCode
order by left(BankCode,6),InExCode--order by 后不要加:case when left(BankCode,6) in ('080313','080402')
then BankCode else Left(BankCode,6) end,
列名 'BasicDataPerDayOnBank_II.BankCode' 在 ORDER BY 子句中无效,因
为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。