declare @t1 table(数值 decimal(10,2))
insert into @t1 values(200)
insert into @t1 values(150)
insert into @t1 values(199)
insert into @t1 values(200) select sum(数值)as a ,a*2 as b from @t1
想要的结果
===========================================
a b
749.00 1498.00
===========================================改SQL语句中在查询分析器中提示:列名 'a' 无效。
请大家帮忙!
insert into @t1 values(200)
insert into @t1 values(150)
insert into @t1 values(199)
insert into @t1 values(200) select sum(数值)as a ,sum(数值)*2 as b from @t1/*
a b
---------------------------------------- ----------------------------------------
749.00 1498.00(所影响的行数为 1 行)
*/
insert into @t1 values(200)
insert into @t1 values(150)
insert into @t1 values(199)
insert into @t1 values(200) select sum(数值)as a ,sum(数值)*2 as b from @t1
/*a b
--------------------------------------- ---------------------------------------
749.00 1498.00(1 行受影响)*/
insert into @t1 values(200)
insert into @t1 values(150)
insert into @t1 values(199)
insert into @t1 values(200) SELECT *,a*2 AS b
FROM (
select sum(数值)as a from @t1
) t
/*
a b
--------------------------------------- ---------------------------------------
749.00 1498.00(1 行受影响)
*/
--or
select a, a*2 from (select sum(数值)as a from @t1) m
insert into @t1 values(200)
insert into @t1 values(150)
insert into @t1 values(199)
insert into @t1 values(200) --1
select sum(数值)as a ,sum(数值)*2 as b from @t1--2
select a , a*2 b from
(
select sum(数值)as a from @t1
) t/*
a b
---------------------------------------- ----------------------------------------
749.00 1498.00(所影响的行数为 1 行)
*/
(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
就如同以下SQL语句:
select sum(数值)as a ,a*2 as b from @t1SUM(数值)已经存在与变量a 中了!我为什么不能用直接用a*2来得到一个新列?
declare @t1 table(数值 decimal(10,2))
insert into @t1 values(200)
insert into @t1 values(150)
insert into @t1 values(199)
insert into @t1 values(200) select sum(数值)as a ,sum(数值)*2 as b from @t1你直接用a他是不认的
(8) select (9) distinct (11)<top_specificationg> <select_list>
(1) from <left_table>
(3) join <right_table>
(2) on <join_condition>
(4) where <where_condition>
(5) group by <group_by_list>
(6) with {culb | rollup}
(7) having<having_condition>
(10) order by <order_by_list>