SQLite选择/过滤表并限制输出
SQLite选择/过滤表并限制输出
提问于 2015-11-24 20:01:34
我正在为学校做一个SQL项目--我有一个表：
name price_gold price_euros role universe
---------- ---------- ----------- ---------- -----------------
Sylvanas 10,000 9,99 Specialist World of Warcraft
The Lost V 10,000 9,99 Specialist The Lost Vikings
Azmodan 10,000 9,99 Specialist Diablo
Zagara 7,000 8,49 Specialist Starcraft
Murky 7,000 8,49 Specialist World of Warcraft
Abathur 10,000 9,99 Specialist Starcraft
Gazlowe 4,000 6,49 Specialist World of Warcraft
Nazeebo 10,000 9,99 Specialist Diablo
Sgt. Hamme 7,000 8,49 Specialist Starcraft
Artanis 10,000 9,99 Warrior Starcraft
Rexxar 10,000 9,99 Warrior World of Warcraft
Leoric 10,000 9,99 Warrior Diablo
Johanna 10,000 9,99 Warrior Diablo
Anub'arak 4,000 6,49 Warrior World of Warcraft
Chen 7,000 8,49 Warrior World of Warcraft
Arthas 7,000 8,49 Warrior World of Warcraft
Diablo 7,000 8,49 Warrior Diablo
E.T.C. 2,000 3,99 Warrior World of Warcraft
Muradin 2,000 3,99 Warrior World of Warcraft
Sonya 4,000 6,49 Warrior Diablo
Stitches 7,000 8,49 Warrior World of Warcraft
Tyrael 4,000 6,49 Warrior Diablo
Cho Warrior World of Warcraft
Gall Assassin World of Warcraft
The Butche 10,000 9,99 Assassin Diablo
Kael'thas 10,000 9,99 Assassin World of Warcraft
Thrall 10,000 9,99 Assassin World of Warcraft
Jaina 4,000 6,49 Assassin World of Warcraft
Tychus 4,000 6,49 Assassin Starcraft
Falstad 7,000 8,49 Assassin World of Warcraft
Illidan 4,000 6,49 Assassin World of Warcraft我想查询5个不同角色和5个随机名称的选择，即：
name role
---------- ---------
Sylvanas Specialis
The Lost V Specialis
Azmodan Specialis
Zagara Specialis
Murky Specialis
Johanna Warrior
Anub'arak Warrior
Chen Warrior
Arthas Warrior
Stitches Warrior
The Butche Assassin
Kael'thas Assassin
Thrall Assassin
Jaina Assassin
Tychus Assassin
Lt. Morale Support
Kharazim Support
Reghar Support
Brightwing Support我尝试使用Limit，但这只给出了表的前5个元素。
该怎么办呢？
Edit1：
SELECT * FROM
(SELECT hero.name, hero.role
FROM hero
INNER JOIN
(SELECT DISTINCT role FROM hero
LIMIT 1 OFFSET 1) n
ORDER BY RANDOM() LIMIT 5) As t1
UNION
SELECT * FROM
(SELECT hero.name, hero.role
FROM hero
INNER JOIN
(SELECT DISTINCT role FROM hero
LIMIT 1 OFFSET 2) n
ORDER BY RANDOM() LIMIT 5) As t2
UNION
SELECT * FROM
(SELECT hero.name, hero.role
FROM hero
INNER JOIN
(SELECT DISTINCT role FROM hero
LIMIT 1 OFFSET 3) n
ORDER BY RANDOM() LIMIT 5) As t3
UNION
SELECT * FROM
(SELECT hero.name, hero.role
FROM hero
INNER JOIN
(SELECT DISTINCT role FROM hero
LIMIT 1 OFFSET 4) n
ORDER BY RANDOM() LIMIT 5) As t4
UNION
SELECT * FROM
(SELECT hero.name, hero.role
FROM hero
INNER JOIN
(SELECT DISTINCT role FROM hero
LIMIT 1 OFFSET 5) n
ORDER BY RANDOM() LIMIT 5) As t5这是您在更改表@Parfait后发送的代码。
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-11-24 22:40:27
考虑对每个不同的角色使用5个随机名称的联合查询。因为UNION查询不支持多个LIMIT和ORDER BY子句，所以使用表别名。偏移量用于减少不同角色的列表。
SELECT * FROM
(SELECT g.name, g.role
FROM games g
WHERE g.role = 'Specialist'
ORDER BY RANDOM() LIMIT 5) As t1
UNION
SELECT * FROM
(SELECT g.name, g.role
FROM games g
WHERE g.role = 'Support'
ORDER BY RANDOM() LIMIT 5) As t2
UNION
SELECT * FROM
(SELECT g.name, g.role
FROM games g
WHERE g.role = 'Warrior'
ORDER BY RANDOM() LIMIT 5) As t3
UNION
SELECT * FROM
(SELECT g.name, g.role
FROM games g
WHERE g.role = 'Assassin'
ORDER BY RANDOM() LIMIT 5) As t4
UNION
SELECT * FROM
(SELECT g.name, g.role
FROM games g
WHERE g.role = 'Admin'
ORDER BY RANDOM() LIMIT 5) As t5;Stack Overflow用户
发布于 2015-11-24 21:30:29
如果您知道您想要的5个角色，您可以对5个子查询使用限制，然后将它们合并在一起：
select name, role from mytable where role = 'Support' limit 5
union all
select name, role from mytable where role = 'Assassin' limit 5
union all
select name, role from mytable where role = 'Warrior' limit 5
union all
select name, role from mytable where role = 'Specialist' limit 5
union all
select name, role from mytable where role = 'Mom' limit 5SQL在随机行选择方面不是很好，但是如果您有行id，则可以使用rand() (如果使用mysql)并使用它来选择随机行id。您提到的和我使用的限制5可能每次返回非常相似的一组行。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33902735
复制相关文章
相似问题
腾讯云开发者
