--有数据如下结构:USE tempdbCREATE TABLE t1(vname VARCHAR(10),phone VARCHAR(10),date DATETIME)
INSERT t1
SELECT 'aaa','110','2008-07-04 16:18:34.000' UNION ALL
SELECT 'bbb','110','2008-07-04 15:54:08.000' UNION ALL
SELECT 'eee','110','2008-07-04 15:54:08.000' UNION ALL
SELECT 'ccc','114','2008-07-04 16:18:34.000' UNION ALL
SELECT 'ddd','110','2008-07-04 15:55:08.000'--需要结果
/*
vname phone date
---------- ---------- -----------------------
bbb 110 2008-07-04 16:18:34.000
bbb 110 2008-07-04 15:54:08.000
bbb 110 2008-07-04 15:54:08.000
ccc 114 2008-07-04 16:18:34.000
bbb 110 2008-07-04 15:55:08.000
*/--这是使用的SQLSELECT b.vname,t.phone,t.date FROM t1 t LEFT JOIN
(SELECT a.vname,a.phone
FROM (SELECT mid=ROW_NUMBER() OVER (PARTITION BY phone ORDER BY date),* FROM t1)a WHERE a.mid=1)b
ON t.phone=b.phone
传说中的第六感告诉我这语句很累赘...事实确实如此因为表t1是个视图目前数据有15W左右
在此求 路过的达人指教是否有更好的查询或者有效的优化方式
解决方案 »
- 求助!如何在sql server查询结果中添加新的一列,大意如下
- sqlserver2005 表同步到远程 sqlserver 2000 有例子没?
- mssql到底能承受多大?
- N'DECLARE @ID DECIMAL;SET @ID=-1; 什么意思?
- 这样的一句sql 语句怎么写
- 两台数据库服务器数据传输问题
- 在附加数据库时提示“错误5105:设备激活错误。物理文件名"[" 可能有误”。该如何解决。
- 请教高手,sql语句查询数据的问题?
- 只有MDF恢复数据库,出现“未能读取并闩锁页 (5:681)(用闩锁类型 SH)”是什么错?
- 关于插入一条记录???
- 一个小小的SQL2005数据库问题
- 我如何知道dbf的版本呢
INSERT t1
SELECT 'aaa','110','2008-07-04 16:18:34.000' UNION ALL
SELECT 'bbb','110','2008-07-04 15:54:08.000' UNION ALL
SELECT 'eee','110','2008-07-04 15:54:08.000' UNION ALL
SELECT 'ccc','114','2008-07-04 16:18:34.000' UNION ALL
SELECT 'ddd','110','2008-07-04 15:55:08.000'
goselect
vname=(select top 1 vname from t1 where phone=t.phone ORDER BY date),
phone,
date
from t1 t
go/**
vname phone date
---------- ---------- -----------------------
bbb 110 2008-07-04 16:18:34.000
bbb 110 2008-07-04 15:54:08.000
bbb 110 2008-07-04 15:54:08.000
ccc 114 2008-07-04 16:18:34.000
bbb 110 2008-07-04 15:55:08.000(5 行受影响)
**/drop table t1
go
INSERT t1
SELECT 'aaa','110','2008-07-04 16:18:34.000' UNION ALL
SELECT 'bbb','110','2008-07-04 15:54:08.000' UNION ALL
SELECT 'eee','110','2008-07-04 15:54:08.000' UNION ALL
SELECT 'ccc','114','2008-07-04 16:18:34.000' UNION ALL
SELECT 'ddd','110','2008-07-04 15:55:08.000'goselect vname = (select top 1 vname from t1 where phone = t.phone order by date , vname),
phone,date from t1 tdrop table t1/*
vname phone date
---------- ---------- ------------------------------------------------------
bbb 110 2008-07-04 16:18:34.000
bbb 110 2008-07-04 15:54:08.000
bbb 110 2008-07-04 15:54:08.000
ccc 114 2008-07-04 16:18:34.000
bbb 110 2008-07-04 15:55:08.000(所影响的行数为 5 行)
*/
b.*
from
t1 a
cross apply
(select top 1 * from t1 where phone=a.phone order by date )b
phone,date from t1 t
from t1 a
cross apply
(select top(1) vname from t1 where phone = a.phone order by [date]) b
再扩充下问题
t1增加个编号
CREATE TABLE t1(vname VARCHAR(10),vno varchar(10),phone VARCHAR(10),date DATETIME)
INSERT t1
SELECT 'aaa','a1001','110','2008-07-04 16:18:34.000' UNION ALL
SELECT 'bbb','b1001','110','2008-07-04 15:54:08.000' UNION ALL
SELECT 'eee','e1001','110','2008-07-04 15:54:08.000' UNION ALL
SELECT 'ccc','c1001','114','2008-07-04 16:18:34.000' UNION ALL
SELECT 'ddd','d1001','110','2008-07-04 15:55:08.000't2:
create table t2(vno varchar(10),phone varchar(10),productno varchar(10),date datetime)
insert t2
select 'a1001','110','p001','2008-07-04 16:18:34.000' union all
select 'f1001','114','p001','2008-07-04 16:18:34.000' union all
select 'n1001','110','p003','2008-07-04 16:54:34.000' union all
select 'c1001','120','p002','2008-07-04 16:18:34.000' union all
select 'd1001','119','p004','2008-07-04 16:54:34.000' union all
select 'h1001','121','p005','2008-07-04 17:18:34.000' /*需求结果*/
vno vname phone productno date
------- -------- ------- --------- ---------
b1001 bbb 110 p001 2008-07-04 16:18:34.000
c1001 ccc 114 p001 2008-07-04 16:18:34.000
b1001 bbb 110 p003 2008-07-04 16:54:34.000
c1001 120 p002 2008-07-04 16:18:34.000
d1001 119 p004 2008-07-04 16:54:34.000
h1001 121 p005 2008-07-04 17:18:34.000
效率还是关键问题,我已经在phone加上非聚集索引了效果还是不太理想
望达人们指点!!分数不足可另开