当值> 1 [duplicate]时，如何获取多个行

Possible Duplicate:
Select Records multiple times from table

可能的重复:从表中多次选择记录

I want to have my query return (multiple) rows for the value of TABLE_B.QTY.

我想让我的查询返回(多个)行作为TABLE_B.QTY的值。

TABLE A
SALESNR  ITEMNR LINENR
100      B2001  1
101      B2002  2
102      A1021  3 

TABLE B 
LINENR   COLOR QTY
1        WHITE  3  
2        BLACK  1
3        BROWN  8

For instance, with the following query:

例如，使用以下查询:

SELECT    TABLE_A.SALESNR, TABLE_A.ITEMNR, TABLE_B.COLOR, TABLE_B.QTY
FROM       TABLE_A  INNER JOIN TABLE_B ON TABLE_B.LINENR = TABLE_A.LINENR

I get:

我得到:

100    B2001   White   3

What I need is:

我需要的是:

100    B2001   White   3
100    B2001   White   3
100    B2001   White   3

Is there a way to do this? Can't think of the right keywords to Google this...

有办法吗?想不出合适的关键字谷歌这个…

Thnx,

感谢,

Mike

迈克

2 个解决方案

#1

This will work as long as QTY is less than 2047

只要QTY小于2047就可以

SELECT TABLE_A.SALESNR, TABLE_A.ITEMNR, TABLE_B.COLOR, TABLE_B.QTY 
FROM TABLE_A
INNER JOIN TABLE_B ON TABLE_B.LINENR = TABLE_A.LINENR 
INNER JOIN master..spt_values ON type = 'P' AND number < TABLE_B.QTY

use this if QTY exceeds 2047:

如果数量超过2047，请使用这个:

;WITH a AS
(
SELECT TABLE_A.SALESNR, TABLE_A.ITEMNR, TABLE_B.COLOR, TABLE_B.QTY, 1 row
FROM TABLE_A
INNER JOIN TABLE_B ON TABLE_B.LINENR = TABLE_A.LINENR 
WHERE QTY > 0
union all
SELECT SALESNR, ITEMNR, COLOR, QTY, row+1 
FROM a
WHERE QTY > row 
)
SELECT SALESNR, ITEMNR, COLOR, QTY from a
OPTION (MAXRECURSION 0)

#2

The cross join won't do it if you have one row in each table, which I interpret it as. I would suggest, if possible, to re-design your data model to solve this - or loop in code where you use this data.

如果每个表中都有一行，那么cross join就不会这么做，我将其解释为。如果可能的话，我建议重新设计您的数据模型来解决这个问题——或者在使用这些数据的代码中循环。

You can loop in the T-SQL if absolutely needed.

如果绝对需要，可以在T-SQL中循环。

regards, Olle

问候,大车

Can't seem to comment other peoples posts, just wanted to say, nice solution, to t-clausen.dk!

似乎不能评论别人的帖子，只想对t-clausen.dk说，不错的解决方案!

#1