SQL 查询中选择的语法 [英] Syntax for Select in SQL Query
问题描述
我有一个带有下表的 sqlite 数据库:
I have an sqlite database with the following table:
CREATE TABLE games (name text, date text, winloss int, gameid int, pointsfor int, pointsagainst int);
两个示例记录如下:
Anna A, 7/12/13, 0, 345, 56, 28
Barley B, 7/12/13, 1, 345, 28, 56
(Barley 的球队输了,Anna 赢了.每场比赛每队都有几名球员.)我想创建一个查询,该查询将返回在一个球队中有 x 名球员而在另一支球队中有 y 名球员的所有比赛,加上累积那些游戏的结果.
(Barley's team lost, and Anna's won. Each game has several players on each team.) I want to create a query that will return all the games that had x players on one team and y players on another, plus the cumulative result of those games.
我知道如何使用 perl 和 csv 文件执行此操作,并且我相信我可以对 dbi 接口使用相同的方法.但是,我想了解如何仅使用 SQL 查询来创建此报告.我是 SQL 的新手,我怀疑该解决方案可能涉及使用 CASE WHEN 或 JOIN 对表进行透视以创建新表;但我不知道该怎么做.
I know how to do this using perl and a csv file, and I believe that I could use the same method with the dbi interface. I want to learn how to create this report using only SQL queries, however. I am a newcomer to SQL, and I suspect the solution may involve pivoting the table using CASE WHEN or JOIN to create a new table; but I can't see how to do it.
此查询将返回玩家在同一支球队并赢得(或输掉,取决于 winloss 的值)的所有比赛:
This query will return all the games where the players were on the same team and won (or lost, depending on the value of winloss):
select gameid,date from games
where name in ('Anna A', 'Barley B') and winloss=1
group by gameid
having count(*)>1;
但我不知道如何概括该查询以返回与另一支球队的球员的比赛.
But I don't know how to generalize that query to return games with players on the other team.
推荐答案
这将为您提供 A 和 B 战胜 C、D 和 E 的一场或多场比赛的所有台词.
This will give you all the lines for the game or games where A and B won versus C, D and E.
select *
from games
where gameid in
(select gameid from games
where name in ('Anna A', 'Barley B') and winloss=1
group by gameid
having count(*) = 2
intersect
select gameid from games
where name in ('Charly C', 'Dave D', 'Ed E') and winloss = 0
group by gameid
having count(*) = 3) ;
或者,您可以使用:
select *
from games where gameid in (
select gameid from games where name = 'Anna A' and winloss = 1 intersect
select gameid from games where name = 'Barley B' and winloss = 1 intersect
select gameid from games where name = 'Charly C' and winloss = 0 intersect
select gameid from games where name = 'Dave D' and winloss = 0 intersect
select gameid from games where name = 'Ed E' and winloss = 0
) ;
哪个最适合你.
然后您可以添加 sum
和 group by
以获得累积结果.
You can then add sum
and group by
to get cumulative results.
这篇关于SQL 查询中选择的语法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!