SQL-连接同一表中的行 [英] SQL - joining rows from same table

问题描述

我只知道有关SQL的最低要求，所以请把我当作一个完整的菜鸟！

I only know the bare min when it comes to SQL so please treat me as a complete noob!

我有一个表，其中有很多行，但是其中一些行可以通过id配对，并且我希望合并这些行的返回数组.

I have a table which has many rows but some of them can pair up by the id and I want return array of those rows merged.

 post_id  # meta_name # meta_value  #     
======================================
   1      #   bar     #    44       #
   1      #   foo     #    on       #
   2      #   bar     #    1        #
   2      #   foo     #    on       #
   3      #   bar     #    1        #
   3      #   foo     #    off      #

作为缩小版本，请想象一下我表的上方，我的目标是返回2个结果

as a scaled down version, imagine the above of my table and I am aiming to return 2 results of

   1    #   foo    #  bar    #   44   #  on
   2    #   foo    #  bar    #   1    #  on

基于ID相关并且c2的值为'on'

based on that the ids are related and that c2 has the value of 'on'

我的尝试是从一些阅读中得到的，但没有得到结果

my attempt was the following from some reading but not getting anywhere

SELECT 
    t1.post_id, t2.post_id, t1.meta_name, t2.meta_name, t1.meta_value, t2.meta_value 
FROM 
    `ecom_page_meta` t1 
JOIN 
    `ecom_page_meta` t2 
ON 
    t1.post_id = t2.post_id 
WHERE 
    t1.meta_name = 'featured-products' 
AND 
    t1.meta_value = 'on'

任何帮助将不胜感激

**编辑**

由于以下答案，我认为Id仅发布了在下面可以很好地使用的语法:

I thought Id just post the syntax which worked nicely below thanks to the answer below:

SELECT L.post_id, L.meta_name, R.meta_name, L.meta_value, R.meta_value
FROM `ecom_page_meta` L
INNER JOIN ecom_page_meta R
    ON L.post_id = R.post_id
    AND L.meta_name = 'featured-products-order' 
    AND R.meta_value = 'on' 
WHERE R.meta_name = 'featured-products' 
ORDER BY L.meta_value 
DESC

再次感谢.

推荐答案

创建测试数据:

SQL> create table ecom_page_meta (post_id number not null
  2      , meta_name varchar2(15) not null
  3      , meta_value varchar2(15) not null);

Table created.

SQL> insert into ecom_page_meta values (1, 'bar', '44');

1 row created.

SQL> insert into ecom_page_meta values (1, 'foo', 'on');

1 row created.

SQL> insert into ecom_page_meta values (2, 'bar', '1');

1 row created.

SQL> insert into ecom_page_meta values (2, 'foo', 'on');

1 row created.

SQL> insert into ecom_page_meta values (3, 'bar', '1');

1 row created.

SQL> insert into ecom_page_meta values (3, 'foo', 'off');

1 row created.

SQL> commit;

Commit complete.

查询以提供与OP样本匹配的结果:

Query to give results matching OP's sample:

SQL> select L.post_id, L.meta_name, R.meta_name, L.meta_value, R.meta_value
  2  from ecom_page_meta L
  3  inner join ecom_page_meta R
  4      on L.post_id = R.post_id
  5      and L.meta_value <> 'on'
  6      and R.meta_value = 'on'
  7  where L.meta_name = 'bar';

   POST_ID META_NAME       META_NAME       META_VALUE      META_VALUE
---------- --------------- --------------- --------------- ---------------
         1 bar             foo             44              on
         2 bar             foo             1               on

我仍然不知道 t1.meta_name ='featured-products'在做什么，因为没有示例数据包含字符串'featured-products'的t1.meta_name.

I still do not know what WHERE t1.meta_name = 'featured-products' has do to do with anything since no sample data includes t1.meta_name of the string 'featured-products'.

这篇关于SQL-连接同一表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！