SQL Group BY在新列中使用字符串 [英] SQL Group BY using strings in new columns

问题描述

我有一个比赛结果数据库。每场比赛都有多个课程。

events 表：

  event_id | event_date 
 --------- + ------------ 
 1 | 11/5/14 
 2 | 11/12/14 
 3 | 11/14/14 
  

结果表：

  result_event |名称| class |位置
 ------------- + --------------- + ------- + ------- ---- 
 1 |杰森史密斯| 40 | 1 
 1 |大卫约翰逊| 40 | 2 
 1 |兰迪白| 30 | 1 
 1 |比利汉森| 30 | 2 
 2 | Wally Mann | 40 | 1 
 2 | Shawn Little | 40 | 2 
 2 |埃里克戴维斯| 30 | 1 
 2 |汤姆Handy | 30 | 2 
  

我想创建一个汇总表，列出每个类的事件日期和获胜者。 / p>

像这样：

 活动日期| 40级优胜者| 30级优胜者
 ------------ + ----------------- + ------------ ------ 
 11/5/14 |杰森史密斯|兰迪白
 11/12/14 | Wally Mann | Eric Davis 
  

我需要什么查询才能创建 GROUP BY event_id 并在不同列中列出获胜者？

解决方案

您可以加入来自结果表的两个查询的事件表，每个类一个：

  SELECT event_data，class_40_winner，class_30_winner 
 FROM events e 
 LEFT JOIN（SELECT result_event，name AS class_40_winner 
 FROM results 
 WHERE class = 40 AND position = 1）c40 ON e.id = c40.result_event 
 LEFT JOIN（SELECT result_event，name AS class_30_winner 
 FROM results 
 WHERE class = 30 AND position = 1）c30 ON e.id = c30.result_event 
  

I have a database of race results. Each race event has multiple classes.

events table:

event_id | event_date
---------+------------
1        | 11/5/14
2        | 11/12/14
3        | 11/19/14

results table:

result_event | name          | class | position
-------------+---------------+-------+-----------
1            | Jason Smith   | 40    | 1
1            | David Johnson | 40    | 2
1            | Randy White   | 30    | 1
1            | Billy Hansen  | 30    | 2 
2            | Wally Mann    | 40    | 1
2            | Shawn Little  | 40    | 2
2            | Eric Davis    | 30    | 1
2            | Tom Handy     | 30    | 2

I want to create a summary table that lists the Event Date and the winners of each class.

Like this:

Event Date  | Class 40 Winner | Class 30 Winner
------------+-----------------+------------------
11/5/14     | Jason Smith     | Randy White
11/12/14    | Wally Mann      | Eric Davis

What query would I need so that I can create a GROUP BY event_id and list winners in separate columns?

解决方案

You can join the events table on two queries from the results table, one for each class:

SELECT    event_data, class_40_winner, class_30_winner
FROM      events e
LEFT JOIN (SELECT result_event, name AS class_40_winner
           FROM   results 
           WHERE  class = 40 AND position = 1) c40 ON e.id = c40.result_event
LEFT JOIN (SELECT result_event, name AS class_30_winner
           FROM   results 
           WHERE  class = 30 AND position = 1) c30 ON e.id = c30.result_event

这篇关于SQL Group BY在新列中使用字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！