產生資料表序號的方式

產生資料表序號的方式
------------------------------------------------------------------------
print '建範例表 #tmp1'
select * into #tmp1
from (
select '1' no, '101' class, 'A' type,'66' a1,'88' a2,'55' a3
union select '2','101','B','77','88','77'
union select '3','101','C','88','78','65'
union select '4','102','A','66','50','56'
union select '5','102','C','77','78','67'
) a
go
print '顯示 #tmp1 '
select * from #tmp1
------------------------------------------------------------------------
-- 4.產生資料表序號的方式 ps.要產生序號 的條件必須是唯一，否則可能產生重覆的序號
------------------------------------------------------------------------
select * from #tmp1
/*
原表
no class type a1 a2 a3
1 101 A 66 88 55
2 101 B 77 88 77
3 101 C 88 78 65
4 102 A 66 50 56
5 102 C 77 78 67

*/
print '4.1 希望依 class, type 的大小 重新產生序號 -- 顯示 可用在 insert '
select a.class, a.type, count(*) no
from #tmp1 a, #tmp1 b
where (a.class+a.type)>=(b.class+b.type)
group by a.class, a.type
order by a.class, a.type
/*
print '4.1 更新序號編號欄位'
update #tmp1 set no = (
select count(*) no
from #tmp1 b
where (#tmp1.class+#tmp1.type)>=(b.class+b.type)
)

*/
select a.class, a.a2
from #tmp1 a
order by a.class, a.a2

print '4.2.1 希望依 class, a2 的大小 重新產生序號 '
select a.class, a.a2, count(*) no
from #tmp1 a, #tmp1 b
where (a.class+a.a2)>=(b.class+b.a2)
group by a.class, a.a2
order by a.class, a.a2
/*
-- class, a2 的大小 發生 重覆的情況，造成重新產生序號異常
101 78 1
101 88 6
102 50 4
102 78 5

*/
print '4.2.2 由於 class, a2 發生重覆 所以要加條件 type 以使其變成 條件唯一'
select a.class, a.a2, count(*) no
from #tmp1 a, #tmp1 b
where (a.class+a.a2+a.type)>=(b.class+b.a2+b.type)
group by a.class, a.a2, a.type
order by a.class, a.a2

-- ps.可以以 group by 先來做 是否 唯一的判斷
print '使用 group by 來判斷 4.2 的兩個例子'
print '4.2.1 檢查'
select a.class, a.a2, count(*) no
from #tmp1 a
group by a.class, a.a2
having count(*)>1
order by a.class, a.a2
/*
101 88 2 -- 重覆資料

*/

print '4.2.2 檢查' select a.class, a.a2, a.type, count(*) no from #tmp1 a group by a.class, a.a2, a.type having count(*)>1
order by a.class, a.a2, a.type
/*
沒有重覆資料

*/
/*
print '4.2.3 更新序號編號欄位'
update #tmp1 set no = (
select count(*) no
from #tmp1 b
where (#tmp1.class+#tmp1.a2+#tmp1.type)>=(b.class+b.a2+b.type)
)

*/
select *
from #tmp1 a
/*
no class type a1 a2 a3
2 101 A 66 88 55
3 101 B 77 88 77
1 101 C 88 78 65
4 102 A 66 50 56
5 102 C 77 78 67

*/