sql 语句

[worlddba]

-----表a
create table a
(
a nvarchar(50),
c decimal(15,1)
)

-----表a的数据是：
99300659 15
99300686 30
99300650 60
99300655 90
99700627 180
99900539 135
99700513 270
99100568 150
99308461 300

----------------------------表a的结构
create table b
(
d nvarchar(50),
e nvarchar(50),
f decimal(15,1)
)

-----实现的功能是，根据表a的数据向表b插入数据
1）表b的d列随机的数（字母F开头）
2）表b的e列是金额 ，如果小于等于30就插入1条表名所对应的数值，如果是45就插入2条数据一条是30，另一条是15
如此类推
3）表b的f列对应是表a的a列

-------------生成表b中插入数据
F8E56CD66 15.00 99300659
F55182C22 30.00 99300686
F6326C69B 30.00 99300650
FD6CDB637 30.00 99300650
F275C01A1 30.00 99300655
F9A561655 30.00 99300655
F35CEE8C3 30.00 99300655
FAF0250E5 30.00 99700627
F0DEEE35E 30.00 99700627
FB51B8F59 30.00 99700627
F969C6FD4 30.00 99700627
F33F719A9 30.00 99700627
F90275E05 30.00 99700627
F56298727 30.00 99900539
F0FBA43BE 30.00 99900539
F04022C53 30.00 99900539
F02A0FF6B 30.00 99900539
FDF0E4295 15.00 99900539
FFFACB0C5 30.00 99700513
FD386C9D5 30.00 99700513
F0DD4B561 30.00 99700513
FDD416210 30.00 99700513
FBE1D73E7 30.00 99700513
F67CD9C62 30.00 99700513
F4AC770D5 30.00 99700513
FC4E354CD 30.00 99700513
FA5B1AFA0 30.00 99700513
F52FDB5B1 30.00 99100568
F5F8220B8 30.00 99100568
FAD1D12A1 30.00 99100568
FF4648F60 30.00 99100568
F22EE716D 30.00 99100568
F280A0E35 30.00 99308461
FE690FBF4 30.00 99308461
FA6B9E0A5 30.00 99308461
F95EA2612 30.00 99308461
FF1C78E1A 30.00 99308461
FAC9A0E71 30.00 99308461
F15270877 30.00 99308461
F2A5A8691 30.00 99308461
FB07D5F7F 30.00 99308461
F22581B78 30.00 99308461
希望各位朋友帮忙，谢谢！

[-晴天]

create table a ( a nvarchar(50), c decimal(15,1) ) -----表a的数据是： insert into a select '300659',15 insert into a select '300686',30 insert into a select '300650',60 insert into a select '300655',90 insert into a select '700627',180 insert into a select '900539',135 insert into a select '700513',270 insert into a select '100568',150 insert into a select '308461',300 go ;with cte as( select 'F'+left(newid(),8) as d,convert(decimal(18,2),(case when c>30 then 30 else c end)) as e,a as f,convert(decimal(18,2),(case when c>30 then c-30 else 0 end)) as g from a union all select 'F'+left(newid(),8),convert(decimal(18,2),(case when g>30 then 30 else g end)),f,convert(decimal(18,2),(case when g>30 then g-30 else 0 end)) from cte where g>0 )select d,e,f from cte order by f /* d e f ----------------- --------------------------------------- -------------------------------------------------- F65AC080C 30.00 100568 FC48FC085 30.00 100568 F4676037E 30.00 100568 FD0911E9A 30.00 100568 FF57A4DCC 30.00 100568 F0E17FBAF 30.00 300650 F41D02603 30.00 300650 FD8B76B3E 30.00 300655 F1D97AE40 30.00 300655 F74DF71BB 30.00 300655 F649F9051 15.00 300659 F3862BA3D 30.00 300686 FE4860567 30.00 308461 FFFA565B4 30.00 308461 FF1148C78 30.00 308461 F9642F0AE 30.00 308461 FB3E1C3F4 30.00 308461 F92A19AA1 30.00 308461 F2F468E5D 30.00 308461 F72848E2E 30.00 308461 F14E686AF 30.00 308461 F0C2A48CE 30.00 308461 F331575B2 30.00 700513 F50D8AB21 30.00 700513 F73F124A0 30.00 700513 F86B337B4 30.00 700513 FDB0DBCA1 30.00 700513 FCA096EA4 30.00 700513 FBBD16C39 30.00 700513 F857E8CA8 30.00 700513 F15838646 30.00 700513 F65D2E0D0 30.00 700627 F54DED6F2 30.00 700627 F4159EBB5 30.00 700627 FA95F4659 30.00 700627 FD505CA6B 30.00 700627 F08C38221 30.00 700627 F024129C1 30.00 900539 F02E4FC2E 30.00 900539 FFA9C182A 30.00 900539 F1DCBCF60 30.00 900539 FECE20852 15.00 900539 (42 行受影响) */ go drop table a

