t表有3个字段,a,b,c表中的数据是
2,1,2
3,1,2
3,2,3
4,2,1想得到的是
2,1,1
2,1,2
3,1,1
3,1,2
3,2,3
3,2,4
3,2,5
4,2,1说明一下,
从原始数据
2,1,2
希望得出
2,1,1
2,1,2这样2条从原始数据
3,1,2
3,2,3
希望得出
3,1,1
3,1,2
3,2,3
3,2,4
3,2,5
这样5条从原始数据
4,2,1
希望得出
4,2,2(这个2,是原始数据2×1的结果)
这样一条有难度啊,,,,,,无弄不明白啊···求助!
2,1,2
3,1,2
3,2,3
4,2,1想得到的是
2,1,1
2,1,2
3,1,1
3,1,2
3,2,3
3,2,4
3,2,5
4,2,1说明一下,
从原始数据
2,1,2
希望得出
2,1,1
2,1,2这样2条从原始数据
3,1,2
3,2,3
希望得出
3,1,1
3,1,2
3,2,3
3,2,4
3,2,5
这样5条从原始数据
4,2,1
希望得出
4,2,2(这个2,是原始数据2×1的结果)
这样一条有难度啊,,,,,,无弄不明白啊···求助!
解决方案 »
- sql server 2008 最大并发连接数
- sqlserver 2005 xml支持?
- 菜鸟问题,怎么把多个表的数据弄到一个表里面
- SQL无法自动备份?
- sqlServer 调用外部DLL 函数 问题???
- 请问:修改表名的T-Sql语句是什么?
- 我的数据库大的很不正常,是什么原因造成的啊?
- SQL-SERVER2000 到本地Access的问题.
- 带标识(自动递增)字段的表如何合并?
- 我用Select distinct Top 100...来取得查询结果的第100条记录,但整个查询结果不止这么多,请问如何取得下面100条的记录出来呢?
- 一个sql语句查询问题
- SQL SERVER能存放文件么?
create table t(a int,b int,c int)
insert into t
select 2,1,2 union all
select 3,1,2 union all
select 3,2,3 union all
select 4,2,1
goselect a.a,a.b,b.number
from t a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and a.cdrop table t/************a b number
----------- ----------- -----------
2 1 1
2 1 2
3 1 1
3 1 2
3 2 1
3 2 2
3 2 3
4 2 1(8 行受影响)
a INT
,b INT
,c INT
)
INSERT @T SELECT
2,1,2
UNION ALL SELECT
3,1,2
UNION ALL SELECT
3,2,3
UNION ALL SELECT
4,2,1;WITH CTE AS (
SELECT * FROM @T
UNION ALL
SELECT A,B,C=C-1 FROM CTE
WHERE C>1
)
SELECT * FROM CTE
ORDER BY A,B,C--结果
a b c
2 1 1
2 1 2
3 1 1
3 1 2
3 2 1
3 2 2
3 2 3
4 2 1
create table t(a int,b int,c int)
insert into t
select 2,1,2 union all
select 3,1,2 union all
select 3,2,3 union all
select 4,2,1
go;with AcHerat as
(
select a.a,a.b,1 as c,row_number() over (order by getdate()) rid
from t a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and a.c
)select a,b,(select sum(c) from AcHerat where rid <= t.rid and a = t.a) c
from AcHerat tdrop table t/***************a b c
----------- ----------- -----------
2 1 1
2 1 2
3 1 1
3 1 2
3 2 3
3 2 4
3 2 5
4 2 1(8 行受影响)
a INT
,b INT
,c INT
)
INSERT @T SELECT
2,1,2
UNION ALL SELECT
3,1,2
UNION ALL SELECT
3,2,3
UNION ALL SELECT
4,2,1;WITH CTE1 AS (
SELECT A,B,C= (SELECT SUM(C) FROM @T WHERE A=A.A AND B<=A.B) FROM @T A
),
CTE AS (
SELECT * FROM CTE1
UNION ALL
SELECT A,B,C=A.C-1 FROM CTE A
WHERE NOT EXISTS (
SELECT 1 FROM CTE1 WHERE A= A.A AND C=A.C -1
)
AND A.C >1
)
SELECT * FROM CTE
ORDER BY A,B,C--结果
A B C
2 1 1
2 1 2
3 1 1
3 1 2
3 2 3
3 2 4
3 2 5
4 2 1
create table t(a int,b int,c int)
insert into t
select 2,1,2 union all
select 3,1,2 union all
select 3,2,3 union all
select 4,2,1
goselect a.a,a.b,c,1 as d,identity(int,1,1) rid,
(select count(*) from t where a = a.a) num
into #tb
from t a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and a.cselect a,b,
(case when c > 1 then (select sum(d) from #tb where rid <= t.rid and a = t.a)
else c*b end)c
from #tb tdrop table t,#tb/**************a b c
----------- ----------- -----------
2 1 1
2 1 2
3 1 1
3 1 2
3 2 3
3 2 4
3 2 5
4 2 2(8 行受影响)