SQL使用两个不同的表对数据进行排序

I need to sort data using 2 different tables. In Table A I have the order that is inserted consecutively by the user and in Table B I have the values that I should get using the order from table A and a linked column from the 2 tables called "IdMetadata". Data:

我需要使用两个不同的表对数据进行排序。在表A中，我有用户连续插入的顺序，在表B中，我有应该使用表A的顺序获得的值，以及来自两个表的链接列“IdMetadata”。数据:

 declare @table1 table(idMetadata int, Orden int identity)
 insert into @table1 values (15)
 insert into @table1 values (16)
 select * from @table1

 declare @table2 table(idDet int, idEnc int, IDMetadata int, OrderValue int)
 insert into @table2 values (185441,    22008,  15, 7)
 insert into @table2 values (187777,    22269,  15, 7)
 insert into @table2 values (211259,    24925,  15, 7)
 insert into @table2 values (251476,    29431,  15, 4)
 insert into @table2 values (185442,    22008,  16, 6)
 insert into @table2 values (187778,    22269,  16, 6)
 insert into @table2 values (211260,    24925,  16, 6)
 insert into @table2 values (251477,    29431,  16, 5)

The main problem is that I have to create a query that gets values from table B and have a consecutive order like table A (rows in yellow) using the field called "OrderValue" with ascending order.

主要的问题是，我必须创建一个查询，该查询从表B获取值，并使用名为“OrderValue”的字段(按升序排列)创建一个像表a(黄色的行)这样的连续顺序。

The following query is the basic idea that I have until now

下面的查询是我到目前为止的基本思想

    select distinct t3.idEnc, t3.IDMetadata, t3.OrderValue
    from @table1 t1
    inner join @table2 t2
    on t1.idMetadata = t2.IDMetadata
    inner join @table2 t3
    on t2.idEnc = t3.idEnc
    order by t3.idEnc desc, t3.IDMetadata asc, t3.OrderValue asc

1 个解决方案

#1

As per your comment, this is what you are trying to do.

根据你的评论，这就是你要做的。

select * from @table2 where idenc in (
select distinct t15.idEnc
    from @table1 t1
    inner join (select * from @table2 where IDmetadata = 15) t15
    on t1.idMetadata = t15.IDMetadata
    inner join (select * from @table2 where IDmetadata = 16) t16
    on  t15.idenc=t16.idenc
where t16.ordervalue > t15.ordervalue
    )
order by idenc,idmetadata,ordervalue