比如说:
表 carysalesid lotno type
0001 rc001 c01
0002 dc001 d01
0003 ct001 t01
0001 rc001 dt1
... ... ...第一种:求出结果:
salesid lotno type
0001 rc001 c01
0001 rc001 dt1
... ... ...也就是说求出salesid lotno 相同内容的记录。第二种:
salesid lotno count
0001 rc001 2
... ... ...
也就是求出相同记录的统计
表 carysalesid lotno type
0001 rc001 c01
0002 dc001 d01
0003 ct001 t01
0001 rc001 dt1
... ... ...第一种:求出结果:
salesid lotno type
0001 rc001 c01
0001 rc001 dt1
... ... ...也就是说求出salesid lotno 相同内容的记录。第二种:
salesid lotno count
0001 rc001 2
... ... ...
也就是求出相同记录的统计
FROM cary C
WHERE EXISTS(SELECT 1 FROM cary WHERE salesid =C.salesid AND
lotno =C.lotno AND type <>T.type )SELECT salesid , lotno ,COUNT(*)
FROM cary C
WHERE EXISTS(SELECT 1 FROM cary WHERE salesid =C.salesid AND
lotno =C.lotno AND type <>T.type )
GROUP BY salesid , lotno
select
*
from
tb t
where
exists(select 1 from tb where salesid=t.salesid and lotno=t.lotno and [type]<>t.type)
salesid ,lotno,count(1) as [count]
from
tb t
where
exists(select 1 from tb where salesid=t.salesid and lotno=t.lotno and [type]<>t.type)
group by
salesid ,lotno
FROM CARY T1 INNER JOIN T2 ON T1.SALESID=T2.SALESID
T1.LOTNO=T2.LOTNO AND T1.TYPE<>T2.TYPE
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-07 09:27:12
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (salesid varchar(4),lotno varchar(5),type varchar(3))
insert into @tb
select '0001','rc001','c01' union all
select '0002','dc001','d01' union all
select '0003','ct001','t01' union all
select '0001','rc001','dt1'--1
select * from @tb t
where (select count(*) from @tb where salesid=t.salesid and lotno=t.lotno)>1/*
salesid lotno type
------- ----- ----
0001 rc001 c01
0001 rc001 dt1(2 行受影响)*/--2
select salesid,lotno,[count]=count(*)
from @tb
group by salesid,lotno
having(count(*)>1)
salesid lotno count
------- ----- -----------
0001 rc001 2(1 行受影响)
FROM CARY T1 INNER JOIN T2 ON T1.SALESID=T2.SALESID
T1.LOTNO=T2.LOTNO AND T1.TYPE<>T2.TYPE
SELECT T1.salesid, T1.lotno,count(type)
FROM CARY T1 INNER JOIN T2 ON T1.SALESID=T2.SALESID
T1.LOTNO=T2.LOTNO AND T1.TYPE<>T2.TYPE
GROUP BY T1.salesid, T1.lotno
select t.* from cary t where exists (select 1 from (select salesid , lotno from cary group by salesid , lotno having count(1) > 1) m where m.salesid = t.salesid and m.lotno = t.lotno)--2
select salesid , lotno , count(1) [count] from cary group by salesid , lotno having count(1) > 1
insert into cary
select '0001','rc001','c01' union all
select '0002','dc001','d01' union all
select '0003','ct001','t01' union all
select '0001','rc001','dt1' --1
select t.* from cary t where exists (select 1 from (select salesid , lotno from cary group by salesid , lotno having count(1) > 1) m where m.salesid = t.salesid and m.lotno = t.lotno)
/*
salesid lotno type
------- ----- ----
0001 rc001 c01
0001 rc001 dt1(所影响的行数为 2 行)
*/--2
select salesid , lotno , count(1) [count] from cary group by salesid , lotno having count(1) > 1
/*
salesid lotno count
------- ----- -----------
0001 rc001 2(所影响的行数为 1 行)
*/drop table cary
declare @table table([salesid] varchar(4),[lotno] varchar(5),[type] varchar(3))
insert @table
select '0001','rc001','c01' union all
select '0002','dc001','d01' union all
select '0003','ct001','t01' union all
select '0001','rc001','dt1'--1
select r.* from @table r join @table t
on r.[salesid] = t.[salesid] and r.[lotno] = t.[lotno]
and r.type <> t.type
---------------
0001 rc001 c01
0001 rc001 dt1--2
select [salesid] ,[lotno] ,count(1) as [count]
from @table
group by [salesid] ,[lotno]
having count(1)>=2
----------------
0001 rc001 2