如题:A表有字段
AID,Name
1 张三
2 李四 B表有 BID,AID,DetailName
1 1 张大大
2 1 张小小
3 2 李大大
4 2 李中中
5 2 李小小期望得到的查询结果是:
AID BID Name DetailName
1 1 张三 张大大
2 3 李四 李大大有人知道如何编写一个比较优化的sql语句吗?(当数据量达到10万以上)sql优化
AID,Name
1 张三
2 李四 B表有 BID,AID,DetailName
1 1 张大大
2 1 张小小
3 2 李大大
4 2 李中中
5 2 李小小期望得到的查询结果是:
AID BID Name DetailName
1 1 张三 张大大
2 3 李四 李大大有人知道如何编写一个比较优化的sql语句吗?(当数据量达到10万以上)sql优化
where not exists (
select 1 from b b2
where b2.aid = b1.aid
and b2.bid < b1.bid
)
select * from tableA as A
inner join TableB as B on A.AID=B.AID
where not exists(select 1 from TableB where AID=B.AID and ID>b.ID)
inner join TableB as B on A.AID=B.AID
where not exists(select 1 from TableB where AID=B.AID and ID<b.ID)
where (select count(1) from testGroup a where a.value=b.value and a.id<=b.id) = 1
select A.AID,A.Name,B.BID,B.DetailName
from B
left join (
select min(b.BID) BID ,A.AID from B
group by A.AID
) T on T.BID =B.BID
join A on A.AID=B.AID
这个是我的方法
(AID int,Name varchar(50))
insert into #tab1
select '1','张三' union all
select '2','李四'
create table #tab2
(BID int,AID int,DetailName varchar(50))
insert into #tab2
select '1','1','张大大 ' union all
select '2', '1', '张小小' union all
select '3', '2', '李大大' union all
select '4', '2', '李中中' union all
select '5', '2', '李小小'select a.AID,b.BID,a.Name,b.DetailName
from #tab1 a
left join
( select ROW_NUMBER()over(partition by AID order by AID)id,* from #tab2)b
on a.AID=b.AID where id=1AID BID Name DetailName
----------- ----------- -------------------------------------------------- --------------------------------------------------
1 1 张三 张大大
2 3 李四 李大大(2 行受影响)
--TableA
CREATE TABLE TableA
(AID INT,
NAME NVARCHAR(20)
)
go
--TableB
CREATE TABLE TableB
(
BID INT,
AID INT,
DetailNAME NVARCHAR(20)
)
go
--添加数据
DECLARE @a INT
DECLARE @b INT
SET @a=1
SET @b=1
WHILE @a<200000 --20万
BEGIN
INSERT INTO dbo.TableA
( AID, Name )
VALUES ( @a,'张_'+LTRIM(@a)
)
INSERT INTO dbo.TableB( BID, AID, DetailName )
VALUES ( @b,@a, '张小_'+LTRIM(@a)+'_A' )
INSERT INTO dbo.TableB ( BID, AID, DetailName )
VALUES ( @b+1,@a, '张小_'+LTRIM(@a) +'_B')
SET @a=@a+1
SET @b=@b+2
END
SELECT * FROM dbo.TableA
SELECT * FROM dbo.Tableb
--delete from TableA
--delete from Tableb
--roy_88 结果查询:22s
select A.AID,A.Name,B.BID,B.DetailName from tableA as A
inner join TableB as B on A.AID=B.AID
where not exists(select 1 from TableB where AID=B.AID and BID>b.BID)--roy_88 结果查询:6s
select A.AID,A.Name,B.BID,B.DetailName
from tableA as A
inner join TableB as B on A.AID=B.AID
where b.BID=(select MIN(BID) from TableB where AID=B.AID)
--seusoftware
select B.BID,B.DetailName from tableB b
where (select count(1) from tableB a where a.BID<=BID) = 1--qujunchang 结果查询:5s
select a.AID,b.BID,a.Name,b.DetailName
from tableA a
left join
( select ROW_NUMBER()over(partition by AID order by AID)id,* from tableB)b
on a.AID=b.AID where id=1--MY 结果查询:6s
select A.AID,A.Name,B.BID,B.DetailName
from tableB B
join (
select min(BID) BID ,AID from tableB B
group BY AID
) T on T.BID =B.BID
join tableA A on A.AID=B.AID
以上是20万的查询结果,发现qujunchang的执行时间最短