SELECT TOP 10 a.*, b.ID FROM userinfo a, Photos b WHERE a.UploadUser = b.UploadUser ORDER BY NEWID()
select top 10 * from Photos表 order by newid()
不行,UploadUser有重复的希望没一个用户一张随机图片,随机10个用户
--> 生成测试数据表: [UserInfo] IF OBJECT_ID('[UserInfo]') IS NOT NULL DROP TABLE [UserInfo] GO CREATE TABLE [UserInfo] ([userId] [nvarchar](10),[other] [nvarchar](10)) INSERT INTO [UserInfo] SELECT 'u1','...' UNION ALL SELECT 'u2','...' UNION ALL SELECT 'u3','...' UNION ALL SELECT 'u4','...' UNION ALL SELECT 'u5','...' UNION ALL SELECT 'u6','...' UNION ALL SELECT 'u7','...' UNION ALL SELECT 'u8','...' UNION ALL SELECT 'u9','...' UNION ALL SELECT 'u10','...' UNION ALL SELECT 'u11','...' UNION ALL SELECT 'u12','...' UNION ALL SELECT 'u13','...' UNION ALL SELECT 'u14','...' UNION ALL SELECT 'u15','...' UNION ALL SELECT 'u16','...' UNION ALL SELECT 'u17','...' UNION ALL SELECT 'u18','...' UNION ALL SELECT 'u19','...'--> 生成测试数据表: [Photos] IF OBJECT_ID('[Photos]') IS NOT NULL DROP TABLE [Photos] GO CREATE TABLE [Photos] ([ID] [int],[UploadUser] [nvarchar](10)) INSERT INTO [Photos] SELECT '1','u11' UNION ALL SELECT '2','u12' UNION ALL SELECT '3','u13' UNION ALL SELECT '4','u14' UNION ALL SELECT '5','u15' UNION ALL SELECT '6','u12' UNION ALL SELECT '7','u13' UNION ALL SELECT '8','u14' UNION ALL SELECT '9','u15' UNION ALL SELECT '10','u16' UNION ALL SELECT '11','u17' UNION ALL SELECT '12','u18' UNION ALL SELECT '13','u19' UNION ALL SELECT '14','u10' UNION ALL SELECT '15','u12' UNION ALL SELECT '16','u1' UNION ALL SELECT '17','u2' UNION ALL SELECT '18','u2' UNION ALL SELECT '19','u3' UNION ALL SELECT '20','u4'--SELECT * FROM [UserInfo] --SELECT * FROM [Photos]-->SQL查询如下:SELECT TOP 10 a.*, b.ID FROM UserInfo a, ( SELECT TOP 10 * FROM Photos p WHERE [ID] = (SELECT MIN(id) FROM Photos WHERE [UploadUser]=p.[UploadUser]) ORDER BY NEWID() ) b WHERE a.[userId] = b.[UploadUser] /* userId other ID ---------- ---------- ----------- u1 ... 16 u3 ... 19 u10 ... 14 u11 ... 1 u12 ... 2 u13 ... 3 u16 ... 10 u17 ... 11 u18 ... 12 u19 ... 13(10 行受影响) */
FROM userinfo a, Photos b
WHERE a.UploadUser = b.UploadUser
ORDER BY NEWID()
--> 生成测试数据表: [UserInfo]
IF OBJECT_ID('[UserInfo]') IS NOT NULL
DROP TABLE [UserInfo]
GO
CREATE TABLE [UserInfo] ([userId] [nvarchar](10),[other] [nvarchar](10))
INSERT INTO [UserInfo]
SELECT 'u1','...' UNION ALL
SELECT 'u2','...' UNION ALL
SELECT 'u3','...' UNION ALL
SELECT 'u4','...' UNION ALL
SELECT 'u5','...' UNION ALL
SELECT 'u6','...' UNION ALL
SELECT 'u7','...' UNION ALL
SELECT 'u8','...' UNION ALL
SELECT 'u9','...' UNION ALL
SELECT 'u10','...' UNION ALL
SELECT 'u11','...' UNION ALL
SELECT 'u12','...' UNION ALL
SELECT 'u13','...' UNION ALL
SELECT 'u14','...' UNION ALL
SELECT 'u15','...' UNION ALL
SELECT 'u16','...' UNION ALL
SELECT 'u17','...' UNION ALL
SELECT 'u18','...' UNION ALL
SELECT 'u19','...'--> 生成测试数据表: [Photos]
IF OBJECT_ID('[Photos]') IS NOT NULL
DROP TABLE [Photos]
GO
CREATE TABLE [Photos] ([ID] [int],[UploadUser] [nvarchar](10))
INSERT INTO [Photos]
SELECT '1','u11' UNION ALL
SELECT '2','u12' UNION ALL
SELECT '3','u13' UNION ALL
SELECT '4','u14' UNION ALL
SELECT '5','u15' UNION ALL
SELECT '6','u12' UNION ALL
SELECT '7','u13' UNION ALL
SELECT '8','u14' UNION ALL
SELECT '9','u15' UNION ALL
SELECT '10','u16' UNION ALL
SELECT '11','u17' UNION ALL
SELECT '12','u18' UNION ALL
SELECT '13','u19' UNION ALL
SELECT '14','u10' UNION ALL
SELECT '15','u12' UNION ALL
SELECT '16','u1' UNION ALL
SELECT '17','u2' UNION ALL
SELECT '18','u2' UNION ALL
SELECT '19','u3' UNION ALL
SELECT '20','u4'--SELECT * FROM [UserInfo]
--SELECT * FROM [Photos]-->SQL查询如下:SELECT TOP 10 a.*, b.ID
FROM UserInfo a, (
SELECT TOP 10 *
FROM Photos p
WHERE [ID] = (SELECT MIN(id) FROM Photos WHERE [UploadUser]=p.[UploadUser])
ORDER BY NEWID()
) b
WHERE a.[userId] = b.[UploadUser]
/*
userId other ID
---------- ---------- -----------
u1 ... 16
u3 ... 19
u10 ... 14
u11 ... 1
u12 ... 2
u13 ... 3
u16 ... 10
u17 ... 11
u18 ... 12
u19 ... 13(10 行受影响)
*/