我现在有两张表:T1和T2
T1有2000个记录,字段:cell,para1
T2有10000个记录 字段:cell,date,time,para1,para2关联字段 T1.cell=T2.cell and T1.para1=T2.para1查询结果要保留T1的所有记录(2000个),也不能超过2000个(即cell,para1不重复),同时保留的字段为:
cell,para1,para2
如何实现?
T1有2000个记录,字段:cell,para1
T2有10000个记录 字段:cell,date,time,para1,para2关联字段 T1.cell=T2.cell and T1.para1=T2.para1查询结果要保留T1的所有记录(2000个),也不能超过2000个(即cell,para1不重复),同时保留的字段为:
cell,para1,para2
如何实现?
--表T1里数据在表T2里不重复!
select t1.*,t2.para2
from t1,t2
where t1.cell = t2.cell and t1.para1 = t2.para1
(
cell int,para1 int
)
insert into #t1
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4
create table #t2
(
cell int,para1 int,paral2 int
)
insert into #t2
select 1,1,1 union all
select 1,2,2 union all
select 1,3,3 union all
select 1,4,4select a.*,b.para2 from #t1 a
left join #t2 b
on a.cell=b.cell and a.para1=b.para1
(
cell int,para1 int
)
insert into #t1
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4
create table #t2
(
cell int,para1 int,paral2 int
)
insert into #t2
select 1,1,1 union all
select 1,2,2 union all
select 1,3,3 union all
select 1,4,4select a.*,b.para2 from #t1 a
left join #t2 b
on a.cell=b.cell and a.para1=b.para1
select distinct T1.cell,T1.para1,T2.para2
from T1 left join T2 on T1.cell = T2.cell and T1.para1=T2.para1
group by T1.cell,T1.para1,T2.para2我先自己测试一下..
cell,para1
10002,0.39
10003,0.23
10004,0.31
.
.
.
T2:
cell,date,time,para1,para2
10002,20110224,19,0.39,2001
10003,20110224,19,0.23,2002
10004,20110224,19,0.31,2003
10002,20110224,20,0.39,2004
10003,20110224,20,0.49,2005
10004,20110224,20,0.51,2006
10002,20110224,21,0.59,2007
10003,20110224,21,0.04,2008
10004,20110224,21,0.25,2009
.
.
.
这个时候出来的就有两个cell=10002,para1=0.39的
FROM T1 a
OUTER APPLY
(SELECT TOP(1) * FROM T2 WHERE cell = a.cell and para1 = a.para1) b
(
cell int,para1 int
)
insert into #t1
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4create table #t2
(
cell int,para1 int,para2 int
)
insert into #t2
select 1,1,1 union all
select 1,2,2 union all
select 1,3,3 union all
select 1,4,4 union all
select 1,1,1 union all
select 1,2,2 union all
select 1,3,3 union all
select 1,4,4 select a.cell,a.para1,b.para2
from #t1 a
left join #t2 b
on a.cell=b.cell and a.para1=b.para1
group by a.cell,a.para1,b.para2drop table #t2
drop table #t1
-----------------------
1 1 1
1 2 2
1 3 3
1 4 4但是我感觉这段代码怪怪的...
T2中的cell,para1可能有重复这时候查询出来的cell和para1就有重复,虽然加上para2后不会重复,但是不是我希望得到的
(
select t1.*,t2.para2,N=ROW_NUMBER()over(partition by t2.cell ,t2.para1 order by getdate())
from t1,t2
where t1.cell = t2.cell and t1.para1 = t2.para1
)
select * from cte t
where not exists(select * from cte where cel1=t.cel1 and para1=t.para1 and N<t.N )
if object_id('tempdb.dbo.#T1') is not null drop table #T1
go
create table #T1 (cell int,para1 numeric(3,2))
insert into #T1
select 10002,0.39 union all
select 10003,0.23 union all
select 10004,0.31
--> 测试数据: #T2
if object_id('tempdb.dbo.#T2') is not null drop table #T2
go
create table #T2 (cell int,date datetime,time int,para1 numeric(3,2),para2 int)
insert into #T2
select 10002,'20110224',19,0.39,2001 union all
select 10003,'20110224',19,0.23,2002 union all
select 10004,'20110224',19,0.31,2003 union all
select 10002,'20110224',20,0.39,2004 union all
select 10003,'20110224',20,0.49,2005 union all
select 10004,'20110224',20,0.51,2006 union all
select 10002,'20110224',21,0.59,2007 union all
select 10003,'20110224',21,0.04,2008 union all
select 10004,'20110224',21,0.25,2009
select a.*,b.para2
from #t1 a,#t2 b
where a.cell = b.cell and a.para1 = b.para1
and not exists(select * from #t2 where cell=b.cell and para1=b.para1 and para2<b.para2 )cell para1 para2
----------- --------------------------------------- -----------
10002 0.39 2001
10003 0.23 2002
10004 0.31 2003(3 row(s) affected)
group by T1.cell,T1.para1得分 Bingo!!!
谢谢二位关注,我好像从来没说过跟para2有关系吧
如果一个组合对应两个para2,你又要求只保留2000条记录,那么只能取其一,如果不这样做,你的这个题目本身就是有错误的。我取min(para2),就是为了避免这种情况,你运行一下就好了
如果放在EXCEL里面实现就是
1.将para1所在的列倒序排列
2.取不同cell值的第一行(同一个cell有不同date,time,不止一行)。
3.构成新表cell,para1,para2,para3...现在想要用SQL实现,保存模板方便日后使用。
(
cell int,
[date] varchar(100),
[time] varchar(100),
para1 int,
para2 int,
para3 int
)
insert #T2
select 1, '2011-1-1', '10:00:00', 1, 2, 3 union all
select 1, '2011-1-2', '11:00:00', 2, 2, 3 union all
select 2, '2011-1-3', '12:00:00', 3, 2, 3 union all
select 2, '2011-1-4', '13:00:00', 4, 2, 3 union all
select 2, '2011-1-5', '14:00:00', 5, 2, 3--SQL
select a.cell, b.para1, b.para2, b.para3 from
(select distinct cell from #T2) a
cross apply
(select top(1) * from #T2 where cell = a.cell order by para1 desc) b
order by b.para1 desc
--RESULT
/*
cell para1 para2 para3
2 5 2 3
1 2 2 3
*/
select t1.*,t2.para2
from t1,t2
where t1.cell = t2.cell and t1.para1 = t2.para1
on T1.cell = T2.cell and T1.para1 = T2.para1
cel1 INT,
para1 FLOAT )INSERT INTO t1
select 10002,0.39 union all
select 10003,0.23 union all
select 10004,0.31
CREATE TABLE T2(
cel1 INT,
[date] INT,
[time] INT,
para1 FLOAT,
para2 INT
)INSERT INTO T2
SELECT 10002,20110224,19,0.39,2001 union all
SELECT 10003,20110224,19,0.23,2002 union all
SELECT 10004,20110224,19,0.31,2003 union ALL
SELECT 10002,20110224,20,0.39,2004 union all
SELECT 10003,20110224,20,0.49,2005 union all
SELECT 10004,20110224,20,0.51,2006 union all
SELECT 10002,20110224,21,0.59,2007 union all
SELECT 10003,20110224,21,0.04,2008 union all
SELECT 10004,20110224,21,0.25,2009SELECT DISTINCT t1.*
--,t2.para2
FROM t1 LEFT JOIN t2 ON t1.cel1 =t2.cel1 AND t1.para1 = t2.para1
这样加上 DISTINCT 就可以了吧
;with cte as
(
select t1.*,t2.para2,N=ROW_NUMBER()over(partition by t2.cell ,t2.para1 order by para1 desc)
from t1,t2
where t1.cell = t2.cell and t1.para1 = t2.para1
)
select * from cte t
where not exists(select * from cte where cel1=t.cel1 and para1=t.para1 and N>t.N )