上面的我写错了 是想这样 { select count(wudcd) from 表a,表b where 表a.wudcd=表b.wudcd and 表b.stcd in (select stcd where m>0) }
这样是不对的。 select count(wudcd) from 表a,表b where 表a.wudcd=表b.wudcd 连接后。 它的值已经是4了!!!
表b.stcd in (select stcd where m>0) 你这个范围不会起作用的。 你测试就知道了!!!!
上面的我写错了 是想这样 { select count(a.wudcd) from a,b where a.wudcd=b.wudcd and b.stcd in (select c.stcd from c where c.m>0) }
你现在好好看看如何 这个也是正确的: { select count(a.wudcd) from a,b where a.wudcd=b.wudcd and b.stcd in (select c.stcd from c where ( (c.m is not null) and (c.m<>0) )) }
是想这样
{
select count(wudcd) from 表a,表b
where 表a.wudcd=表b.wudcd and
表b.stcd in (select stcd where m>0)
}
select count(wudcd) from 表a,表b
where 表a.wudcd=表b.wudcd
连接后。
它的值已经是4了!!!
你这个范围不会起作用的。
你测试就知道了!!!!
是想这样
{
select count(a.wudcd) from a,b
where a.wudcd=b.wudcd and
b.stcd in (select c.stcd from c where c.m>0)
}
这个也是正确的:
{
select count(a.wudcd) from a,b
where a.wudcd=b.wudcd and
b.stcd in (select c.stcd from c where ( (c.m is not null) and (c.m<>0) ))
}
我在SQL的查询分析器里测试过的了
上面两个都是正确的,你好好看看吧
你现在试试重建你的表看看,我是这样建的表
你看看
if exists (select * from sysobjects where id = OBJECT_ID('[a]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [a]
CREATE TABLE [a] ( [WUDCD] [int] NULL , [x] [varchar] (50) NULL , [y] [varchar] (50) NULL ) INSERT [a] ( [WUDCD] , [x] , [y] ) VALUES ( 222 , '中国' , '日本' )
INSERT [a] ( [WUDCD] , [x] , [y] ) VALUES ( 223 , '美国' , '台湾' )
INSERT [a] ( [WUDCD] , [x] , [y] ) VALUES ( 224 , '俄罗斯' , '台湾' )
INSERT [a] ( [WUDCD] , [x] , [y] ) VALUES ( 225 , '印度' , '巴西' )
if exists (select * from sysobjects where id = OBJECT_ID('[b]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [b]
CREATE TABLE [b] ( [WUDCD] [int] NULL , [STCD] [int] NULL , [z] [int] NULL ) INSERT [b] ( [WUDCD] , [STCD] , [z] ) VALUES ( 222 , 1 , 344 )
INSERT [b] ( [WUDCD] , [STCD] , [z] ) VALUES ( 223 , 2 , 33 )
INSERT [b] ( [WUDCD] , [STCD] , [z] ) VALUES ( 224 , 3 , 555 )
INSERT [b] ( [WUDCD] , [STCD] , [z] ) VALUES ( 225 , 4 , 111 ) if exists (select * from sysobjects where id = OBJECT_ID('[c]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [c]
CREATE TABLE [c] ( [STCD] [int] NULL , [m] [int] NULL ) INSERT [c] ( [STCD] ) VALUES ( 1 )
INSERT [c] ( [STCD] , [m] ) VALUES ( 2 , 0 )
INSERT [c] ( [STCD] , [m] ) VALUES ( 3 , 22 )
INSERT [c] ( [STCD] , [m] ) VALUES ( 4 , 55 )
select stcd where isnull(m,0)<>0把条件这样写应该就没有问题了.
你的SQL确实没有问题。结果也正确。
但我用到我的实际中确实出现问题。
我真没弄明白
in
(
子查询
)in 的用法:
如果 外面的条件大于 子查询的结果集,会缺外面的还是里面的呢???