原始表資料：
組號 藥品名 數量 每天次數 每次用量
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
請大家指點一下，謝謝了
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
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,藥
uj5u.com熱心網友回復：
組 藥 數 量 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 行受影響)
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/149579.html
標籤：應用實例
上一篇：求sql