select * from Sep a
where ntype = 2 and not exists(
select 1 from Sep
where nid = a.nid and nDate = a.nDate and nName = a.nName and nType = 1
)
where ntype = 2 and not exists(
select 1 from Sep
where nid = a.nid and nDate = a.nDate and nName = a.nName and nType = 1
)
where ntype = 2
and not exists(
select 1 from Sep
where nid = a.nid and nDate = a.nDate and nName = a.nName and nType = 1
)
inner join
(
select * from sep
where nType = 2
) s2 on s1.nID = s2.nID and s1.nDate = s2.nDate
xluzhong(打麻将一缺三,咋办?)
nid = a.nid 好像不需要加这个吧?JafyLiu(土豆) :好像不符合题意!
(
aa money
)
declare @a varchar(100)
set @a='12.123'
insert into #test select cast(@a as money)select * from #test
drop table #testcreate table #test
(
nID int,
nDate datetime,
nTime varchar(8),
nName varchar(100),
nType int
)insert into #test
select 1 , '2001-1-1' , '12:00:00' , '11' , 1 union all
select 2 , '2001-1-1' , '12:00:00' , '11' , 2 union all
select 3 , '2001-1-1' , '12:00:00' , '22' , 1 union all
select 4 , '2001-1-1' , '12:00:00' , '33' , 2 union all
select 5 , '2001-1-1' , '12:00:00' , '33' , 2 union all
select 6 , '2001-1-1' , '12:00:00' , '44' , 2 union all
select 7 , '2001-1-2' , '12:00:00' , '11' , 2
select * from #test where nID not in
(
select nID from #test a where (select count(distinct nType) from #test where nDate=a.nDate and nName=a.nName )=2
or
(
(select count(distinct nType) from #test where nDate=a.nDate and nName=a.nName )=1 and nType='1'
)
)
4 2001-01-01 00:00:00.000 12:00:00 33 2
5 2001-01-01 00:00:00.000 12:00:00 33 2
6 2001-01-01 00:00:00.000 12:00:00 44 2
7 2001-01-02 00:00:00.000 12:00:00 11 2
declare @tb table(nID int,nDate varchar(10),nTime varchar(10),nName int,nType int)
insert into @tb
select 1, '2001-1-1', '12:00:00', 11, 1 union all
select 2, '2001-1-1', '12:00:00', 11, 2 union all
select 3, '2001-1-1', '12:00:00', 22, 1 union all
select 4, '2001-1-1', '12:00:00', 33, 2 union all
select 5, '2001-1-1', '12:00:00', 33, 2 union all
select 6, '2001-1-1', '12:00:00', 44, 2 union all
select 7, '2001-1-2', '12:00:00', 11, 2
select t.nID,u.ndate,t.ntime,u.nname,t.ntype from @tb t right join (
select b.ndate,b.nname,flag=case when a.ntype=1 then 0 else 1 end from (select ndate,nname,ntype from @tb where ntype=2 group by ndate,nname,ntype)b left join (select ndate,nname,ntype='1' from @tb where ntype=1 group by ndate,nname)a on b.ndate=a.ndate and b.nname=a.nname
)u
on t.ndate=u.ndate and t.nname=u.nname where u.flag=1
----------- ---------- ---------- ----------- -----------
4 2001-1-1 12:00:00 33 2
5 2001-1-1 12:00:00 33 2
6 2001-1-1 12:00:00 44 2
7 2001-1-2 12:00:00 11 2