No drug number of times a day each time the dosage
1 10 medicine A 50 mg 2
1 medicine B 10 20 mg 2
15 mg 2 medicine C 5 1
Medicine 3 D 3 25 mg 1
Now through a SQL statement, generate the following results
No drug name number every time the dosage
Medicine A 5 50 mg 1
1 medicine 5 B 20 mg
2 medicine C 5 to 15 mg
3 D medicine 3 25 mg
4 medicine A 5 50 mg
4 drug B 5 20 mg
Ask everybody to give directions, thank you
CodePudding user response:
DECLARE @ t TABLE (no INT the NOT NULL, drug name NVARCHAR (100) NOT NULL, a DECIMAL number (1, 2) NOT NULL, every times INT the NOT NULL, the dosage of each NVARCHAR (100) NOT NULL)
DECLARE @ out TABLE (group INT the NOT NULL, drug name NVARCHAR (100) NOT NULL, a DECIMAL number (1, 2) NOT NULL, the dosage of each NVARCHAR (100) NOT NULL)
INSERT @ t (group, drug name, number, number every day, every time the dosage)
VALUES (1, 'A', 10, 2, '50 mg), (1,' B ', 10, 2, '20 mg), (2,' C ', 5, 1, '15 mg), (3,' D ', 3, 1, '25 mg)
WHILE the EXISTS (SELECT 1 FROM @ t WHERE every number & gt; 0)
The BEGIN
INSERT the @ out (group, drug name, quantity, every time the dosage)
A SELECT group of number, drug name, quantity, every time the dosage FROM @ t WHERE every number & gt; 0
The UPDATE @ t SET=number 1 WHERE every day every day number & gt; 0 - what is the rule of group number? Regularly to update along with all the
END
SELECT * FROM @ out
CodePudding user response:
with t as (
Select the as group 1, 'A' as medicine, as the number 10, 2 as, '50 mg as quantity
Union all
Select 1, 'B', 10, 2, '20 mg'
Union all
Select 2, 'C', 5, 1, '15 mg'
Union all
Select 3, 'D', 3, 1, '25 mg'
Union all
Select 4, 'E', 12, 3, '25 mg'
)
, t1 as (
A select group, medicine, number/time as number, quantity, b.n umber
The from t a
Left the join master.. Spt_values b on b.n umber<=a. times and b.n umber> 0 and b.t ype='p'
)
, t2 as (
A select group, the number of groups as n the from t1 group by group, number
), t3 as (
Select * from t2 where number=1
Union all
A select group, the number, (select Max (group) the from t2) + row_number () over (order by group, number) as n the from t2 where number> 1
)
Select a. *, n the from t1 a
Left the join t3 on a. b=b. group and a.n umber=b.n umber
The order by n, medicine
CodePudding user response:
group medicine quantity number n
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
1 A 5 50 mg 1 1
5 20 mg 1 1 1 B
2 C 5 to 15 mg 1 2
3 D 3 25 mg 1 3
4 E 4 25 mg 1 4
1 A 5 50 mg 2
1 B 5 20 mg 2
4 E 25 mg 2 4 6
4 E 4 25 mg 3 7
(9 rows affected)
CodePudding user response:
IF OBJECT_ID (N 'TEMPDB for. DBO. # T') IS NOT NULL
DROP TABLE # T
GO
The 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.N UMBER, c. AX_GROUP_NO
The FROM # T A
JOIN the MASTER. The DBO. SPT_VALUES B ON A.F REQUENCY>=B.N UMBER
The JOIN (SELECT MAX (GROUP_NO) AS MAX_GROUP_NO FROM # T) C=1 ON 1
WHERE NUMBER> 0
AND TYPE='P')
SELECT *
The FROM
(SELECT MAX_GROUP_NO + DENSE_RANK () OVER (ORDER BY NUMBER, GROUP_NO) AS GROUP_NO,
PHA_NAME, QTY/FREQUENCY AS QTY
The FROM CTE
WHERE NUMBER> 1
UNION ALL
The SELECT GROUP_NO PHA_NAME, QTY/FREQUENCY FROM CTE
WHERE the NUMBER AS A
=1)The ORDER BY GROUP_NO, PHA_NAME
CodePudding user response:
DECLARE @ t TABLE (no int the NOT NULL, drug name varchar (50) NOT NULL, the number of int the NOT NULL, every times int the NOT NULL, each replacement varchar (50), NOT NULL)
INSERT into @ t
SELECT 1, 'A', 10, 2, '50 mg' UNION ALL
SELECT 1, 'B', 10, 2, '20 mg' UNION ALL
SELECT 2, C 'medicine', 5, 1, '15 mg' UNION ALL
SELECT 3, 'D', 3, 1, '25 mg'
A SELECT group of number,
Drug name,
CASE
WHEN a day=0 THEN 0
The ELSE (quantity/times a day)
END the AS number,
Every time change
The FROM @ t