高难度啊!!!请问怎么样把相同数据一起统计、合并显示?
company与name相同的数据出现的次数,并一起显示最新的数据,按次数多少进行排序,以company与name排序id company name Product Mark date
1 A YY 1 H 2008-11-3
2 A HFF 2 Y 2008-11-3
3 B JGR 1 H 2008-11-5
4 C NYJ 3 U 2008-11-5
5 B NRM 1 O 2008-11-6
6 A YY 2 P 2008-11-7
7 A YY 2 Y 2008-11-8
8 B JGR 1 Y 2008-11-8上表的统计结果应为
id company name Product Mark date count
7 A YY 2 Y 2008-11-8 3
8 B JGR 1 Y 2008-11-8 2
2 A HFF 2 Y 2008-11-3 1
3 B JGR 1 H 2008-11-5 1
4 C NYJ 3 U 2008-11-5 1
即,每A公司的YY职员一共来了3次,显示最后日期的记录+次数。
麻烦大家帮帮忙,非常感谢!!
company与name相同的数据出现的次数,并一起显示最新的数据,按次数多少进行排序,以company与name排序id company name Product Mark date
1 A YY 1 H 2008-11-3
2 A HFF 2 Y 2008-11-3
3 B JGR 1 H 2008-11-5
4 C NYJ 3 U 2008-11-5
5 B NRM 1 O 2008-11-6
6 A YY 2 P 2008-11-7
7 A YY 2 Y 2008-11-8
8 B JGR 1 Y 2008-11-8上表的统计结果应为
id company name Product Mark date count
7 A YY 2 Y 2008-11-8 3
8 B JGR 1 Y 2008-11-8 2
2 A HFF 2 Y 2008-11-3 1
3 B JGR 1 H 2008-11-5 1
4 C NYJ 3 U 2008-11-5 1
即,每A公司的YY职员一共来了3次,显示最后日期的记录+次数。
麻烦大家帮帮忙,非常感谢!!
*,
b.cnt as [count]
from a
left join
(select company,name,count(1) as cnt from tb group by company,name) b
on a.company=b.company and a.name=b.name
order by b.cnt desc
*,
b.cnt as [count]
from tb a
left join
(select company,name,count(1) as cnt from tb group by company,name) b
on a.company=b.company and a.name=b.name
order by b.cnt desc
1 1 A YY 1 H 2008-11-3 A YY 3 3
2 6 A YY 2 P 2008-11-7 A YY 3 3
3 7 A YY 2 Y 2008-11-8 A YY 3 3
4 8 B JGR 1 Y 2008-11-8 B JGR 2 2
5 3 B JGR 1 H 2008-11-5 B JGR 2 2
6 4 C NYJ 2 U 2008-11-5 C NYJ 1 1
7 2 A HFF 2 Y 2008-11-3 A HFF 1 1
8 5 B NRM 1 O 2008-11-6 B NRM 1 1
insert @t select 1,'A','YY',1,'H','2008-11-3'
insert @t select 2,'A','HFF',2,'Y','2008-11-3'
insert @t select 3,'B','JGR',1,'H','2008-11-5'
insert @t select 4,'C','NYJ',3,'U','2008-11-5'
insert @t select 5,'B','NRM',1,'O','2008-11-6'
insert @t select 6,'A','YY',2,'P','2008-11-7'
insert @t select 7,'A','YY',2,'Y','2008-11-8'
insert @t select 8,'B','JGR',1,'Y','2008-11-8'select
*,
[count]=(select count(1) from @t where company=t.company and name=t.name)
from @t t
where not exists(
select 1
from @t
where company=t.company and name=t.name and date>t.date)
/*
id company name Product Mark date count
----------- ---------- ---------- ----------- ---------- ----------------------- -----------
2 A HFF 2 Y 2008-11-03 00:00:00.000 1
4 C NYJ 3 U 2008-11-05 00:00:00.000 1
5 B NRM 1 O 2008-11-06 00:00:00.000 1
7 A YY 2 Y 2008-11-08 00:00:00.000 3
8 B JGR 1 Y 2008-11-08 00:00:00.000 2(5 行受影响)
*/
company与name相同的数据出现的次数,并一起显示最新的数据,按次数多少进行排序,以company与name排序id company name Product Mark date
1 A YY 1 H 2008-11-3
2 A HFF 2 Y 2008-11-3
3 B JGR 1 H 2008-11-5
4 C NYJ 3 U 2008-11-5
5 B NRM 1 O 2008-11-6
6 A YY 2 P 2008-11-7
7 A YY 2 Y 2008-11-8
8 B JGR 1 Y 2008-11-8上表的统计结果应为
id company name Product Mark date count
7 A YY 2 Y 2008-11-8 3
8 B JGR 1 Y 2008-11-8 2
2 A HFF 2 Y 2008-11-3 1
3 B JGR 1 H 2008-11-5 1
4 C NYJ 3 U 2008-11-5 1即,每A公司的YY职员一共来了3次,显示最后日期的记录+次数。麻烦大家帮帮忙,非常感谢!! */create table #t
(id int identity, company varchar(20), [name] varchar(20),Product varchar(20), Mark varchar(20), date varchar(20))
insert into #t
select 'A','YY','1','H','2008-11-3' union all
select 'A','HFF','2','Y','2008-11-3' union all
select 'B','JGR','1','H','2008-11-5' union all
select 'C','NYJ','3','U','2008-11-5' union all
select 'B','NRM','1','O','2008-11-6' union all
select 'A','YY','2','P','2008-11-7' union all
select 'A','YY','2','Y','2008-11-8' union all
select 'B','JGR','1','Y','2008-11-8'select a.*,b.[count]
from #t a
join (select max(id) id,company,[name],count(1) [count] from #t group by company,[name]) b
on a.id=b.id and a.company=b.company and a.[name]=b.[name]
order by 7 descid company name Product Mark date count
7 A YY 2 Y 2008-11-8 3
8 B JGR 1 Y 2008-11-8 2
5 B NRM 1 O 2008-11-6 1
4 C NYJ 3 U 2008-11-5 1
2 A HFF 2 Y 2008-11-3 1
楼主给的结果是不是有问题?
上表的统计结果应为
id company name Product Mark date count
7 A YY 2 Y 2008-11-8 3
8 B JGR 1 Y 2008-11-8 2
2 A HFF 2 Y 2008-11-3 1
3 B JGR 1 H 2008-11-5 1 --存在吗?
4 C NYJ 3 U 2008-11-5 1
-->生成测试数据
declare @t table(id int,company varchar(10), name varchar(10), Product int, Mark varchar(10),date datetime)
insert @t select 1,'A','YY',1,'H','2008-11-3'
insert @t select 2,'A','HFF',2,'Y','2008-11-3'
insert @t select 3,'B','JGR',1,'H','2008-11-5'
insert @t select 4,'C','NYJ',3,'U','2008-11-5'
insert @t select 5,'B','NRM',1,'O','2008-11-6'
insert @t select 6,'A','YY',2,'P','2008-11-7'
insert @t select 7,'A','YY',2,'Y','2008-11-8'
insert @t select 8,'B','JGR',1,'Y','2008-11-8'
-->开始查询
select
*,
[count]=(select count(1) from @t where company=t.company and name=t.name)
from @t t
where not exists(
select 1
from @t
where company=t.company and name=t.name and date>t.date)
order by [count] desc-->结果
/*
id company name Product Mark date count
----------- ---------- ---------- ----------- ---------- ----------------------- -----------
7 A YY 2 Y 2008-11-08 00:00:00.000 3
8 B JGR 1 Y 2008-11-08 00:00:00.000 2
2 A HFF 2 Y 2008-11-03 00:00:00.000 1
4 C NYJ 3 U 2008-11-05 00:00:00.000 1
5 B NRM 1 O 2008-11-06 00:00:00.000 1(5 行受影响)
*/补充排序
if object_id('test') is not null drop table test
create table test(id int,company char,name varchar(3),product int, char,date varchar(20))
insert into test select 1,'A','YY',1,'H','2008-11-3' union all
select 2,'A','HFF',2,'Y','2008-11-3' union all
select 3,'B','JGR',1,'H','2008-11-5' union all
select 4,'C','NYJ',3,'U','2008-11-5' union all
select 5,'B','NRM',1,'O','2008-11-6' union all
select 6,'A','YY',2,'P','2008-11-7' union all
select 7,'A','YY',2,'Y','2008-11-8' union all
select 8,'B','JGR',1,'Y','2008-11-8'
--
select a.* ,b.count
from test a
join (select company,name,count(*) count from test group by company,name) b
on a.company=b.company and a.name=b.name
where not exists(select 1 from test where company=a.company and name=a.name and date>a.date)
order by b.count desc
--结果
7 A YY 2 Y 2008-11-8 3
8 B JGR 1 Y 2008-11-8 2
2 A HFF 2 Y 2008-11-3 1
4 C NYJ 3 U 2008-11-5 1
5 B NRM 1 O 2008-11-6 1
理论上表扫描次数越少越好。join的确是扫描一次,而7楼扫描3次。
同时执行两个语句时join查询开销大,
分开执行查询开销都是100%,再看join排序开销占77%。
抽时间再用大量数据分析。
select * from tbale1 where id=1
select * from table1 where id=1
CREATE TABLE TABLETEST(id INT,company CHAR(5), name CHAR(5), Product INT, Mark CHAR(5), date DATETIME)
INSERT INTO TABLETEST
SELECT 1,'A', 'YY', 1,'H', '2008-11-3' UNION
SELECT 2,'A', 'HFF', 2,'Y' ,'2008-11-3' UNION
SELECT 3, 'B','JGR', 1, 'H', '2008-11-5' UNION
SELECT 4, 'C', 'NYJ', 3 ,'U', '2008-11-5' UNION
SELECT 5, 'B', 'NRM', 1 ,'O', '2008-11-6' UNION
SELECT 6, 'A', 'YY', 2 ,'P', '2008-11-7 'UNION
SELECT 7, 'A' ,'YY' ,2 ,'Y', '2008-11-8 'UNION
SELECT 8, 'B', 'JGR', 1 ,'Y', '2008-11-8 'SELECT ID,T.COMPANY,T.NAME,T1. DATE ,次数 FROM TABLETEST TAB,(select company,name,COUNT(NAME) 次数 from tabletest group by company,name ) T,(SELECT MAX(DATE) DATE,NAME FROM TABLETEST GROUP BY NAME) T1WHERE TAB.COMPANY=T.COMPANY AND TAB.NAME=T.NAME
AND TAB.DATE=T1.DATE AND T.NAME=T1.NAME GROUP BY ID,T.COMPANY,T.NAME,T.次数,T1.DATEORDER BY 次数 DESC7 A YY 2008-11-08 00:00:00.000 3
8 B JGR 2008-11-08 00:00:00.000 2
2 A HFF 2008-11-03 00:00:00.000 1
4 C NYJ 2008-11-05 00:00:00.000 1
5 B NRM 2008-11-06 00:00:00.000 1