一个简单的SQL有关问题
一个简单的SQL问题?
表1: 列是A, 下边是表的查询数据
A
------
19
16
14
10
请问我想取出第一列19 减去第二列16怎么写语句? 谢谢!
------解决方案--------------------
--create test data
create table A(id int)
insert into A
select 19
union all
select 16
union all
select 14
union all
select 10
select identity(int,1,1) as mark,id into #t from A
select(select id from #t where mark=1)-(select id from #t where mark=3) as result
--drop tables
drop table A
drop table #t
------解决方案--------------------
create table t(A int)
insert into t(A)
select 19
union all
select 16
union all
select 14
union all
select 10
select identity(int,1,1) as id,A into #temp from t
select t1.A-t2.A as A from #temp t1
left join #temp t2 on t2.id=2
where t1.id=1
------解决方案--------------------
create table A(A int)
insert into A
select 19
union all
select 16
union all
select 14
union all
select 10
select distinct (select top 1 A from A)-
(
select top 1 A from A where A not in
(
select top 1 A from A
)
) from A
drop table A
------解决方案--------------------
也可以这样:
select
2*(select top 1 A from A)
-(select sum(A) from (select top 2 A from A) t)
楼上也可以去掉 distinct
select (select top 1 A from A)-
(
select top 1 A from A where A not in
(
select top 1 A from A
)
)
表1: 列是A, 下边是表的查询数据
A
------
19
16
14
10
请问我想取出第一列19 减去第二列16怎么写语句? 谢谢!
------解决方案--------------------
--create test data
create table A(id int)
insert into A
select 19
union all
select 16
union all
select 14
union all
select 10
select identity(int,1,1) as mark,id into #t from A
select(select id from #t where mark=1)-(select id from #t where mark=3) as result
--drop tables
drop table A
drop table #t
------解决方案--------------------
create table t(A int)
insert into t(A)
select 19
union all
select 16
union all
select 14
union all
select 10
select identity(int,1,1) as id,A into #temp from t
select t1.A-t2.A as A from #temp t1
left join #temp t2 on t2.id=2
where t1.id=1
------解决方案--------------------
create table A(A int)
insert into A
select 19
union all
select 16
union all
select 14
union all
select 10
select distinct (select top 1 A from A)-
(
select top 1 A from A where A not in
(
select top 1 A from A
)
) from A
drop table A
------解决方案--------------------
也可以这样:
select
2*(select top 1 A from A)
-(select sum(A) from (select top 2 A from A) t)
楼上也可以去掉 distinct
select (select top 1 A from A)-
(
select top 1 A from A where A not in
(
select top 1 A from A
)
)