当前位置: 动力学知识库 > 问答 > 编程问答 >

Retrieve different number of rows for each category from SQL Server table

问题描述:

I need to generate a list of addresses to which my client is going to send merchandise catalogs. They want to send a certain number of catalogs (X) to a particular zip code (Y) [This has to do with average income in those zip codes]

I have 2 tables in SQL Server 2008 R2: CatalogRequests and AddressList

CatalogRequests is a list of how many catalogs we should send to each zip code in our customer database:

| Zip | QuantityRequested |

-------------------------------

| 12345 | 150 |

| 13445 | 800 |

| 45678 | 200 |

| 41871 | 350 |

| 77777 | 125 |

AddressList is just that, a list of addresses =)

| Name | Address1 | Address2 | City | State | Zip |

---------------------------------------------------------------------------

| Bruce | 1 BatCave Dr | | Gotham City | IL | 12345 |

| Clark | 9 Smallville St | Apt A | Metropolis | NY | 45678 |

| Thor | 5 Valhalla Way | | Asgard | ?? | 77777 |

I racked my brain for a little while trying to do this with an SQL query, then gave up and wrote a small C# program to do what I needed to do (basically, generating a bunch of SQL queries - one for each record in CatalogRequests).

My question is, how could I have done this with one SQL query? I'm just curious at this point, it seems like there is a way to do it and I'm just missing something. Or might not be possible and I'm crazy =)

The result set would be records from AddressList that met the requirements in CatalogRequest (for example, 150 records with a zip code of 12345, 800 records with a zip code of 13445, etc).

网友答案:

Hmm... What about something like this:

;with addressListWithID 
AS 
(
    SELECT name, address1, adress2, city, state, zip, 
           ROW_NUMBER() OVER(partition by zip   order by newid()) as Row
    FROM AddressList 
)
SELECT A.name, A.address1, A.adress2, A.city, A.state, A.zip
FROM addressListWithID A 
    INNER JOIN CatalogRequests C
        ON C.zip = A.zip 
            AND A.row <= C.QuantityRequested 

Random addresses in quantity requested.

网友答案:

Untested, because there is no DDL and no test data:

SELECT  A.name ,
        A.address1 ,
        A.adress2 ,
        A.city ,
        A.[state] ,
        A.zip
FROM    CatalogRequests AS C
        CROSS APPLY ( SELECT TOP ( C.QuantityRequested )
                                A.name ,
                                A.address1 ,
                                A.adress2 ,
                                A.city ,
                                A.[state] ,
                                A.zip
                      FROM      addressListWithID AS A
                      WHERE     C.zip = A.zip
                    ) AS A
网友答案:

One method is to use dynamic SQL (which is dangerous and not to be taken lightly):

DECLARE @SQL varchar(max)

SET @SQL = 'USE Database'

SELECT @SQL = @SQL + 'SELECT TOP ' 
            + QuantityRequired
            + ' * FROM AddressList WHERE Zip = '
            + Zip
            + ' UNION ALL'
FROM CatalogRequests

SET @SQL = LEFT(@SQL, (LEN(@SQL - 10))) -- remove last UNION ALL 

PRINT (@SQL)

-- EXEC (@SQL)

Check the link for the canonical reference on Dynamic SQL.

分享给朋友:
您可能感兴趣的文章:
随机阅读: