select cd=1,No1='11',No2='a' into #1 union all
select cd=2,No1='22',No2='b' union all
select cd=3,No1='33',No2='c' union all
select cd=4,No1='44',No2='d' union all
select cd=4,No1='44',No2='d' union all
select cd=4,No1='44',No2='d'
go
select * from #1select No2='a',kbn='x' into #2 union all
select No2='b',kbn='y' union all
select No2='c',kbn='z' union all
select No2='d',kbn='h' union all
select No2='d',kbn='m'
go
select * from #2--想得到的是
No1 cnt
11 1
22 1
33 1
44 3
44 3
44 3
select cd=2,No1='22',No2='b' union all
select cd=3,No1='33',No2='c' union all
select cd=4,No1='44',No2='d' union all
select cd=4,No1='44',No2='d' union all
select cd=4,No1='44',No2='d'
go
select * from #1
select no1,COUNT(no1)over(partition by no1) as count from #1
-----------------
no1 count
11 1
22 1
33 1
44 3
44 3
44 3
--sql 2005没用到#2
select cd=1,No1='11',No2='a' into #1 union all
select cd=2,No1='22',No2='b' union all
select cd=3,No1='33',No2='c' union all
select cd=4,No1='44',No2='d' union all
select cd=4,No1='44',No2='d' union all
select cd=4,No1='44',No2='d'
go
select * from #1select t1.No1,cnt
from t1 left join(
select No1,count(No2)cnt
from t1
group by No1)U
on t1.No1 = U.No1No1 cnt
----------- -----------
11 1
22 1
33 1
44 3
44 3
44 3(6 行受影响)
一着急,问题让我描述简单了。对不起大家哈,我重新说一遍select cd=1,No1='11',No2='a' into #1 union all
select cd=2,No1='22',No2='b' union all
select cd=3,No1='33',No2='c' union all
select cd=3,No1='33',No2='c' union all
select cd=4,No1='44',No2='c' union all
select cd=4,No1='44',No2='d' union all
select cd=4,No1='44',No2='d'
goselect No2='a',kbn='1' into #2 union all
select No2='b',kbn='1' union all
select No2='c',kbn='1' union all
select No2='c',kbn='9' union all
select No2='d',kbn='1' union all
select No2='d',kbn='1'
go
--想得到的数据是
No1 cnt
11 1
22 1
44 2
44 2
select cd=1,No1='11',No2='a' into #1 union all
select cd=2,No1='22',No2='b' union all
select cd=3,No1='33',No2='c' union all
select cd=3,No1='33',No2='c' union all
select cd=4,No1='44',No2='c' union all
select cd=4,No1='44',No2='d' union all
select cd=4,No1='44',No2='d'
goselect No2='a',kbn='1' into #2 union all
select No2='b',kbn='1' union all
select No2='c',kbn='1' union all
select No2='c',kbn='9' union all
select No2='d',kbn='1' union all
select No2='d',kbn='1'
goselect No1,count(No2) over (partition by No2) cnt
from t1
where No2 not in (select No2 from t2 where kbn <> '1')No1 cnt
----------- -----------
11 1
22 1
44 2
44 2(4 行受影响)
FROM #1 a
INNER JOIN
(SELECT No2, COUNT(1) AS cnt FROM #2 GROUP BY No2) b
ON a.No2 = b.No2
WHERE a.No2 NOT IN (SELECT No2 FROM #2 WHERE kbn <> 1)
select cd=2,No1='22',No2='b' union all
select cd=3,No1='33',No2='c' union all
select cd=3,No1='33',No2='c' union all
select cd=4,No1='44',No2='c' union all
select cd=4,No1='44',No2='d' union all
select cd=4,No1='44',No2='d'
goselect No2='a',kbn='1' into #2 union all
select No2='b',kbn='1' union all
select No2='c',kbn='1' union all
select No2='c',kbn='9' union all
select No2='d',kbn='1' union all
select No2='d',kbn='1'
go
--想得到的数据是
No1 cnt
11 1
22 1
44 2
44 2
select cd,cnt=(select COUNT(1) from #1 where cd=t.cd
and No2 not in (select No2 from #2 where kbn<>1) ) from #1 t
where No2 not in (select No2 from #2 where kbn<>1)
select t1.No1,cnt
from t1 right join(
select No1,No2,isnull(count(No2),0)cnt
from t1
where No2 not in (select No2 from t2 where kbn <> '1')
group by No1,No2)U
on t1.No1 = U.No1 and t1.No2 = U.no2No1 cnt
----------- -----------
11 1
22 1
44 2
44 2(4 行受影响)