SQL - 多次选择选择性行 | 多次选择选择性行

本文介绍了SQL - 多次选择选择性行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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 - 多次选择选择性行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！