use master
create database test
go
use testcreate table test1
(
id int identity(1,1) primary key,
senten11 varchar(50),
senten12 varchar(50),
senten13 varchar(50),
number1 int
)create table test2
(
id int identity(1,1) primary key,
senten21 varchar(50),
senten22 varchar(50),
senten23 varchar(50),
number2 int
)insert into test1 values('aa','bb','cc',10)
insert into test1 values('aa1','bb2','cc3',10)insert into test2 values('aa','bb','cc',2)
insert into test2 values('bb','dd','ee',4)select * from test1
select * from test2--如果两个表里面的sentence11 12 13 和对应表test2 里面21 22 23 都相同话 将test1的numer1减去 test2表里面对应的number2 如果没有符合相同条件 显示number1的数量--结果为:-- 1 aa bb cc 8
-- 2 aa1 bb2 cc3 10--这个sql怎么写啊 ?
create database test
go
use testcreate table test1
(
id int identity(1,1) primary key,
senten11 varchar(50),
senten12 varchar(50),
senten13 varchar(50),
number1 int
)create table test2
(
id int identity(1,1) primary key,
senten21 varchar(50),
senten22 varchar(50),
senten23 varchar(50),
number2 int
)insert into test1 values('aa','bb','cc',10)
insert into test1 values('aa1','bb2','cc3',10)insert into test2 values('aa','bb','cc',2)
insert into test2 values('bb','dd','ee',4)select * from test1
select * from test2--如果两个表里面的sentence11 12 13 和对应表test2 里面21 22 23 都相同话 将test1的numer1减去 test2表里面对应的number2 如果没有符合相同条件 显示number1的数量--结果为:-- 1 aa bb cc 8
-- 2 aa1 bb2 cc3 10--这个sql怎么写啊 ?
select t1.senten11, t1.senten12, t2.senten13,
(case when t1.senten11=t2.senten21 and t1.senten12=t2.senten22 and t1.senten13=t2.senten23
then t1.number-t2.number
else t1.number end) number
from test1 t1 inner join test2 t2
on t1.id=t2.id;
(case when t1.senten11=t2.senten21 and t1.senten12=t2.senten22 and t1.senten13=t2.senten23
then t1.number1-t2.number2
else t1.number1 end) number
from test1 t1 inner join test2 t2
on t1.id=t2.id多谢 等会 我往里面嵌套一下 看还有什么问题没有 谢谢 马上结贴啊
select t1.senten11, t1.senten12, t1.senten13,
(case when t1.senten11=t2.senten21 and t1.senten12=t2.senten22 and t1.senten13=t2.senten23
then t1.number1-t2.number2
else t1.number1 end) number
from test1 t1 ,test2 t2
但是结果就多出来2行来
create table test1
(
id int identity(1,1) primary key,
senten11 varchar(20),
senten12 varchar(20),
senten13 varchar(20),
number1 int
)create table test2
(
id int identity(1,1) primary key,
senten21 varchar(20),
senten22 varchar(20),
senten23 varchar(20),
number2 int
)insert into test1 values('aa','bb','cc',10)
insert into test1 values('aa1','bb2','cc3',10)insert into test2 values('aa','bb','cc',2)
insert into test2 values('bb','dd','ee',4)select * from test1
select * from test2--如果两个表里面的sentence11 12 13 和对应表test2 里面21 22 23 都相同话 将test1的numer1减去 test2表里面对应的number2 如果没有符合相同条件 显示number1的数量--结果为:-- 1 aa bb cc 8
-- 2 aa1 bb2 cc3 10--这个sql怎么写啊 ?
select test1.senten11,test1.senten12,test1.senten13 ,
isnull(test1.number1-test2.number2,test1.number1) as number from test1
left join test2 on test1.senten11=test2.senten21
and test1.senten12=test2.senten22
and test1.senten13=test2.senten23/*
senten11 senten12 senten13 number
-------------------- -------------------- -------------------- -----------
aa bb cc 8
aa1 bb2 cc3 10
*/
select
test1.id,
test1.senten11,
test1.senten12,
test1.senten13 ,
isnull(test1.number1-test2.number2,test1.number1) as number
from test1 left join test2
on test1.senten11=test2.senten21
and test1.senten12=test2.senten22
and test1.senten13=test2.senten23/*
id senten11 senten12 senten13 number
----------- -------------------- -------------------- -------------------- -----------
1 aa bb cc 8
2 aa1 bb2 cc3 10
*/
刚才我把id落下了。