帮忙看看这个统计问题!

[xufengo0oID]

我想统计出以下例如:uid='W0000017'的情况下存在和它有相同level值但input值是相反的纪录总数!
input uid level
否 W0000012 130300
是 W0000017 130300
否 W0000015 020000
是 W0000017 020000
是 W0000013 020000
否 W0000017 020000
是 W0000016 130300
否 W0000017 020110
。。。。。。。
帮帮忙!

[lsqkeke]

select count(1) from tb a,(select input ,level from tb where uid='W0000017')b
where a.input<>b.input and a.level=b.level

[xeqtr1982]

同意可可：）

[quanquanfu]

用个临时表很好解决！

[子陌红尘]

declare @t table(input varchar(4),uid varchar(10),level varchar(10))
insert into @t select '否','W0000012','130300'
insert into @t select '是','W0000017','130300'
insert into @t select '否','W0000015','020000'
insert into @t select '是','W0000017','020000'
insert into @t select '是','W0000013','020000'
insert into @t select '否','W0000017','020000'
insert into @t select '是','W0000016','130300'
insert into @t select '否','W0000017','020110'

select count(*) from @t a where a.uid='W0000017' and exists(select 1 from @t where level=a.level and input!=a.input)

/*
-----------
3
*/

[lsqkeke]

反复理解了楼主的意思，应该说的是：


create table tb(input varchar(5),uid varchar(30), level varchar(20))
insert tb
select '否' , 'W0000012', '130300' union all
select '是' ,'W0000017' , '130300' union all
select '否' ,'W0000015' , '020000' union all
select '是', 'W0000017' , '020000' union all
select '是' ,'W0000013' , '020000' union all
select '否' ,'W0000017' , '020000' union all
select '是' , 'W0000016' , '130300' union all
select '否','W0000017' , '020110'

select count(1) from tb a where uid='W0000017' and exists(select 1 from tb where input='是' and [level]=a.[level] and uid=a.uid )
and exists(select 1 from tb where input='否' and [level]=a.[level] and uid=a.uid)

结构是:
2

[-狙击手-]

declare @t table(input char(2),uid varchar(10),level varchar(10))
insert @t
select '否','W0000012','130300' union all
select '是','W0000017','130300' union all
select '否','W0000015','020000' union all
select '是','W0000017','020000' union all
select '是','W0000013','020000' union all
select '否','W0000017','020000' union all
select '是','W0000016','130300' union all
select '否','W0000017','020110'


select level,(select count(1) from @t where level = a.level and input != a.input)
from @t a
where a.uid = 'W0000017'