使用数据透视表mySQL查询特定值 [英] Query a specific value with Pivot Table mySQL

问题描述

我已经在互联网上进行了搜索，但找不到找到我问题的答案的运气很差.我希望这还没有专门发布，但是如果您能指出我可以找到答案的地方，我将不胜感激.

I have searched all over the internet and I have had no luck in finding the answer to my question. I hope this has not specifically been posted yet, but if you can point to where i can find the answer I would appreciate it.

我能够获取以下表格数据:

I was able to take the following table data below:

id   lead_id form_id field_number   value
1       1      1        1           Hosker
2       1      1        7           b**********@yahoo.com
3       1      1        6           Hyundai Tournament of Champions
4       1      1        3           Adam Scott
5       1      1        4           Harris English
6       1      1        5           2014-01-02 23:59:47
7       1      1        8           5b409692-e9ed-486e-8d77-7d734f1e023d

并使用以下查询字符串获取以下结果:

And get the result below using this query string:

SELECT id, lead_id, form_id, 
MAX(case when field_number = 1 then value end) username, 
MAX(case when field_number = 7 then value end) email, 
MAX(case when field_number = 6 then value end) tournament_name, 
MAX(case when field_number = 3 then value end) primary_golfer, 
MAX(case when field_number = 4 then value end) backup_golfer, 
MAX(case when field_number = 5 then value end) date, 
MAX(case when field_number = 8 then value end) tournament_id 
FROM `wp_rg_lead_detail` GROUP BY lead_id



id   lead_id form_id field_number   value     value                  value                              value          value            value                      value
 1      1      1        1           Hosker    b**********@yahoo.com  Hyundai Tournament of Champions    Adam Scott    Harris English    2014-01-02 23:59:47    5b409692-e9ed-486e-8d77-7d734f1e023d

我只希望该查询字符串仅在race_id列的值等于特定值时提取相关数据.我该怎么办?

I just want that query string to only pull the related data when the value of the tournament_id column equals a specific value. How would I do that?

推荐答案

尝试此查询.我认为您可以使用HAVING子句.

try this query. I think you can use the HAVING clause.

SELECT id, lead_id, form_id, 
MAX(case when field_number = 1 then value end) username, 
MAX(case when field_number = 7 then value end) email, 
MAX(case when field_number = 6 then value end) tournament_name, 
MAX(case when field_number = 3 then value end) primary_golfer, 
MAX(case when field_number = 4 then value end) backup_golfer, 
MAX(case when field_number = 5 then value end) date, 
MAX(case when field_number = 8 then value end) tournament_id 
FROM `wp_rg_lead_detail` GROUP BY lead_id
HAVING tournament_id = '5b409692-e9ed-486e-8d77-7d734f1e023d'

sqlFiddle 在sqlFiddle中，我有2个不同的tour_id和将应用HAVING子句，以便仅返回该race_id的值，您可以尝试删除HAVING并看到它返回2行.

sqlFiddle In the sqlFiddle I have sample data for 2 different tournament_id and the HAVING clause is applied so that only values for that tournament_id would be returned, you can try removing the HAVING and see that it returns 2 rows.

您的分组依据不是标准的，因为您还同时选择了id和form_id.我建议您删除ID或使用MIN(id)或MAX(id)和GROUP BY lead_id,form_id

Your Group By is not standard since you're selecting id and form_id as well. I suggest you drop the the id or use MIN(id) or MAX(id) and GROUP BY lead_id,form_id

这篇关于使用数据透视表mySQL查询特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！