条件:
表A的字段有ID,A,B,C
表B的字段有PID,D
表A.ID=表B.PID,表B的字段D是更改表A的字段A得到的。
目的:
如果有B.PID=A.ID,则组合成这样的语句:select A.ID,B.D,A.B,A.C from A,B where A.ID=B.PID
否则,则组合成这样的语句:select ID,A,B,C from A
条件: 表A的字段有ID,A,B,C 表B的字段有PID,D 表A.ID=表B.PID,表B的字段D是更改表A的字段A得到的。 目的: 如果有B.PID=A.ID,则组合成这样的语句:select A.ID,B.D,A.B,A.C from A,B where A.ID=B.PID 否则,则组合成这样的语句:select ID,A,B,C from A 请问:能够用一条语句完成吗?比如说select b,c,(if......else......) 谢谢各位。
再试一次,上次方式对头,不过条件限制简单一些,有些问题
SELECT A.ID,B.D,A.B,A.C FROM A,B WHERE A.ID=B.PID AND (SELECT COUNT(*) FROM A,B WHERE A.ID=B.PID)>0
UNION
SELECT A.ID,A.A,A.B,A.C FROM A,B WHERE (SELECT COUNT(*) FROM A,B WHERE A.ID=B.PID)=0
--测试数据
create table A(id int,A varchar(10),B varchar(10),C varchar(10))
insert A select 1,'aa1','bb1','cc1'
union all select 2,'aa2','bb2','cc2'
union all select 3,'aa3','bb3','cc3'
union all select 4,'aa4','bb4','cc4'
create table B(pid int,D varchar(10))
insert B select 1,'zz1'
union all select 2,'zz2'
go
--查询
select a.id,d=isnull(b.d,a.a)
,a.b,a.c
from A
left join B on A.ID=B.PID
go
--删除测试环境
drop table a,b
/*--测试结果
id d b c
----------- ---------- ---------- ----------
1 zz1 bb1 cc1
2 zz2 bb2 cc2
3 aa3 bb3 cc3
4 aa4 bb4 cc4
create table 表A(
id int,
A varchar(10),
B varchar(10),
C varchar(10)
)
go
create table 表B(
pid int,
D varchar(10)
)
go
insert into 表A values(1,'aa1','bb1','cc1')
insert into 表A values(2,'aa2','bb2','cc2')
insert into 表A values(3,'aa3','bb3','cc3')
insert into 表A values(4,'aa4','bb4','cc4')
insert into 表B values(1,'zz1')
insert into 表B values(2,'zz2')
select * from 表A
select * from 表B
select a.ID,isnull(表B.D,(select 表A.A from 表A where id=a.id)) as A ,a.B,a.C from 表A a,表B where a.ID*=表B.PID