表A:
id,姓名,地点
1、A 上海
2、b 上海
3、c 浙江
4、a 上海
5、a 江苏
6、a 上海
7 b 江苏
8 c 北京
9 b 江苏
10 c 上海要求:输入同一人的最多出差地点(如果出差地点相同,则取id小的一个),这个结果是:
1、A 上海
2、b 江苏
3、c 浙江
id,姓名,地点
1、A 上海
2、b 上海
3、c 浙江
4、a 上海
5、a 江苏
6、a 上海
7 b 江苏
8 c 北京
9 b 江苏
10 c 上海要求:输入同一人的最多出差地点(如果出差地点相同,则取id小的一个),这个结果是:
1、A 上海
2、b 江苏
3、c 浙江
from ta a
where not exists(select 1 from ta where 姓名 = a.姓名 and id < a.id)
INTO #Tmp
FROM
(
SELECT 姓名,地点,COUNT(*) as Cnt
FROM TableA
GROUP BY 姓名,地点
) b
INNER JOIN TableA a
ON a.姓名=b.姓名 AND a.地点=b.地点SELECT * FROM #tmp a
WHERE NOT EXISTS(
SELECT * FROM #Tmp
WHERE 姓名=a.姓名 AND Cnt>a.Cnt
)DROP TABLE #tmp
INTO #Tmp
FROM
(
SELECT 姓名,地点,COUNT(*) as Cnt
FROM TableA
GROUP BY 姓名,地点
) b
INNER JOIN TableA a
ON a.姓名=b.姓名 AND a.地点=b.地点SELECT * FROM #tmp a
WHERE NOT EXISTS(
SELECT * FROM #Tmp
WHERE 姓名=a.姓名 AND Cnt>a.Cnt
)DROP TABLE #tmp
接楼上一用哈哈
id int,
姓名 varchar(10),
地点 varchar(10)
)
insert @t select
1,'a','上海'
union all select
2,'b','上海'
union all select
3,'c','浙江'
union all select
4,'a','上海'
union all select
5,'a','江苏'
union all select
6,'a','上海'
union all select
7,'b','江苏'
union all select
8,'c','北京'
union all select
9,'b','江苏'
union all select
10,'c','上海' SELECT a.ID,a.姓名,a.地点
FROM
(
SELECT min(id) as id,姓名,地点,COUNT(*) as Cnt
FROM @t
GROUP BY 姓名,地点
) as a
left join
(
SELECT min(id) as id,姓名,地点,COUNT(*) as Cnt
FROM @t
GROUP BY 姓名,地点
) as b
ON a.姓名=b.姓名 AND a.地点<>b.地点 and (a.Cnt<b.Cnt or a.Cnt=b.Cnt and a.id>b.id)
where b.姓名 is null--结果(好像与搂主的有所不同,我的正确吧)ID 姓名 地点
----------- ---------- ----------
1 a 上海
7 b 江苏
3 c 浙江(所影响的行数为 3 行)
insert into tb values(1, 'A' , '上海')
insert into tb values(2, 'b' , '上海')
insert into tb values(3, 'c' , '浙江')
insert into tb values(4, 'a' , '上海')
insert into tb values(5, 'a' , '江苏')
insert into tb values(6, 'a' , '上海')
insert into tb values(7, 'b' , '江苏')
insert into tb values(8, 'c' , '北京')
insert into tb values(9, 'b' , '江苏')
insert into tb values(10, 'c' , '上海')
goselect t1.* from
(
select * from tb t where id = (select min(id) from tb where 姓名 = t.姓名)
) t1,
(
select m.* from
(
select 姓名,地点,min(id) id ,count(1) cnt from tb group by 姓名,地点
) m where not exists
(
select 1 from
(
select 姓名,地点,count(1) cnt from tb group by 姓名,地点
) n where 姓名 = m.姓名 and cnt > m.cnt
)
) t2
where t1.姓名 = t2.姓名 and t2.cnt = 1 and t1.地点 = t2.地点
union all
select t1.id , t2.姓名,t2.地点 from
(
select * from tb t where id = (select min(id) from tb where 姓名 = t.姓名)
) t1,
(
select m.* from
(
select 姓名,地点,min(id) id ,count(1) cnt from tb group by 姓名,地点
) m where not exists
(
select 1 from
(
select 姓名,地点,count(1) cnt from tb group by 姓名,地点
) n where 姓名 = m.姓名 and cnt > m.cnt
)
) t2
where t2.cnt > 1 and t1.姓名 = t2.姓名
order by t1.iddrop table tb/*
id 姓名 地点
----------- ---------- ----------
1 a 上海
2 b 江苏
3 c 浙江(所影响的行数为 3 行)
*/
declare @t table (
id int,
username varchar(10),
addr varchar(10)
)
insert @t select
1,'a','上海'
union all select
2,'b','上海'
union all select
3,'c','浙江'
union all select
4,'a','上海'
union all select
5,'a','江苏'
union all select
6,'a','上海'
union all select
7,'b','江苏'
union all select
8,'c','北京'
union all select
9,'b','江苏'
union all select
10,'c','上海'
select * from
(
select min(id) as id,username,addr,count(*) as count1 from @t
group by username,addr
)a
where id=(select top 1 id from (select min(id) as id,username,addr,count(*) as count1 from @t
group by username,addr) b where b.username=a.username order by count1 desc)
order by id