SQL - 多次选择选择性行 [英] SQL - select selective row multiple times

问题描述

我需要为我的公司制作邮寄标签，我想我会为此做一个查询:

I need to produce mailing labels for my company and I thought I would do a query for that:

我有 2 个表 - tblAddress ，tblContact.

I have 2 tables - tblAddress , tblContact.

在 tblContact 中，我有addressNum"，它是地址的外键，labelsNum"列表示地址应出现在标签表中.

In tblContact I have "addressNum" which is a foreign key of address and "labelsNum" column that represents the number of times the address should appear in the labels sheet.

我需要通过 addressNum 创建 tblcontact 和 tbladdress 的内部连接，但是如果 labelsNum 存在不止一次，它应该显示与 labelsNum 一样多的次数.

I need to create an inner join of tblcontact and tbladdress by addressNum, but if labelsNum exists more than once it should be displayed as many times as labelsNum is.

推荐答案

我建议使用递归查询为每一行执行正确的迭代次数.

I suggest using a recursive query to do the correct number of iterations for each row.

这是代码(+ SQL fiddle的链接):

Here is the code (+ link to SQL fiddle):

;WITH recurs AS (
  SELECT *, 1 AS LEVEL
  FROM tblContact
  UNION ALL
  SELECT t1.*, LEVEL + 1
  FROM tblContact t1
  INNER JOIN
  recurs t2
  ON t1.addressnum = t2.addressnum
  AND t2.labelsnum > t2.LEVEL
 )

SELECT *
FROM recurs
ORDER BY addressnum

这篇关于SQL - 多次选择选择性行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！