只匹配两个表之间第一个 “not used” 值的出现。

huangapple go评论78阅读模式
英文:

match only first "not used" occurrence of a value between two tables

问题

以下是您提供的代码的翻译部分:

拥有相同结构的两个表:

    CREATE TABLE Table1 
    (
        Id INT, 
        Name VARCHAR(256), 
        Value DECIMAL(18,2), 
        LineId INT
    ); 

    CREATE TABLE Table2 
    (
        Id INT, 
        Name VARCHAR(256), 
        Value DECIMAL(18,2), 
        LineId INT
    ); 

是否有办法仅匹配基于其列的记录的第一个“未使用”出现:

    Table1.ID = Table2.Id AND Table1.Value = Table2.Value

示例数据:

    INSERT INTO Table1 VALUES(1, 'aaaaa', 100, 1);
    INSERT INTO Table1 VALUES(1, 'bbbb', 50, 2);
    INSERT INTO Table1 VALUES(1, 'cccc', 100, 3);
    INSERT INTO Table1 VALUES(1, 'dddd', 5, 4);
    INSERT INTO Table1 VALUES(1, 'ffff', 100, 5);

    INSERT INTO Table2 VALUES(1, 'ddd', 40, 1);
    INSERT INTO Table2 VALUES(1, 'eee', 100, 2);
    INSERT INTO Table2 VALUES (1, 'ffff', 5, 3)
    INSERT INTO Table2 VALUES(1, 'fff', 100, 4);

结果应该是:

    (1, 'aaaaa', 100 , 'eee',1);
    (1, 'bbbb', 50, null,2);
    (1, 'cccc', 100, 'fff',3);
    (1, 'dddd', 5, 'fff',4);
    (1, 'ffff', 100, null,5);

