100分, 找出自身重复的行.

[poloyzhang]

  use TempTest 
  go   
  if OBJECT_ID ('Tb1') is not null drop table Tb1 
  Create Table Tb1  (
  colKey varchar(10) not null primary key ,
  col1 varchar(10) null,
  col2 varchar (10)  null
  ,col3 varchar (10)  null
  )
go


Insert into Tb1 
Select  'key1' , 'aa1' , 'C' , 'D'  union all 
Select  'key2' , 'aa2' , 'C' , 'D'  union all 
Select  'key3' , 'aa3' , 'D' , 'D'  union all 
Select  'key4' , 'aa4' , 'D' , 'D'  union all 
Select  'key5' , 'aa5' , 'C' , 'C'  union all 
Select  'key6' , 'aa6' , 'D' , 'C'  union all 
Select  'key7' , 'aa3' , 'D' , 'D'  union all 
Select  'key8' , 'aa4' , 'D' , 'C'  union all 
Select  'key9' , 'aa3' , 'D' , 'C'  union all 
Select  'key10' , 'aa10' , 'C' , 'D'  union all 
Select  'key11' , 'aa3' , 'D' , 'C'  union all 
Select  'key12' , 'aa12' , 'C' , 'D' 



SELECT * FROM [TempTest].[dbo].[Tb1] order by col1,col2,col3

--要求找出 col,col2 值相同 并且 col3 值为 'D' 的重复行

[xdashewan]

select a.* from tb1 a inner join (
Select  col1, col2, col3, count(0) from tb1
group by col1, col2, col3
having count(0)  > 1)  b on a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3

[xdashewan]

select a.* from tb1 a inner join (
Select  col1, col2, col3, count(0) from tb1
where  col3 = 'D'
group by col1, col2, col3
having count(0)  > 1)  b on a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3

忘记加D条件了

[poloyzhang]

消息 8155，级别 16，状态 2，第 7 行 没有为 'b' 的列 4 指定任何列名称。

[lzw_0736]

 
WITH a1 AS
( 
SELECT *,ROW_NUMBER()  OVER(PARTITION BY col1,col2 ORDER BY GETDATE()) re
FROM #Tb1 
WHERE col3='D'
)
,a2 AS
(
SELECT col1,col2
FROM a1
GROUP BY col1,col2
HAVING MAX(re)>1
)
SELECT a.*
FROM a1 a
JOIN a2 b ON a.col1=b.col1 AND a.col2=b.col2
ORDER BY col1,col2,col3

[lzw_0736]

4樓第5行改為 from Tb1

[lzw_0736]

引用 3 楼 poloyzhang 的回复:

消息 8155，级别 16，状态 2，第 7 行 没有为 'b' 的列 4 指定任何列名称。

改為以下就行了： select a.* from tb1 a inner join ( Select col1, col2, col3, count(0) sl from tb1 where col3 = 'D' group by col1, col2, col3 having count(0) > 1) b on a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3

[xdashewan]

引用 3 楼 poloyzhang 的回复:

消息 8155，级别 16，状态 2，第 7 行 没有为 'b' 的列 4 指定任何列名称。

我晕，给count(0)随便AS个名称不就好了

[poloyzhang]

上面能否用在 C#编程中? 作为 数据源 datatable 给 dgv ?

[--小F--]

if OBJECT_ID ('Tb1') is not null drop table Tb1 
  Create Table Tb1  (
  colKey varchar(10) not null primary key ,
  col1 varchar(10) null,
  col2 varchar (10)  null
  ,col3 varchar (10)  null
  )
go


