帮忙看看这个统计问题!

xufengo0oID 2006-03-15 02:57:27
我想统计出以下例如:uid='W0000017'的情况下存在和它有相同level值但input值是相反的纪录总数!
input uid level
否 W0000012 130300
是 W0000017 130300
否 W0000015 020000
是 W0000017 020000
是 W0000013 020000
否 W0000017 020000
是 W0000016 130300
否 W0000017 020110
。。。。。。。
帮帮忙!
...全文
55 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
-狙击手- 2006-03-15
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'


回复
lsqkeke 2006-03-15
反复理解了楼主的意思,应该说的是:


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
回复
子陌红尘 2006-03-15
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
*/
回复
quanquanfu 2006-03-15
用个临时表很好解决!
回复
xeqtr1982 2006-03-15
同意可可:)
回复
lsqkeke 2006-03-15
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
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-03-15 02:57
社区公告
暂无公告