现在出现这种情况
SQL2005 新增函数有很多
表
ITEMID QTY
091601 5
091601 10
091601 5
091602 10
091603 3
091603 5达到效果:
ITEMID QTY LineID
091601 5 1-1
091601 10 1-2
091601 5 1-3
091602 10 2-1
091603 3 3-1
091603 5 3-2有没有直接得到汇总的序列的函数哦 。学习中
SQL2005 新增函数有很多
表
ITEMID QTY
091601 5
091601 10
091601 5
091602 10
091603 3
091603 5达到效果:
ITEMID QTY LineID
091601 5 1-1
091601 10 1-2
091601 5 1-3
091602 10 2-1
091603 3 3-1
091603 5 3-2有没有直接得到汇总的序列的函数哦 。学习中
解决方案 »
- 江湖救急,SQL怎么进行“字符串”的转化?
- sqlserver2000和2008,BCP和BULK INSERT的问题
- 如何用变量来指定字段名
- 查询某个用户所拥有的产品
- 中秋了,提两个问题:1听说游标性能很差,FAST_FORWARD可以提高性能吗?
- 如何用sql 语句取出发布复制中以发布的所有<发布的名称>
- 请教大侠们一个执行select语句的问题
- SQL语法错误,请各位指点!
- SQL问题,可不可以用group by 对一个字符型的字段进行累加了?????在线等!!!!!
- SQL7.0中更改了表结构(如字段类型),需要重建相关的view、存储过程和触发器吗?
- 从a表得到b表那样的结构如何实现?
- 求not exists 的sql语句
INSERT @T SELECT '091601' ,5
INSERT @T SELECT '091601' ,10
INSERT @T SELECT '091601' ,5
INSERT @T SELECT '091602' ,10
INSERT @T SELECT '091603' , 3
INSERT @T SELECT '091603', 5
SELECT ITEMID,QTY,
LTRIM(DENSE_RANK( ) OVER(ORDER BY ITEMID))+'-'+
LTRIM(ROW_NUMBER() OVER(PARTITION BY ITEMID ORDER BY GETDATE())) FROM @T
/*ITEMID QTY
---------- ----------- -------------------------------------------------
091601 5 1-1
091601 10 1-2
091601 5 1-3
091602 10 2-1
091603 3 3-1
091603 5 3-2(6 個資料列受到影響)*/
from @T1 ITEMID QTY LineID
---------- ----------- ----------
091601 5 1-1
091601 5 1-2
091601 10 1-3
091602 10 2-1
091603 3 3-1
091603 5 3-2(6 行受影响)
DECLARE @T TABLE(ITEMID VARCHAR(10),QTY INT)
INSERT @T SELECT '091601' ,5
INSERT @T SELECT '091601' ,10
INSERT @T SELECT '091601' ,5
INSERT @T SELECT '091602' ,10
INSERT @T SELECT '091603' , 3
INSERT @T SELECT '091603', 5
SELECT ITEMID,QTY,
LTRIM(DENSE_RANK( ) OVER(ORDER BY ITEMID))+'-'+
LTRIM(ROW_NUMBER() OVER(PARTITION BY ITEMID ORDER BY GETDATE())) as LineID FROM @T
/*ITEMID QTY LineID
---------- ----------- -------------------------------------------------
091601 5 1-1
091601 10 1-2
091601 5 1-3
091602 10 2-1
091603 3 3-1
091603 5 3-2(6 個資料列受到影響)*/
insert @T1
SELECT '091601',5 UNION ALL
SELECT '091601',10 UNION ALL
SELECT '091601',5 UNION ALL
SELECT '091602',10 UNION ALL
SELECT '091603',3 UNION ALL
SELECT '091603',5select *,LineID=ltrim(dense_rank() over(order by ITEMID))+'-'
+ltrim(row_number() over(partition by ITEMID order by QTY))
from @T1 ITEMID QTY LineID
---------- ----------- ----------
091601 5 1-1
091601 5 1-2
091601 10 1-3
091602 10 2-1
091603 3 3-1
091603 5 3-2(6 行受影响)
create table T(itemid varchar(10),qty int)insert into T select '091601', 5
insert into T select '091601', 10
insert into T select '091601', 5
insert into T select '091602', 10
insert into T select '091603', 3
insert into T select '091603' , 5GO
select itemid,qty,
Rtrim(DENSE_RANK() over(order by itemid) )
+'-'+Rtrim(row_number() over(partition by itemid order by getdate()) )
from T
/*
091601 5 1-1
091601 10 1-2
091601 5 1-3
091602 10 2-1
091603 3 3-1
091603 5 3-2
*/drop table t
DECLARE @TA TABLE(ITEMID VARCHAR(10),QTY INT)
INSERT INTO @TA
SELECT '091601',5 UNION ALL
SELECT '091601',10 UNION ALL
SELECT '091601',5 UNION ALL
SELECT '091602',10 UNION ALL
SELECT '091603',3 UNION ALL
SELECT '091603',5 SELECT *,ROW_NUMBER() OVER (PARTITION BY ITEMID ORDER BY ITEMID),dense_rank() OVER(ORDER BY itemid)
FROM @TA
DECLARE @TA TABLE(ITEMID VARCHAR(10),QTY INT)
INSERT INTO @TA
SELECT '091601',5 UNION ALL
SELECT '091601',10 UNION ALL
SELECT '091601',5 UNION ALL
SELECT '091602',10 UNION ALL
SELECT '091603',3 UNION ALL
SELECT '091603',5 SELECT *,cast(dense_rank() OVER(ORDER BY itemid) as varchar)+'-'+
cast(ROW_NUMBER() OVER (PARTITION BY ITEMID ORDER BY ITEMID) as varchar)LineID
FROM @TA
IF OBJECT_ID('LI') IS NOT NULL
DROP TABLE LI
go CREATE TABLE LI(ITEMID VARCHAR(10),QTY INT)
INSERT INTO LI(ITEMID,QTY)
SELECT '091601', 5 UNION ALL
SELECT '091601', 10 UNION ALL
SELECT '091601', 5 UNION ALL
SELECT '091602', 10 UNION ALL
SELECT '091603', 3 UNION ALL
SELECT '091603', 5
GO--SQL 语句
SELECT LTRIM(DENSE_RANK() OVER(ORDER BY ITEMID)) + '-' + LTRIM(ROW_NUMBER() OVER(PARTITION BY ITEMID ORDER BY ITEMID)) AS RID,*
FROM LI /*
RID ITEMID QTY
------------------------------------------------- ---------- -----------
1-1 091601 5
1-2 091601 10
1-3 091601 5
2-1 091602 10
3-1 091603 3
3-2 091603 5*/
insert into T select '091601', 10
insert into T select '091601', 5
insert into T select '091602', 10
insert into T select '091603', 3
insert into T select '091603' , 5GO
select itemid,qty,
left(reverse(itemid),1)
+'-'+Rtrim(row_number() over(partition by itemid order by getdate()) )
from Tdrop table t