declare @t table(部门 varchar(10), 商品 varchar(10)) insert @t select 'a' , 'P1' insert @t select 'a' , 'P2' insert @t select 'b' , 'P1' insert @t select 'b' , 'P2' insert @t select 'b' , 'P3' select 行ID=(select count(*)+ 1 from @t where 部门=t.部门 and 商品<t.商品),* from @t t /*行ID 部门 商品 ----------- ---------- ---------- 1 a P1 2 a P2 1 b P1 2 b P2 3 b P3(影響 5 個資料列)*/
DECLARE @TB TABLE(部门 VARCHAR(2), 商品 VARCHAR(10)) INSERT @TB SELECT 'a', 'P1' UNION ALL SELECT 'a', 'P2' UNION ALL SELECT 'b', 'P1' UNION ALL SELECT 'b', 'P2' UNION ALL SELECT 'b', 'P3' SELECT ID=(SELECT COUNT(DISTINCT 部门) FROM @TB WHERE 部门<=A.部门) , ID2=(SELECT COUNT(*) FROM @TB WHERE 部门=A.部门 AND 商品<=A.商品) , * FROM @TB AS A /* ID ID2 部门 商品 ----------- ----------- ---- ---------- 1 1 a P1 1 2 a P2 2 1 b P1 2 2 b P2 2 3 b P3 */
是说这样么 select cnt=(select count(*)+ 1 from tb where 部门=t.部门 and 商品<>t.商品),* from tb t
if object_id('[tb]') is not null drop table [tb] go create table [tb]([部门] varchar(1),[商品] varchar(2)) insert [tb] select 'a','p1' union all select 'a','p2' union all select 'b','p1' union all select 'b','p2' union all select 'b','p3'select * from [tb]select ID=(select count(distinct 部门) from tb where 部门<=a.部门) ,行ID=row_number() over(partition by 部门 order by 商品) ,部门,商品 from tb a--测试结果: /* ID 行ID 部门 商品 ----------- -------------------- ---- ---- 1 1 a p1 1 2 a p2 2 1 b p1 2 2 b p2 2 3 b p3(5 row(s) affected)*/
商品 <t.商品 --------- 看不太明白这是什么意思呢
DECLARE @TB TABLE(部门 VARCHAR(2), 商品 VARCHAR(10)) INSERT @TB SELECT 'a', 'P1' UNION ALL SELECT 'a', 'P2' UNION ALL SELECT 'b', 'P1' UNION ALL SELECT 'b', 'P2' UNION ALL SELECT 'b', 'P3'SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB SELECT ID=(SELECT COUNT(DISTINCT 部门) FROM @TB WHERE 部门<=A.部门), ID2=ID-(SELECT COUNT(*) FROM # WHERE 部门<A.部门) , 部门, 商品 FROM # AS ADROP TABLE # /* ID ID2 部门 商品 ----------- ----------- ---- ---------- 1 1 a P1 1 2 a P2 2 1 b P1 2 2 b P2 2 3 b P3 */
DECLARE @t TABLE(ID INT,rowid INT,dept VARCHAR(10),product VARCHAR(10)) INSERT @t (dept,product) SELECT 'a', 'P1' UNION ALL SELECT 'a' , 'P2' UNION ALL SELECT 'b' , 'P1' UNION ALL SELECT 'b' , 'P2' UNION ALL SELECT 'b', 'P3' DECLARE @i INT,@r INT,@d VARCHAR(10)SET @i=0 SET @r=0 SET @d=''UPDATE @t SET @i=id=CASE WHEN @d=dept THEN 0 ELSE 1 END + @i,@r=rowid=ISNULL((CASE WHEN @d=dept THEN @r ELSE null END),0)+1,@d=deptSELECT * FROM @t/* 1 1 a P1 1 2 a P2 2 1 b P1 2 2 b P2 2 3 b P3 */
另:部门那个字段是varchar(18)
sql2005 DECLARE @t TABLE(ID INT,rowid INT,dept VARCHAR(10),product VARCHAR(10)) INSERT @t (dept,product) SELECT 'a', 'P1' UNION ALL SELECT 'a' , 'P2' UNION ALL SELECT 'b' , 'P1' UNION ALL SELECT 'b' , 'P2' UNION ALL SELECT 'b', 'P3' DECLARE @i INT,@r INT,@d VARCHAR(10)SET @i=0 SET @r=0 SET @d=''SELECT DENSE_RANK() OVER(ORDER BY dept) id, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY product) rowid, dept,product FROM @t/* 1 1 a P1 1 2 a P2 2 1 b P1 2 2 b P2 2 3 b P3 */--更新 UPDATE a SET a.id=b.id ,a.rowid = b.rowid FROM @t a INNER JOIN ( SELECT DENSE_RANK() OVER(ORDER BY dept) id, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY product) rowid, dept,product FROM @t ) b ON a.dept = b.dept AND a.product = b.productSELECT * FROM @t/* 1 1 a P1 1 2 a P2 2 1 b P1 2 2 b P2 2 3 b P3 */
insert @t select 'a' , 'P1'
insert @t select 'a' , 'P2'
insert @t select 'b' , 'P1'
insert @t select 'b' , 'P2'
insert @t select 'b' , 'P3'
select 行ID=(select count(*)+ 1 from @t where 部门=t.部门 and 商品<t.商品),* from @t t
/*行ID 部门 商品
----------- ---------- ----------
1 a P1
2 a P2
1 b P1
2 b P2
3 b P3(影響 5 個資料列)*/
INSERT @TB
SELECT 'a', 'P1' UNION ALL
SELECT 'a', 'P2' UNION ALL
SELECT 'b', 'P1' UNION ALL
SELECT 'b', 'P2' UNION ALL
SELECT 'b', 'P3'
SELECT ID=(SELECT COUNT(DISTINCT 部门) FROM @TB WHERE 部门<=A.部门) ,
ID2=(SELECT COUNT(*) FROM @TB WHERE 部门=A.部门 AND 商品<=A.商品) ,
*
FROM @TB AS A
/*
ID ID2 部门 商品
----------- ----------- ---- ----------
1 1 a P1
1 2 a P2
2 1 b P1
2 2 b P2
2 3 b P3
*/
select cnt=(select count(*)+ 1 from tb where 部门=t.部门 and 商品<>t.商品),* from tb t
go
create table [tb]([部门] varchar(1),[商品] varchar(2))
insert [tb]
select 'a','p1' union all
select 'a','p2' union all
select 'b','p1' union all
select 'b','p2' union all
select 'b','p3'select * from [tb]select ID=(select count(distinct 部门) from tb where 部门<=a.部门)
,行ID=row_number() over(partition by 部门 order by 商品)
,部门,商品
from tb a--测试结果:
/*
ID 行ID 部门 商品
----------- -------------------- ---- ----
1 1 a p1
1 2 a p2
2 1 b p1
2 2 b p2
2 3 b p3(5 row(s) affected)*/
---------
看不太明白这是什么意思呢
INSERT @TB
SELECT 'a', 'P1' UNION ALL
SELECT 'a', 'P2' UNION ALL
SELECT 'b', 'P1' UNION ALL
SELECT 'b', 'P2' UNION ALL
SELECT 'b', 'P3'SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB
SELECT ID=(SELECT COUNT(DISTINCT 部门) FROM @TB WHERE 部门<=A.部门),
ID2=ID-(SELECT COUNT(*) FROM # WHERE 部门<A.部门) ,
部门,
商品
FROM # AS ADROP TABLE #
/*
ID ID2 部门 商品
----------- ----------- ---- ----------
1 1 a P1
1 2 a P2
2 1 b P1
2 2 b P2
2 3 b P3
*/
INSERT @t (dept,product) SELECT 'a', 'P1'
UNION ALL SELECT 'a' , 'P2'
UNION ALL SELECT 'b' , 'P1'
UNION ALL SELECT 'b' , 'P2'
UNION ALL SELECT 'b', 'P3'
DECLARE @i INT,@r INT,@d VARCHAR(10)SET @i=0
SET @r=0
SET @d=''UPDATE @t SET @i=id=CASE WHEN @d=dept THEN 0 ELSE 1 END + @i,@r=rowid=ISNULL((CASE WHEN @d=dept THEN @r ELSE null END),0)+1,@d=deptSELECT * FROM @t/*
1 1 a P1
1 2 a P2
2 1 b P1
2 2 b P2
2 3 b P3
*/
DECLARE @t TABLE(ID INT,rowid INT,dept VARCHAR(10),product VARCHAR(10))
INSERT @t (dept,product) SELECT 'a', 'P1'
UNION ALL SELECT 'a' , 'P2'
UNION ALL SELECT 'b' , 'P1'
UNION ALL SELECT 'b' , 'P2'
UNION ALL SELECT 'b', 'P3'
DECLARE @i INT,@r INT,@d VARCHAR(10)SET @i=0
SET @r=0
SET @d=''SELECT DENSE_RANK() OVER(ORDER BY dept) id,
ROW_NUMBER() OVER(PARTITION BY dept ORDER BY product) rowid,
dept,product FROM @t/*
1 1 a P1
1 2 a P2
2 1 b P1
2 2 b P2
2 3 b P3
*/--更新
UPDATE a SET a.id=b.id ,a.rowid = b.rowid
FROM @t a
INNER JOIN
(
SELECT DENSE_RANK() OVER(ORDER BY dept) id,
ROW_NUMBER() OVER(PARTITION BY dept ORDER BY product) rowid,
dept,product FROM @t
) b
ON a.dept = b.dept AND a.product = b.productSELECT * FROM @t/*
1 1 a P1
1 2 a P2
2 1 b P1
2 2 b P2
2 3 b P3
*/