我尝试使用LEFT JOIN [sqlfiddle](http://sqlfiddle.com/#!18/d7443f/3),但这会创建一个笛卡尔积:

    SELECT
        t1.Id AS id, 
        t1.Name AS NameT1, 
        t1.Value AS ValueT1, 
        t2.Name AS fromName,
        t1.LineID AS LineId
    FROM
        Table1 t1
    LEFT JOIN
        Table2 t2 ON t1.Id = t2.Id AND t1.Value = t2.Value
    ORDER BY
        t1.LineId;
英文:

Having two table with same structure:

CREATE TABLE Table1 
(
Id INT, 
Name VARCHAR(256), 
Value DECIMAL(18,2), 
LineId INT
); 
CREATE TABLE Table2 
(
Id INT, 
Name VARCHAR(256), 
Value DECIMAL(18,2), 
LineId INT
); 

Is there a way to match only the first "not used" occurrence of the record based on their columns:

Table1.ID = Table2.Id AND Table1.Value = Table2.Value

Sample data:

INSERT INTO Table1 VALUES(1, 'aaaaa', 100, 1);
INSERT INTO Table1 VALUES(1, 'bbbb', 50, 2);
INSERT INTO Table1 VALUES(1, 'cccc', 100, 3);
INSERT INTO Table1 VALUES(1, 'dddd', 5, 4);
INSERT INTO Table1 VALUES(1, 'ffff', 100, 5);
INSERT INTO Table2 VALUES(1, 'ddd', 40, 1);
INSERT INTO Table2 VALUES(1, 'eee', 100, 2);
INSERT INTO Table2 VALUES (1, 'ffff', 5, 3)
INSERT INTO Table2 VALUES(1, 'fff', 100, 4);

The result should be :

(1, 'aaaaa', 100 , 'eee',1);
(1, 'bbbb', 50, null,2);
(1, 'cccc', 100, 'fff',3);
(1, 'dddd', 5, 'fff',4);
(1, 'ffff', 100, null,5);

I've tried using a LEFT JOIN sqlfiddle but this creates a cartesian product :

SELECT
t1.Id AS id, 
t1.Name AS NameT1, 
t1.Value AS ValueT1, 
t2.Name AS fromName,
t1.LineID AS LineId
FROM
Table1 t1
LEFT JOIN
Table2 t2 ON t1.Id = t2.Id AND t1.Value = t2.Value
ORDER BY
t1.LineId;

答案1

得分: 1

以下是翻译好的部分:

您可以为每个数据集动态生成行号,并根据值+行号将行配对。例如:

select x.id, x.name, x.value, y.name, x.lineid
from (
  select t.*, row_number() over(partition by id, value order by lineid) as rn 
  from table1 t
) x
left join (
  select t.*, row_number() over(partition by id, value order by lineid) as rn
  from table2 t
) y on y.id = x.id and y.value = x.value and y.rn = x.rn
order by x.name

结果:

 id  name   value   name  lineid 
 --- ------ ------- ----- ------ 
 1   aaaaa  100.00  eee   1      
 1   bbbb   50.00   null  2      
 1   cccc   100.00  fff   3      
 1   dddd   5.00    ffff  4      
 1   ffff   100.00  null  5
英文:

You can generate a row number on the fly for each dataset and pair rows according to value+rownumber. For example:

select x.id, x.name, x.value, y.name, x.lineid
from (
select t.*, row_number() over(partition by id, value order by lineid) as rn 
from table1 t
) x
left join (
select t.*, row_number() over(partition by id, value order by lineid) as rn
from table2 t
) y on y.id = x.id and y.value = x.value and y.rn = x.rn
order by x.name

Result:

 id  name   value   name  lineid 
--- ------ ------- ----- ------ 
1   aaaaa  100.00  eee   1      
1   bbbb   50.00   null  2      
1   cccc   100.00  fff   3      
1   dddd   5.00    ffff  4      
1   ffff   100.00  null  5      

答案2

得分: 0

以下是您要翻译的内容:

Query 1:

使用公用表表达式(CTE)和ROW_NUMBER,您可以获得所需的结果。此外,您还需要在连接条件中添加另一个条件以消除交叉连接。
WITH CTE AS
(选择t1.Id作为id,
t1.Name作为NameT1,
t1.Value作为ValueT1,
t2.Name作为fromName,
t1.LineID作为LineId,
ROW_NUMBER() OVER(按t1.Name,t1.Value分区,按t2.LineID排序)rn
从Table1 t1
LEFT JOIN Table2 t2 ON t1.Id = t2.Id并且t1.Value = t2.Value AND
t1.LineID < t2.LineID
)
选择id,NameT1,ValueT1,fromName,LineId
从CTE中选择rn = 1
按LineId排序

Results:

| id | NameT1 | ValueT1 | fromName | LineId |
|----|--------|---------|----------|--------|
|  1 |  aaaaa |     100 |      eee |      1 |
|  1 |   bbbb |      50 |   (null) |      2 |
|  1 |   cccc |     100 |      fff |      3 |
|  1 |   dddd |       5 |   (null) |      4 |
|  1 |   ffff |     100 |   (null) |      5 |
英文:

You cqan use a CTE with a ROW_NUMBER to get your wanted results, further you need als to get rid of the croos join add anonther condition to your join

Query 1:

WITH CTE AS
(select t1.Id as id, 
t1.Name as NameT1, 
t1.Value as ValueT1, 
t2.Name as fromName,
t1.LineID as LineId,
ROW_NUMBER() OVER(PARTITION BY t1.Name,t1.Value ORDER BY  t2.LineID) rn
from Table1 t1
LEFT join Table2 t2 on t1.Id = t2.Id and t1.Value = t2.Value AND 
t1.LineID &lt; t2.LineID
)
SELECT id ,	NameT1, 	ValueT1, 	fromName ,	LineId
FROM CTE WHERE rn = 1
order by LineId

Results:

| id | NameT1 | ValueT1 | fromName | LineId |
|----|--------|---------|----------|--------|
|  1 |  aaaaa |     100 |      eee |      1 |
|  1 |   bbbb |      50 |   (null) |      2 |
|  1 |   cccc |     100 |      fff |      3 |
|  1 |   dddd |       5 |   (null) |      4 |
|  1 |   ffff |     100 |   (null) |      5 |

huangapple
  • 本文由 发表于 2023年3月4日 02:13:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75630545.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定