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 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'
SELECT * from @ t
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