[还在加载中灬]

这个我想用两个查询应该可以做的 你用的是MSSQL的版本是？

[-晴天]

create table a ( a nvarchar(50), c decimal(15,1) ) -----表a的数据是： insert into a select '99300659',15 insert into a select '99300686',30 insert into a select '99300650',60 insert into a select '99300655',90 insert into a select '99700627',180 insert into a select '99900539',135 insert into a select '99700513',270 insert into a select '99100568',150 insert into a select '99308461',300 go ;with cte as( select 'F'+left(newid(),8) as d,convert(decimal(18,2),(case when c>30 then 30 else c end)) as e,a as f,convert(decimal(18,2),(case when c>30 then c-30 else 0 end)) as g from a union all select 'F'+left(newid(),8),convert(decimal(18,2),(case when g>30 then 30 else g end)),f,convert(decimal(18,2),(case when g>30 then g-30 else 0 end)) from cte where g>0 )select d,e,f from cte order by f /* d e f ----------------- --------------------------------------- -------------------------------------------------- F3FC6EF51 30.00 99100568 FB39E52F5 30.00 99100568 FD1801307 30.00 99100568 F4AC6FBF2 30.00 99100568 F0F71C5C8 30.00 99100568 F81ED563A 30.00 99300650 F19B5EAA5 30.00 99300650 F20F4993B 30.00 99300655 F5F700DE8 30.00 99300655 FAC6FA6E0 30.00 99300655 FBA1A7A4D 15.00 99300659 FDC8872BA 30.00 99300686 F6B5CCAEF 30.00 99308461 F8D156E7D 30.00 99308461 F48F2E642 30.00 99308461 F1FAC30A5 30.00 99308461 F7799CBE4 30.00 99308461 F05B1FA8E 30.00 99308461 FFA9A9061 30.00 99308461 F420569BA 30.00 99308461 F3042DC7C 30.00 99308461 F8C3BFF2C 30.00 99308461 FEE83D0DB 30.00 99700513 FF875F474 30.00 99700513 FD6365F03 30.00 99700513 F6C295200 30.00 99700513 FAC58E684 30.00 99700513 FFB318A5B 30.00 99700513 F295C59FA 30.00 99700513 F00CBFED7 30.00 99700513 F567520B6 30.00 99700513 F3FFE0AC6 30.00 99700627 FC79D0348 30.00 99700627 FC0D9145B 30.00 99700627 FBD3C3BFF 30.00 99700627 F2D7673BF 30.00 99700627 FBF61187B 30.00 99700627 F090125AD 30.00 99900539 F8C60965B 30.00 99900539 F3C3FADD6 30.00 99900539 FD92EF510 30.00 99900539 FB20CB3E2 15.00 99900539 (42 行受影响) */ go drop table a

[还在加载中灬]

引用 7 楼 qianjin036a 的回复:

……

大神出手就是快，我还想问下楼主CTE能不能用

[worlddba]

引用 6 楼 ky_min 的回复:

这个我想用两个查询应该可以做的 你用的是MSSQL的版本是？

sql server 2005

[worlddba]

引用 7 楼 qianjin036a 的回复:

create table a ( a nvarchar(50), c decimal(15,1) ) -----表a的数据是： insert into a select '99300659',15 insert into a select '99300686',30 insert into a select '99300650',60 insert in……

谢谢朋友

[还在加载中灬]

引用 9 楼 worlddba 的回复:

sql server 2005

哦，那qianjin036a大神 已解决此问题了

[worlddba]

最好不用游标，谢谢！

[newtee]

20分的代码

[worlddba]

[worlddba]