Insert into Tb1 
Select  'key1' , 'aa1' , 'C' , 'D'  union all 
Select  'key2' , 'aa2' , 'C' , 'D'  union all 
Select  'key3' , 'aa3' , 'D' , 'D'  union all 
Select  'key4' , 'aa4' , 'D' , 'D'  union all 
Select  'key5' , 'aa5' , 'C' , 'C'  union all 
Select  'key6' , 'aa6' , 'D' , 'C'  union all 
Select  'key7' , 'aa3' , 'D' , 'D'  union all 
Select  'key8' , 'aa4' , 'D' , 'C'  union all 
Select  'key9' , 'aa3' , 'D' , 'C'  union all 
Select  'key10' , 'aa10' , 'C' , 'D'  union all 
Select  'key11' , 'aa3' , 'D' , 'C'  union all 
Select  'key12' , 'aa12' , 'C' , 'D' 



--SELECT * FROM [Tb1] order by col1,col2,col3

--要求找出 col,col2 值相同 并且 col3 值为 'D' 的重复行

SELECT *  FROM TB1 AS T WHERE  EXISTS(SELECT 1 FROM TB1 WHERE COL1=T.COL1 AND COL2=T.COL2  AND colKey<>T.colKey) AND COL3='D'

/*colKey     col1       col2       col3
---------- ---------- ---------- ----------
key3       aa3        D          D
key4       aa4        D          D
key7       aa3        D          D

(3 行受影响)*/

是这样吗？

[reenjie]

試一下下面的sql

with cte as (
select col1,col2,col3 from (
select *,ROW_NUMBER() over(partition by col1,col2 order by colkey) as qty from Tb1 where col3='D' 
) a where qty>1 group by col1,col2,col3
)
select * from Tb1 where col1 in(select col1 from cte) and col2 in(select col2 from cte) and col3='D'

[--小F--]

if OBJECT_ID ('Tb1') is not null drop table Tb1 
  Create Table Tb1  (
  colKey varchar(10) not null primary key ,
  col1 varchar(10) null,
  col2 varchar (10)  null
  ,col3 varchar (10)  null
  )
go


Insert into Tb1 
Select  'key1' , 'aa1' , 'C' , 'D'  union all 
Select  'key2' , 'aa2' , 'C' , 'D'  union all 
Select  'key3' , 'aa3' , 'D' , 'D'  union all 
Select  'key4' , 'aa4' , 'D' , 'D'  union all 
Select  'key5' , 'aa5' , 'C' , 'C'  union all 
Select  'key6' , 'aa6' , 'D' , 'C'  union all 
Select  'key7' , 'aa3' , 'D' , 'D'  union all 
Select  'key8' , 'aa4' , 'D' , 'C'  union all 
Select  'key9' , 'aa3' , 'D' , 'C'  union all 
Select  'key10' , 'aa10' , 'C' , 'D'  union all 
Select  'key11' , 'aa3' , 'D' , 'C'  union all 
Select  'key12' , 'aa12' , 'C' , 'D' 



--SELECT * FROM [Tb1] order by col1,col2,col3

--要求找出 col,col2 值相同 并且 col3 值为 'D' 的重复行

SELECT *  FROM TB1 AS T WHERE  EXISTS(SELECT 1 FROM TB1 WHERE COL1=T.COL1 AND COL2=T.COL2  AND colKey<>T.colKey AND COL3='D') AND COL3='D'

/*colKey     col1       col2       col3
---------- ---------- ---------- ----------
key3       aa3        D          D
key7       aa3        D          D

(2 行受影响)
*/

[还在加载中灬]

引用 8 楼 poloyzhang 的回复:

上面能否用在 C#编程中? 作为 数据源 datatable 给 dgv ?

可以的，是出了什么问题吗？

SELECT * FROM(
	SELECT *,COUNT(1)OVER(PARTITION BY col1)C FROM tb1
	WHERE col3='D'
)T WHERE C>=2

[poloyzhang]

收到 ,快速结帐给分.

[reenjie]

再提供一種方法

select * from Tb1 as a where exists(select 1 from Tb1 where col1=a.col1 and col2=a.col2 and col3=a.col3 and col3='D' 
group by col1,col2 having COUNT(*)>1)

[还在加载中灬]

SELECT * FROM(
	SELECT *,COUNT(1)OVER(PARTITION BY col1,col2)C FROM tb1
	WHERE col3='D'
)T WHERE C>=2