27,579
社区成员
发帖
与我相关
我的任务
分享SQL Server 数据库中数据拆分
atf_ypxx 医嘱表
字段表
药品编码 药品名称 姓名 药品数量
drug_code drugname name amount
1 aaa 张三 36
2 bbb 张三 36
3 ccc 李四 36
atf_cas 药品表
药品编码 药品上限
drug_code order_max
1 10
2 10
3 10
最后的结果是：
drug_code drugname name amount
1 aaa 张三 10
1 aaa 张三 10
1 aaa 张三 10
1 aaa 张三 6
2 bbb 张三 10
2 bbb 张三 10
2 bbb 张三 10
2 bbb 张三 6
3 ccc 李四 10
3 ccc 李四 10
3 ccc 李四 10
3 ccc 李四 6
字段表
药品编码 药品名称 姓名 药品数量
drug_code drugname name amount
1 aaa 张三 36
2 bbb 张三 36
3 ccc 李四 36
atf_cas 药品表
药品编码 药品上限
drug_code order_max
1 10
2 10
3 10
最后的结果是：
drug_code drugname name amount
1 aaa 张三 10
1 aaa 张三 10
1 aaa 张三 10
1 aaa 张三 6
2 bbb 张三 10
2 bbb 张三 10
2 bbb 张三 10
2 bbb 张三 6
3 ccc 李四 10
3 ccc 李四 10
3 ccc 李四 10
3 ccc 李四 6
...全文
请发表友善的回复…
发表回复
霜寒月冷 2014-09-09
- 打赏
- 举报
在加一层就可以了
select * from
(
select c.drug_code,
c.drugname,
c.name,
case when d.number<=c.t then c.order_max
else c.y end 'amount'
from
(select a.drug_code,a.drugname,a.name,b.order_max,
a.amount/b.order_max 't',
a.amount%b.order_max 'y'
from atf_ypxx a
inner join atf_cas b on a.drug_code=b.drug_code) c
cross join master.dbo.spt_values d
where d.type='P' and d.number between 1 and c.t+1 )t
where t .amount<>0
wtujedp 2014-09-09
- 打赏
- 举报
刚才试了一下。如果刚才刚可以整除的话，会多一行0的记录。例：把36 改成40，限制改成10
pengpeng409 2014-09-09
- 打赏
- 举报
好的，谢谢大家。
木鸟飞木鸟飞 2014-09-09
- 打赏
- 举报
唐诗三百首 2014-09-08
- 打赏
- 举报
create table atf_ypxx
(drug_code int,drugname varchar(10),name varchar(10),amount int)
insert into atf_ypxx
select 1,'aaa','张三',36 union all
select 2,'bbb','张三',36 union all
select 3,'ccc','李四',36 union all
select 4,'eee','王五',19
create table atf_cas
(drug_code int,order_max int)
insert into atf_cas
select 1,10 union all
select 2,10 union all
select 3,10 union all
select 4,3
select c.drug_code,
c.drugname,
c.name,
case when d.number<=c.t then c.order_max
else c.y end 'amount'
from
(select a.drug_code,a.drugname,a.name,b.order_max,
a.amount/b.order_max 't',
a.amount%b.order_max 'y'
from atf_ypxx a
inner join atf_cas b on a.drug_code=b.drug_code) c
cross join master.dbo.spt_values d
where d.type='P' and d.number between 1 and c.t+1
/*
drug_code drugname name amount
----------- ---------- ---------- -----------
1 aaa 张三 10
1 aaa 张三 10
1 aaa 张三 10
1 aaa 张三 6
2 bbb 张三 10
2 bbb 张三 10
2 bbb 张三 10
2 bbb 张三 6
3 ccc 李四 10
3 ccc 李四 10
3 ccc 李四 10
3 ccc 李四 6
4 eee 王五 3
4 eee 王五 3
4 eee 王五 3
4 eee 王五 3
4 eee 王五 3
4 eee 王五 3
4 eee 王五 1
(19 行受影响)
*/
注: LZ所提供测试数据的数量都是36,上限都是10,结果都是拆为4笔记录(10,10,10,6).无法测试出同一写法在不同情况下的结果. 故本例新增一笔王五的测试数据.
习惯性蹭分 2014-09-08
- 打赏
- 举报
use tempdb
if object_id('atf_ypxx') is not null
drop table atf_ypxx
if object_id('atf_cas') is not null
drop table atf_cas
create table atf_ypxx (drug_code int not null primary key,
drugName varchar(20),
name varchar(20),
amount int)
create table atf_cas(drug_code int,order_max int)
insert into atf_ypxx
select 1,'aaa','张三',36 union all
select 2,'bbb','张三',36 union all
select 3,'ccc','李四',36
insert into atf_cas
select 1,10 union all
select 2,10 union all
select 3, 10
;with sel as(
select a.drug_code,a.drugName,a.name,b.order_max amount,floor(a.amount/b.order_max) as num
from atf_ypxx a
join atf_cas b on a.drug_code=b.drug_code
where a.amount>b.order_max
union all
select a.drug_code,a.drugName,a.name, b.order_max,a.num-1 as num from sel a
join atf_cas b on a.drug_code=b.drug_code
where num>1
)
select drug_code,drugName,name,amount from sel
union all
select a.drug_code,a.drugName,a.name,a.amount%b.order_max
from atf_ypxx a
join atf_cas b on a.drug_code=b.drug_code
where a.amount%b.order_max>0
order by drug_code,amount desc
xiaodongni 2014-09-08
- 打赏
- 举报
前面代码有点问题。用取模有点问题。如果干好整除最好一行就成0了。
with atf_ypxx (drug_code,drugname,name,amount) as
(select 1,'aaa','张三',36 union all
select 2,'bbb','张三',36 union all
select 3,'ccc','李四',36 ),
atf_cas(drug_code,order_max) as
(select 1,10 union all
select 2,10 union all
select 3,10 ),
cte1 as
(select t1.drug_code,t1.drugname,t1.name,t2.order_max,t1.amount from atf_ypxx as t1 join atf_cas as t2 on
t1.drug_code=t2.drug_code ),
cte as
(select drug_code,drugname,name,order_max,amount from cte1
union all
select a.drug_code,a.drugname,a.name,a.order_max+b.order_max as order_max,a.amount
from cte1 as a join cte as b on a.drug_code=b.drug_code
where b.order_max< a.amount
)
select c1.drug_code,c1.drugname,c1.name,
case when c1.order_max =(select MAX(order_max) from cte as c3 where c1.drug_code=c3.drug_code)
then c2.amount+c2.order_max-c1.order_max
else c2.order_max
end as amount
from cte as c1 join cte1 as c2 on c1.drug_code=c2.drug_code
order by c1.drug_code,amount desc
xiaodongni 2014-09-08
- 打赏
- 举报
with atf_ypxx (drug_code,drugname,name,amount) as
(select 1,'aaa','张三',36 union all
select 2,'bbb','张三',36 union all
select 3,'ccc','李四',36 ),
atf_cas(drug_code,order_max) as
(select 1,10 union all
select 2,10 union all
select 3,10 ),
cte1 as
(select t1.drug_code,t1.drugname,t1.name,t2.order_max,t1.amount from atf_ypxx as t1 join atf_cas as t2 on
t1.drug_code=t2.drug_code ),
cte as
(select drug_code,drugname,name,order_max,amount from cte1
union all
select a.drug_code,a.drugname,a.name,a.order_max+b.order_max as order_max,a.amount
from cte1 as a join cte as b on a.drug_code=b.drug_code
where b.order_max< a.amount
)
select drug_code,drugname,name,
case when order_max =(select MAX(order_max) from cte
as c2 where c2.drug_code=c1.drug_code )
then c1.amount%(select min(order_max) from cte
as c2 where c2.drug_code=c1.drug_code )
else (select min(order_max) from cte
as c2 where c2.drug_code=c1.drug_code)
end as amount
from cte as c1
order by drug_code,amount desc
drug_code drugname name amount
----------- -------- ---- -----------
1 aaa 张三 10
1 aaa 张三 10
1 aaa 张三 10
1 aaa 张三 6
2 bbb 张三 10
2 bbb 张三 10
2 bbb 张三 10
2 bbb 张三 6
3 ccc 李四 10
3 ccc 李四 10
3 ccc 李四 10
3 ccc 李四 6
(12 行受影响)
--小F-- 2014-09-08
- 打赏
- 举报
可以用CTE或者MASTER..SPT_VALUES系统表来构造，没环境智能提供个思路。
boybang 2014-09-08
- 打赏
- 举报
一个很简单的内联查询而已，别想太多。atf_ypxx 医嘱表作为主表，atf_cas 药品表作为关联表，关联条件为药品编码 drug_code。
pengpeng409 2014-09-08
- 打赏
- 举报
感觉好像要用数组，又不会用数组
pengpeng409 2014-09-08
- 打赏
- 举报
请大家帮忙看一下需要怎么拆分，谢谢
