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

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

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

问题

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

  1. 拥有相同结构的两个表：
  3.     CREATE TABLE Table1 
  4.     (
  5.         Id INT, 
  6.         Name VARCHAR(256), 
  7.         Value DECIMAL(18,2), 
  8.         LineId INT
  9.     ); 
  11.     CREATE TABLE Table2 
  12.     (
  13.         Id INT, 
  14.         Name VARCHAR(256), 
  15.         Value DECIMAL(18,2), 
  16.         LineId INT
  17.     ); 
  19. 是否有办法仅匹配基于其列的记录的第一个“未使用”出现：
  21.     Table1.ID = Table2.Id AND Table1.Value = Table2.Value
  23. 示例数据：
  25.     INSERT INTO Table1 VALUES(1, 'aaaaa', 100, 1);
  26.     INSERT INTO Table1 VALUES(1, 'bbbb', 50, 2);
  27.     INSERT INTO Table1 VALUES(1, 'cccc', 100, 3);
  28.     INSERT INTO Table1 VALUES(1, 'dddd', 5, 4);
  29.     INSERT INTO Table1 VALUES(1, 'ffff', 100, 5);
  31.     INSERT INTO Table2 VALUES(1, 'ddd', 40, 1);
  32.     INSERT INTO Table2 VALUES(1, 'eee', 100, 2);
  33.     INSERT INTO Table2 VALUES (1, 'ffff', 5, 3)
  34.     INSERT INTO Table2 VALUES(1, 'fff', 100, 4);
  36. 结果应该是：
  38.     (1, 'aaaaa', 100 , 'eee',1);
  39.     (1, 'bbbb', 50, null,2);
  40.     (1, 'cccc', 100, 'fff',3);
  41.     (1, 'dddd', 5, 'fff',4);
  42.     (1, 'ffff', 100, null,5);
  44. 我尝试使用LEFT JOIN [sqlfiddle](http://sqlfiddle.com/#!18/d7443f/3)，但这会创建一个笛卡尔积：
  46.     SELECT
  47.         t1.Id AS id, 
  48.         t1.Name AS NameT1, 
  49.         t1.Value AS ValueT1, 
  50.         t2.Name AS fromName,
  51.         t1.LineID AS LineId
  52.     FROM
  53.         Table1 t1
  54.     LEFT JOIN
  55.         Table2 t2 ON t1.Id = t2.Id AND t1.Value = t2.Value
  56.     ORDER BY
  57.         t1.LineId;
英文:

Having two table with same structure:

  1. CREATE TABLE Table1 
  2. (
  3. Id INT, 
  4. Name VARCHAR(256), 
  5. Value DECIMAL(18,2), 
  6. LineId INT
  7. ); 
  8. CREATE TABLE Table2 
  9. (
  10. Id INT, 
  11. Name VARCHAR(256), 
  12. Value DECIMAL(18,2), 
  13. LineId INT
  14. );

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

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

Sample data:

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

The result should be :

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

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

  1. SELECT
  2. t1.Id AS id, 
  3. t1.Name AS NameT1, 
  4. t1.Value AS ValueT1, 
  5. t2.Name AS fromName,
  6. t1.LineID AS LineId
  7. FROM
  8. Table1 t1
  9. LEFT JOIN
  10. Table2 t2 ON t1.Id = t2.Id AND t1.Value = t2.Value
  11. ORDER BY
  12. t1.LineId;

答案1

得分: 1

以下是翻译好的部分：

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

  1. select x.id, x.name, x.value, y.name, x.lineid
  2. from (
  3.   select t.*, row_number() over(partition by id, value order by lineid) as rn 
  4.   from table1 t
  5. ) x
  6. left join (
  7.   select t.*, row_number() over(partition by id, value order by lineid) as rn
  8.   from table2 t
  9. ) y on y.id = x.id and y.value = x.value and y.rn = x.rn
  10. order by x.name

结果：

  1.  id  name   value   name  lineid 
  2.  --- ------ ------- ----- ------ 
  3.  1   aaaaa  100.00  eee   1      
  4.  1   bbbb   50.00   null  2      
  5.  1   cccc   100.00  fff   3      
  6.  1   dddd   5.00    ffff  4      
  7.  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:

  1. select x.id, x.name, x.value, y.name, x.lineid
  2. from (
  3. select t.*, row_number() over(partition by id, value order by lineid) as rn 
  4. from table1 t
  5. ) x
  6. left join (
  7. select t.*, row_number() over(partition by id, value order by lineid) as rn
  8. from table2 t
  9. ) y on y.id = x.id and y.value = x.value and y.rn = x.rn
  10. order by x.name

Result:

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

答案2

得分: 0

以下是您要翻译的内容：

Query 1:

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

Results:

  1. | id | NameT1 | ValueT1 | fromName | LineId |
  2. |----|--------|---------|----------|--------|
  3. |  1 |  aaaaa |     100 |      eee |      1 |
  4. |  1 |   bbbb |      50 |   (null) |      2 |
  5. |  1 |   cccc |     100 |      fff |      3 |
  6. |  1 |   dddd |       5 |   (null) |      4 |
  7. |  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:

  1. WITH CTE AS
  2. (select t1.Id as id, 
  3. t1.Name as NameT1, 
  4. t1.Value as ValueT1, 
  5. t2.Name as fromName,
  6. t1.LineID as LineId,
  7. ROW_NUMBER() OVER(PARTITION BY t1.Name,t1.Value ORDER BY  t2.LineID) rn
  8. from Table1 t1
  9. LEFT join Table2 t2 on t1.Id = t2.Id and t1.Value = t2.Value AND 
  10. t1.LineID &lt; t2.LineID
  11. )
  12. SELECT id ,	NameT1, 	ValueT1, 	fromName ,	LineId
  13. FROM CTE WHERE rn = 1
  14. order by LineId

Results:

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

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

发表评论

匿名网友

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

确定