table.qybm' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。 这是怎么回事呀!
select * from tablename where zsbh in (select zsbh from tablename group by zsbh having count(*)>1)
select zsbh,...,count(zsbh) from tablename where zsbh in (select zsbh from tablename group by zsbh,... having count(zsbh)>1)
如果只要zsbh 字段,則 select zsbh from tablename group by zsbh having count(*)>1 如果需要所有字段,則 select * from tablename where zsbh in(select zsbh from tablename group by zsbh having count(*)>1)
--先建立个functionCREATE PROCEDURE FindDistinct @tbl_name varchar(100), @col_name varchar(100),@i varchar(100) AS EXECUTE ('SELECT * FROM ' +@tbl_name+ ' a,(SELECT ' +@col_name+ ' FROM ' +@tbl_name+ ' GROUP BY ' +@col_name+ ' HAVING (COUNT(' +@col_name+ ') > '+@i+')) b where a.' +@col_name+ '= b.' +@col_name) GO --然后执行 FindDistinct '表名', 'ZSBH',2
这是怎么回事呀!
from tablename
group by zsbh
having count(*)>1)
from tablename
group by zsbh,...
having count(zsbh)>1)
select zsbh from tablename group by zsbh having count(*)>1
如果需要所有字段,則
select * from tablename
where zsbh in(select zsbh from tablename group by zsbh having count(*)>1)
EXECUTE ('SELECT * FROM '
+@tbl_name+
' a,(SELECT '
+@col_name+
' FROM '
+@tbl_name+
' GROUP BY '
+@col_name+
' HAVING (COUNT('
+@col_name+
') > '+@i+')) b where a.'
+@col_name+
'= b.'
+@col_name)
GO
--然后执行
FindDistinct '表名', 'ZSBH',2