Mysql选择并显示两列 [英] Mysql select and display two column

问题描述

我想提取存储在表的同一列中的两种类型的数据并将其显示在两列中.我做了很多搜索，做了很多测试，但没有任何效果.这可能很容易做到...

I want to extract two types of data which is store in the same column of my table and display it in two column. I have do many search and do many test but nothing works. It could be easy to do...

这是我的桌子:

| id  | field_id | user_id | value            | 
| 175 |       65 |       3 | 48.8984188201264 | 
| 173 |       65 |       2 | 37.9841493       | 
| 177 |       65 |       4 | 48.8440603       | 
| 179 |       65 |       5 | 48.8407529       | 
| 174 |       66 |       2 | 23.7279843       | 
| 176 |       66 |       3 | 2.25568230749569 | 
| 178 |       66 |       4 | 2.3730525        | 
| 180 |       66 |       5 | 2.3213214        | 

我想通过加入 user_id 在一个字段中显示纬度 (Field_id=65) 和在另一个字段中显示经度 (field_id=66).

I want to display latitude (Field_id=65) in one field and longitude (field_id= 66) in another one by joining on the user_id.

二选一:

select value as longitude 
from wp_bp_xprofile_data 
where field_id=66; 

select value as latitude 
from wp_bp_xprofile_data 
where field_id=65;

| longitude        |
| 23.7279843       |
| 2.25568230749569 |
| 2.3730525        |
| 2.3213214        |

| latitude         |
| 48.8984188201264 |
| 37.9841493       |
| 48.8440603       |
| 48.8407529       |

如何在一个包含两列的表格中显示这些数据?

How I can display this data in one table with two column?

谢谢

推荐答案

Bassam 的回答很好，但它并不是世界上最有效的方法.您可以像这样简化查询:

Bassam's answer is all well and good, but it's not exactly the most efficient thing in the world. You can simplify your query like so:

select
    t1.user_id,
    t1.value as latitude,
    t2.value as longitude
from
    wp_bp_xprofile_data t1
    inner join wp_bp_xprofile_data t2 on
        t1.user_id = t2.user_id
where
    t1.field_id = 65
    and t2.field_id = 66

这样，您就不会引入子查询，而且您的查询会更清晰一些，至少对我来说是这样.

This way, you're not pulling in subqueries, and your query is a little clearer, at least to me.

这篇关于Mysql选择并显示两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！