--假定上面说的情况取true --sql中我没见过bool数据类型,所以我用bit来表示了(1--true, 0--false) create table a(id int, vid varchar(10), vdate varchar(100)) create table b(id int, vid varchar(10), state bit) insert a select 1, 'v007','abc' union all select 2, 'v008','efc' union all select 3, 'v009','bcs' insert b select 20, 'v007', 0 union all select 20, 'v007', 1 union all select 20, 'v008', 1 --查询 select * ,state=(case when (select 1 from b where a.vid=b.vid and b.state=1) is not null then 'true' else 'false' end)
from a --清除 drop table a drop table b
select a.id,A.VID,A.Vdata, case when state='true' then 'true' else 'false' end from A left join B on a.VID = B.VID
SELECT max(A.ID) as ID, A.VID, max(A.Vdata), iif(sum(IIf(B.State=true,1,0))=1,true,false) as stat from A left join B on a.VID = B.VID group by a.vid
这样应该保险一点。 SELECT max(A.ID) as ID, A.VID, max(A.Vdata), iif(sum(IIf(B.State=true,1,0))=0,false,true) as stat from A left join B on a.VID = B.VID group by a.vid
tangqijun199(撒旦. 不行呀,Max() 怎么可以 Group By ?? 只有Sum() 才能进行 Group By 的啊
实在不行就试试这样 SELECT A.ID, A.VID, A.Vdata, iif(sum(IIf(B.State=true,1,0))=0,false,true) as stat from A left join B on a.VID = B.VID group by a.id,a.vid,a.vdata
是呀,在Access查询里提示类型不匹配,我都不懂你的为什么就行,你看看应该没错吧SELECT A.id, A.vid, A.vdata, IIf(Sum(IIf(B.State=True,1,0))=0,False,True) AS stat FROM A LEFT JOIN B ON A.vid = B.vid GROUP BY A.id, A.vid, A.vdata;
楼主用的是不是access2000我测试通过了啊
测试数据 ____________________________________________ A表 id, vid, vdata 1 v007 xxxx 2 v022 xxxx 3 v018 xxxx 4 v034 xxxx ____________________ B表 id vid state25 v018 true 26 v007 true 27 v022 false 28 v007 false 29 v034 false ___________________________ SELECT max(A.ID) as ID, A.VID, max(A.Vdata), iif(sum(IIf(B.State=true,1,0))=0,false,true) as stat from A left join B on a.VID = B.VID group by a.vid结果 1 v007 xxxx -1 2 v018 xxxx -1 3 v022 xxxx 0 4 v034 xxxx 0 _______________________________________________________ SELECT max(A.ID) as ID, A.VID, max(A.Vdata), iif(sum(IIf(B.State=true,1,0))=0,'false','true') as stat from A left join B on a.VID = B.VID group by a.vid结果 1 v007 xxxx true 2 v018 xxxx true 3 v022 xxxx false 4 v034 xxxx false ___________________________________________SELECT A.ID, A.VID, A.Vdata, iif(sum(IIf(B.State=true,1,0))=0,'false','true') AS stat FROM A LEFT JOIN B ON a.VID = B.VID GROUP BY a.id, a.vid, a.vdata 结果1 v007 xxxx true 2 v022 xxxx false 3 v018 xxxx true 4 v034 xxxx false
SELECT A.ID, A.VID, A.Vdata, sum(IIf(B.State=true,1,0) AS stat FROM A LEFT JOIN B ON a.VID = B.VID GROUP BY a.id, a.vid, a.vdata;这个OK了,谢谢撒旦,谢谢大家啦
--sql中我没见过bool数据类型,所以我用bit来表示了(1--true, 0--false)
create table a(id int, vid varchar(10), vdate varchar(100))
create table b(id int, vid varchar(10), state bit)
insert a select 1, 'v007','abc'
union all select 2, 'v008','efc'
union all select 3, 'v009','bcs'
insert b select 20, 'v007', 0
union all select 20, 'v007', 1
union all select 20, 'v008', 1
--查询
select *
,state=(case when (select 1 from b
where a.vid=b.vid and b.state=1)
is not null then 'true' else 'false' end)
from a
--清除
drop table a
drop table b
from A left join B on a.VID = B.VID
正在用C#开发winform 只能用Access 唉...
我要求当B.VID 有多个重复的时候,只显示True的那一行,其余忽略。
再说明:表A就好比图书馆的书库,表B就记录借、还。
当书本被借出B.State=True 归还后B.State=False;
当我们SHOW表A时,就可以看到书库里全部的书 和 哪本书处于借出状态。谢谢!
A.VID,
max(A.Vdata),
iif(sum(IIf(B.State=true,1,0))=1,true,false) as stat
from A left join B on a.VID = B.VID
group by a.vid
SELECT max(A.ID) as ID,
A.VID,
max(A.Vdata),
iif(sum(IIf(B.State=true,1,0))=0,false,true) as stat
from A left join B on a.VID = B.VID
group by a.vid
只有Sum() 才能进行 Group By 的啊
SELECT A.ID,
A.VID,
A.Vdata,
iif(sum(IIf(B.State=true,1,0))=0,false,true) as stat
from A left join B on a.VID = B.VID
group by a.id,a.vid,a.vdata
FROM A LEFT JOIN B ON A.vid = B.vid
GROUP BY A.id, A.vid, A.vdata;
测试数据
____________________________________________
A表
id, vid, vdata
1 v007 xxxx
2 v022 xxxx
3 v018 xxxx
4 v034 xxxx
____________________
B表
id vid state25 v018 true
26 v007 true
27 v022 false
28 v007 false
29 v034 false
___________________________
SELECT max(A.ID) as ID,
A.VID,
max(A.Vdata),
iif(sum(IIf(B.State=true,1,0))=0,false,true) as stat
from A left join B on a.VID = B.VID
group by a.vid结果
1 v007 xxxx -1
2 v018 xxxx -1
3 v022 xxxx 0
4 v034 xxxx 0
_______________________________________________________
SELECT max(A.ID) as ID,
A.VID,
max(A.Vdata),
iif(sum(IIf(B.State=true,1,0))=0,'false','true') as stat
from A left join B on a.VID = B.VID
group by a.vid结果
1 v007 xxxx true
2 v018 xxxx true
3 v022 xxxx false
4 v034 xxxx false
___________________________________________SELECT A.ID, A.VID, A.Vdata, iif(sum(IIf(B.State=true,1,0))=0,'false','true') AS stat
FROM A LEFT JOIN B ON a.VID = B.VID
GROUP BY a.id, a.vid, a.vdata
结果1 v007 xxxx true
2 v022 xxxx false
3 v018 xxxx true
4 v034 xxxx false
FROM A LEFT JOIN B ON a.VID = B.VID
GROUP BY a.id, a.vid, a.vdata;这个OK了,谢谢撒旦,谢谢大家啦