select * from abc inner join (select (name+convert(varchar(10),brith)) a, count(1) b) from abc ) abc2 on (name+convert(varchar(10),brith)) = abc2.b and abc2.b > 1
多了一个 )改成select * from abc inner join (select (name+convert(varchar(10),brith)) a, count(1) b from abc) abc2 on (name+convert(varchar(10),brith)) = abc2.b and abc2.b > 1未测,你试试,应该OK
再改,呵呵~~select * from abc inner join (select (name+convert(varchar(10),brith,120)) a, count(1) b from abc) abc2 on (name+convert(varchar(10),brith,120)) = abc2.b and abc2.b > 1
select * from 表名 a join ( select name,birth from 表名 group by name,birth having count(*)>1) b on a.name=b.XH and a.birth=b.birth order by a.name,a.birth
手誤更改一下:(把上面的b.XH修改為b.name)...select * from 表名 a join ( select name,birth from 表名 group by name,birth having count(*)>1) b on a.name=b.name and a.birth=b.birth order by a.name,a.birth
測試如下:1: select A.name,A.birth, FROM 表名 A,表名 B WHERE A.name=B.name AND A.birth=B.birth GROUP BY A.name,A.birth HAVING COUNT(*)>1 2: select name,birth from 表名 where name in (select distinct name from 表名) and birth in (select distinct birth from 表名) 3: select a.name,a.birth from 表名 a join ( select name,birth from 表名 group by name,birth having count(*)>1) b on a.name=b.name and a.birth=b.birth order by a.name,a.birth 測試結果:USE [eHR] GO -- ============================================= /* 作者:作者:天涯浪子一居士(有志者事竟成破釜沉舟百二秦关终属楚;苦心人天不负卧薪尝胆三千越甲可吞吴!) 时间:2010-08-16 地点:广东東莞 公司:昆盈電腦製品有限公司 功能:Test */ -- =============================================
SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOCREATE TABLE [dbo].[Test_TB]( [name] [nvarchar](50) NULL, [birth] [datetime] NULL ) ON [PRIMARY]GO /* insert into TEST_TB (name,birth) values ('張三','2010/08/16') insert into TEST_TB (name,birth) values ('張三','2010/08/16') insert into TEST_TB (name,birth) values ('李四','2010/08/15') insert into TEST_TB (name,birth) values ('張三','2010/08/16') insert into TEST_TB (name,birth) values ('王五','2010/08/17') insert into TEST_TB (name,birth) values ('趙六','2010/08/14')
select * from TEST_TB*/select a.name,a.birth from TEST_TB a join ( select name,birth from TEST_TB group by name,birth having count(*)>1) b on a.name=b.name and a.birth=b.birth order by a.name,a.birth 返回結果:name birth -------------------------------------------------- ----------------------- 張三 2010-08-16 00:00:00.000 張三 2010-08-16 00:00:00.000 張三 2010-08-16 00:00:00.000(3 個資料列受到影響)drop table TEST_TB
inner join (select (name+convert(varchar(10),brith)) a, count(1) b) from abc ) abc2 on
(name+convert(varchar(10),brith)) = abc2.b and abc2.b > 1
inner join (select (name+convert(varchar(10),brith)) a, count(1) b from abc) abc2 on
(name+convert(varchar(10),brith)) = abc2.b and abc2.b > 1未测,你试试,应该OK
inner join (select (name+convert(varchar(10),brith,120)) a, count(1) b from abc) abc2 on
(name+convert(varchar(10),brith,120)) = abc2.b and abc2.b > 1
select name,birth from 表名 group by name,birth
having count(*)>1) b on a.name=b.XH and a.birth=b.birth order by
a.name,a.birth
手誤更改一下:(把上面的b.XH修改為b.name)...select * from 表名 a join (
select name,birth from 表名 group by name,birth
having count(*)>1) b on a.name=b.name and a.birth=b.birth order by
a.name,a.birth
select A.name,A.birth, FROM 表名 A,表名 B
WHERE A.name=B.name AND A.birth=B.birth
GROUP BY A.name,A.birth
HAVING COUNT(*)>1 2:
select name,birth from 表名 where name in (select distinct name from 表名) and birth in (select distinct birth from 表名) 3:
select a.name,a.birth from 表名 a join (
select name,birth from 表名 group by name,birth
having count(*)>1) b on a.name=b.name and a.birth=b.birth order by
a.name,a.birth
測試結果:USE [eHR]
GO -- =============================================
/*
作者:作者:天涯浪子一居士(有志者事竟成破釜沉舟百二秦关终属楚;苦心人天不负卧薪尝胆三千越甲可吞吴!)
时间:2010-08-16
地点:广东東莞
公司:昆盈電腦製品有限公司
功能:Test
*/
-- =============================================
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Test_TB](
[name] [nvarchar](50) NULL,
[birth] [datetime] NULL
) ON [PRIMARY]GO
/* insert into TEST_TB (name,birth) values ('張三','2010/08/16')
insert into TEST_TB (name,birth) values ('張三','2010/08/16')
insert into TEST_TB (name,birth) values ('李四','2010/08/15')
insert into TEST_TB (name,birth) values ('張三','2010/08/16')
insert into TEST_TB (name,birth) values ('王五','2010/08/17')
insert into TEST_TB (name,birth) values ('趙六','2010/08/14')
select * from TEST_TB*/select a.name,a.birth from TEST_TB a join (
select name,birth from TEST_TB group by name,birth
having count(*)>1) b on a.name=b.name and a.birth=b.birth order by
a.name,a.birth 返回結果:name birth
-------------------------------------------------- -----------------------
張三 2010-08-16 00:00:00.000
張三 2010-08-16 00:00:00.000
張三 2010-08-16 00:00:00.000(3 個資料列受到影響)drop table TEST_TB