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:

I have 2 tables - tblAddress , tblContact.

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.

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.

回答1:

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

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

回答2:

Wouldn't the script return multiple lines for different contacts anyway?

CREATE TABLE tblAddress ( 
  AddressID int IDENTITY
  , [Address] nvarchar(35)
);

CREATE TABLE tblContact ( 
  ContactID int IDENTITY
  , Contact nvarchar(35)
  , AddressNum int
  , labelsNum int
);

INSERT INTO tblAddress VALUES ('foo1');
INSERT INTO tblAddress VALUES ('foo2');

INSERT INTO tblContact VALUES ('bar1', 1, 1);
INSERT INTO tblContact VALUES ('bar2', 2, 2);
INSERT INTO tblContact VALUES ('bar3', 2, 2);

SELECT * FROM tblAddress a JOIN tblContact c ON a.AddressID = c.AddressNum

This yields 3 rows on my end. The labelsNum column seems redundant to me. If you add a third contact for address foo2, you would have to update all labelsNum columns for all records referencing foo2 in order to keep things consistent.

The amount of labels is already determined by the amount of different contacts.

Or am I missing something?