27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[name] nvarchar(22))
Insert #a
select 1,N'张三' union all
select 1,N'李四' union all
select 2,N'王五' union all
select 2,N'赵六' union all
select 3,N'钱七'
Go
--测试数据结束
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY id ORDER BY GETDATE()) AS num FROM #a
)
SELECT a.id,a.name,b.name AS name1
FROM cte a
LEFT JOIN cte b ON b.id = a.id
AND b.num = 2
WHERE a.num = 1
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[name] nvarchar(21))
Insert #a
select 1,N'一' union all
select 2,N'二'
GO
if not object_id(N'Tempdb..#b') is null
drop table #b
Go
Create table #b([id] int,[name] nvarchar(25))
Insert #b
select 1,N'一,二,三' union all
select 2,N'二' --这里添加了一条测试数据,不满足条件的数据,不会搜索出来
Go
--测试数据结束
SELECT *
FROM #b
WHERE ( SELECT COUNT(1)
FROM #a
WHERE CHARINDEX(',' + #a.name + ',', ',' + #b.name + ',') > 0
) = ( SELECT COUNT(1)
FROM #a
)
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[name] nvarchar(22))
Insert #a
select 1,N'张三' union all
select 1,N'张三' union all
select 2,N'李四' union all
select 2,N'李四' union all
select 3,N'王五'
Go
--测试数据结束
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY id ORDER BY GETDATE()) AS num FROM #a
)
SELECT a.id,a.name,b.name AS name1
FROM cte a
LEFT JOIN cte b ON b.id = a.id
AND b.num = 2
WHERE a.num = 1
SELECT t.id ,
( SELECT TOP 1
name
FROM #a
WHERE t.id = id
ORDER BY name
) AS name1 ,
( SELECT TOP 1
name
FROM #a
WHERE t.id = id
ORDER BY name DESC
) AS name2
FROM ( SELECT DISTINCT
id
FROM #a
) t
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[name] nvarchar(22))
Insert #a
select 1,N'张三' union all
select 1,N'李四' union all
select 2,N'张三' union all
select 2,N'李四'
Go
--测试数据结束
SELECT t.id ,
( SELECT TOP 1
name
FROM #a
WHERE t.id = id
ORDER BY NEWID()
) AS name
FROM ( SELECT DISTINCT
id
FROM #a
) t
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[name] nvarchar(22))
Insert #a
select 1,N'张三' union all
select 1,N'李四' union all
select 2,N'张三' union all
select 2,N'李四'
Go
--测试数据结束
SELECT id ,
STUFF(( SELECT ',' + name
FROM #a
WHERE id = a.id
FOR
XML PATH('')
), 1, 1, '') AS name
FROM #a AS a
GROUP BY id
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[name] nvarchar(22))
Insert #a
select 1,N'张三' union all
select 1,N'李四' union all
select 2,N'张三' union all
select 2,N'李四'
Go
--测试数据结束
SELECT t.id ,
( SELECT TOP 1
name
FROM #a
WHERE t.id = id
ORDER BY name
) AS name1 ,
( SELECT TOP 1
name
FROM #a
WHERE t.id = id
ORDER BY name DESC
) AS name2
FROM ( SELECT DISTINCT
id
FROM #a
) t