WHERE子句中的SQL Server结果列
问题描述:
SELECT
MyColumn = 'something'
FROM table
WHERE MyColumn == 'something'
可能在WHERE子句中使用MyColumn
?WHERE子句中的SQL Server结果列
编辑:
下面是完整的查询:
select TOP 10
PremiumYTDCurrent=Sum(CASE WHEN
AASI.Inv_Acctcur>='201101'
and AASI.Inv_Acctcur<='201102'
THEN (AASI.Inv_Premium)*R.[Percent]
ELSE 0 END),
PremiumYTDPrevious=Sum(CASE WHEN
AASI.Inv_Acctcur>='201001'
and AASI.Inv_Acctcur<='201002'
THEN (AASI.Inv_Premium)*R.[Percent]
ELSE 0 END),
R.STAFF, L.Description, L.LINE_OF_BUSINESS
from AAS_Invoice AASI,Invoice I,Revenue_Tracking R, Policy P, Line_Of_Business L
where I.Invoice_No=convert(Char,Convert(int,AASI.Inv_Entry_Num))
and I.Invoice=R.Invoice
and I.POLICY=P.POLICY
and L.LINE_OF_BUSINESS=P.LINE_OF_BUSINESS
and R.Organization IN (SELECT ST.ORGANIZATION FROM Staff ST WHERE ST.STAFF=14407)
and R.Staff=14407
and R.Activity_type='Broker'
and R.[Percent]>0
and PremiumYTDCurrent != 0
group by R.STAFF, L.Description, L.LINE_OF_BUSINESS
order by PremiumYTDCurrent DESC, PremiumYTDPrevious DESC, average_policy DESC
答
在where子句中不能使用的列。改用表达式。
and Sum(CASE WHEN
AASI.Inv_Acctcur>='201101'
and AASI.Inv_Acctcur<='201102'
THEN (AASI.Inv_Premium)*R.[Percent]
ELSE 0 END) <> 0
编辑1 没有注意到SUM条款。 尝试添加它作为一个HAVING子句,而不是按顺序。
having Sum(CASE WHEN
AASI.Inv_Acctcur>='201101'
and AASI.Inv_Acctcur<='201102'
THEN (AASI.Inv_Premium)*R.[Percent]
ELSE 0 END) != 0
答
你可以换的SQL了在嵌套声明中,一个窘况简单的例子是,如:
SELECT MyMadeUpColumnName, col2, AnotherMadeUpColumn FROM (
SELECT SUM(sillycolumn) AS 'MyMadeUpColumnName', col2 FROM table GROUP BY col2
) AS t
WHERE t.AnotherMadeUpColumn <> 0
任何列名,你(重新)定义派生表成为实际列父级选择的名称。
我不明白,只要MyColumn存在于数据库中,你可以做上述查询 – 2011-02-01 10:52:56
这就是说,MyColumn是由用户定义的,它不存在于db – 2011-02-01 10:55:39