求sql语句

[liqiyou]

原始表数据：
组号 药品名 数量 每天次数 每次用量
1 药A 10 2 50mg
1 药B 10 2 20mg
2 药C 5 1 15mg
3 药D 3 1 25mg
现在通过一个SQL 语句，生成如下结果
组号 药品名 数量 每次用量
1 药A 5 50mg
1 药B 5 20mg
2 药C 5 15mg
3 药D 3 25mg
4 药A 5 50mg
4 药B 5 20mg
请大家指点一下，谢谢了

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(GROUP_NO INT,
 PHA_NAME VARCHAR(10),
 QTY INT,
 FREQUENCY INT)


INSERT INTO #T
SELECT 1,'A',10,2 UNION ALL
SELECT 1,'B',10,2 UNION ALL
SELECT 2,'C',5,1 UNION ALL
SELECT 3,'D',8,4 UNION ALL
SELECT 4,'E',6,1 UNION ALL
SELECT 5,'F',6,3 UNION ALL
SELECT 5,'G',6,3
GO

WITH CTE
AS
(SELECT A.*,B.NUMBER,C.MAX_GROUP_NO 
FROM #T A
JOIN MASTER.DBO.SPT_VALUES B ON A.FREQUENCY>=B.NUMBER
JOIN (SELECT MAX(GROUP_NO) AS MAX_GROUP_NO FROM #T) C ON 1=1
WHERE NUMBER>0
AND TYPE='P')


SELECT * 
FROM
(SELECT MAX_GROUP_NO+DENSE_RANK() OVER (ORDER BY NUMBER,GROUP_NO) AS GROUP_NO,
PHA_NAME,QTY/FREQUENCY AS QTY 
FROM CTE
WHERE NUMBER>1
UNION ALL
SELECT GROUP_NO,PHA_NAME,QTY/FREQUENCY FROM CTE
WHERE NUMBER=1) AS A
ORDER BY GROUP_NO,PHA_NAME

[Hello World,]

DECLARE @t TABLE(组号 INT NOT NULL, 药品名 NVARCHAR(100) NOT NULL, 数量 DECIMAL(10,2) NOT NULL, 每天次数 INT NOT NULL, 每次用量 NVARCHAR(100) NOT NULL)
DECLARE @out TABLE(组号 INT NOT NULL, 药品名 NVARCHAR(100) NOT NULL, 数量 DECIMAL(10,2) NOT NULL, 每次用量 NVARCHAR(100) NOT NULL)

INSERT @t(组号, 药品名, 数量, 每天次数, 每次用量)
VALUES(1,'药A',10,2,'50mg'),(1,'药B',10,2,'20mg'),(2,'药C',5,1,'15mg'),(3,'药D',3,1,'25mg')
WHILE EXISTS (SELECT 1 FROM @t WHERE 每天次数>0)
BEGIN
	INSERT @out(组号, 药品名, 数量, 每次用量)
	SELECT 组号,药品名,数量,每次用量 FROM @t WHERE 每天次数>0
	UPDATE @t SET 每天次数=每天次数-1 WHERE 每天次数>0	--组号的规律是什么？有规律可一并更新
END
SELECT * FROM @out

[文盲老顾]

with t as (
	select 1 as 组,'A' as 药,10 as 数,2 as 次,'50mg' as 量
	union all
	select 1,'B',10,2,'20mg'
	union all
	select 2,'C',5,1,'15mg'
	union all
	select 3,'D',3,1,'25mg'
	union all
	select 4,'E',12,3,'25mg'
)
,t1 as (
	select 组,药,数/次 as 数,量,b.number 
	from t a 
	left join master..spt_values b on b.number<=a.次 and b.number>0 and b.type='p'
)
,t2 as (
	select 组,number,组 as n from t1 group by 组,number
),t3 as (
	select * from t2 where number=1
	union all
	select 组,number,(select max(组) from t2)+row_number() over(order by 组,number) as n from t2 where number>1
)
select a.*,n from t1 a
left join t3 b on a.组=b.组 and a.number=b.number
order by n,药

[文盲老顾]

组           药    数           量    number      n
----------- ---- ----------- ---- ----------- --------------------
1           A    5           50mg 1           1
1           B    5           20mg 1           1
2           C    5           15mg 1           2
3           D    3           25mg 1           3
4           E    4           25mg 1           4
1           A    5           50mg 2           5
1           B    5           20mg 2           5
4           E    4           25mg 2           6
4           E    4           25mg 3           7

(9 行受影响)

[北冥小渔]

DECLARE  @t TABLE(组号 int NOT NULL ,药品名 varchar(50) NOT NULL,数量 int NOT NULL,每天次数 int NOT NULL,每次更换 VARCHAR(50) NOT NULL )
INSERT into @t  
SELECT 1,'药A',10,2,'50mg' UNION ALL
SELECT 1,'药B',10, 2, '20mg' UNION ALL
SELECT 2,'药C',5, 1, '15mg' UNION ALL
SELECT 3,'药D',3, 1, '25mg' 

SELECT 组号,
       药品名,
       CASE 
            WHEN 每天次数 = 0 THEN 0
            ELSE (数量 / 每天次数)
       END AS 数量,
       每次更换
FROM   @t