34,590
社区成员
发帖
与我相关
我的任务
分享
0)表和新的测试数据
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([ID] INT,[Name] NVARCHAR(2))
Go
INSERT INTO ta
SELECT 1,'张三' UNION ALL
SELECT 2,'李四'
GO
-- Test Data: tb
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
Go
----tb new data 这里MAX(PATH)为图片一,MIN(PATH)为图片二
CREATE TABLE tb([ID] INT,[AID] INT,[path] NVARCHAR(3))
Go
INSERT INTO tb
SELECT 1,1,'图片一' UNION ALL
SELECT 1,2,'图片二' UNION ALL
SELECT 1,1,'图片三'
GO
1)查询1
SELECT ID = ta.id ,NAME,PATH
FROM ta
INNER JOIN
(SELECT *,RANK = ROW_NUMBER()OVER(PARTITION BY AID,id ORDER BY GETDATE())FROM tb)B
ON ta.id = B.Aid
WHERE B.RANK = 1
1.1)结果
ID NAME PATH
----------- ---- ----
1 张三 图片一
2 李四 图片二
(2 row(s) affected)
--SET STATISTICS PROFILE OFF
2)查询2
select ta.*,tb.path from ta,tb where ta.id=tb.aid and
not exists(select 1 from tb t where t.aid=tb.aid and t.path<tb.path)
2.1)结果
ID Name path
----------- ---- ----
1 张三 图片三
2 李四 图片二
(2 row(s) affected)
3)同理修改tb数据,再测试 ...
0)表和数据
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([ID] INT,[Name] NVARCHAR(2))
Go
INSERT INTO ta
SELECT 1,'张三' UNION ALL
SELECT 2,'李四'
GO
-- Test Data: tb
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
Go
CREATE TABLE tb([ID] INT,[AID] INT,[path] NVARCHAR(3))
Go
INSERT INTO tb
SELECT 1,1,'图片零' UNION ALL
SELECT 1,2,'图片二' UNION ALL
SELECT 1,1,'图片一'
GO
1)查询
SELECT ID = ta.id ,NAME,PATH
FROM ta
INNER JOIN
(SELECT *,RANK = ROW_NUMBER()OVER(PARTITION BY AID,id ORDER BY GETDATE())FROM tb)B
ON ta.id = B.Aid
WHERE B.RANK = 1
2)结果
ID NAME PATH
----------- ---- ----
1 张三 图片零
2 李四 图片二
(2 row(s) affected)
/*
-----------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
-----------------------------------------------------------------------
瘦狼阿亮 \ 享受学习的乐趣
*/
use tempdb
--建立A变量表
declare @A table (ID smallint,Name varchar(30))
--录入表A测试数据
insert into @A
select 1,'张三' union all
select 2,'李四'
/*执行结果:
(所影响的行数为 2 行)
*/
--查看A表录入的测试数据
select * from @a
/*执行结果:
ID Name
------ ------------------------------
1 张三
2 李四
(所影响的行数为 2 行)
*/
--建立B变量表
declare @B table (ID smallint,AID smallint,Path varchar(30))
--录入表A测试数据
insert into @B
select 1,1,'图片一' union all
select 1,2,'图片二' union all
select 1,1,'图片三'
/*执行结果:
(所影响的行数为 3 行)
*/
--查看B表录入的测试数据
select * from @b
/*执行结果:
ID AID Path
------ ------ ------------------------------
1 1 图片一
1 2 图片二
1 1 图片三
(所影响的行数为 3 行)
*/
---执行相关查询
select
a.id,a.name,b.path
from
@a a,@b b
where
a.id=b.aid and not exists
(select 1 from @b tb where tb.aid=b.aid and tb.Path>b.path)
/*执行结果:
id name path
------ ------------------------------ ------------------------------
1 张三 图片一
2 李四 图片二
(所影响的行数为 2 行)
*/
select a.*,b.path from a,b where a.id=b.aid and
not exists(select 1 from b t where t.aid=b.aid and t.path<b.path)
select
a.*,b.path
from
a,b
where
a.id=b.aid
and
path=(select max(path) from b t where t.aid=b.aid )
------------------------------------------------------------------------
-- Author : happyflystone
-- Date : 2010-03-07
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
------------------------------------------------------------------------
-- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([ID] INT,[Name] NVARCHAR(2))
Go
INSERT INTO ta
SELECT 1,'张三' UNION ALL
SELECT 2,'李四'
GO
-- Test Data: tb
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
Go
CREATE TABLE tb([ID] INT,[AID] INT,[path] NVARCHAR(3))
Go
INSERT INTO tb
SELECT 1,1,'图片一' UNION ALL
SELECT 1,2,'图片二' UNION ALL
SELECT 1,1,'图片三'
GO
--Start
;with cte
as
(select rid = row_number() over(partition by aid order by getdate()),*
from tb )
select a.*,b.path
from ta a left join cte b on a.id = b.aid and b.rid = 1
--Result:
/*
ID Name path
----------- ---- ----
1 张三 图片一
2 李四 图片二
(2 行受影响)
*/
--End
;with cte
as
(select rid = row_number() over(partition by aid order by getdate()),*
from tb )
select a.*,b.path
from ta a left join cte b on a.id = b.aid and b.rid = 1
select id,name,path
from (select row=row_number()over(order by getdate()),tba.id.name.path
from tba join tbb on tba.id=tbb.aid)K
where row=1
select a.*,b.path from a,b
where a.id=b.aid and
not exists(select 1 from b t where t.aid=b.aid and t.path<b.path)