create table #u(uid int,sum1 int,sum2 int)
insert into #u values (1,2,3)
insert into #u values (2,1,0)
insert into #u values (3,0,0)
insert into #u values (4,1,2)
insert into #u values (5,0,0)
insert into #u values (6,0,0)
insert into #u values (7,0,0)create table #m(mid int,uid int,tid int,ind int,vOrder int)
insert into #m values (1,1,11,0,0)
insert into #m values (2,1,11,1,1)
insert into #m values (3,1,11,1,2)
insert into #m values (4,4,12,0,0)
insert into #m values (5,4,12,1,1)
insert into #m values (6,4,12,1,2)
insert into #m values (7,1,12,1,3)
insert into #m values (8,1,13,0,0)
insert into #m values (9,2,14,0,0)create table #s(mid int)
insert into #s values (10)
insert into #s values (11)create table #t(tid int,mid int)
insert into #t values (11,10)
insert into #t values (12,10)
insert into #t values (13,10)
insert into #t values (14,11)--SQL语句如下：
SELECT * FROM #u;
SELECT * FROM #m;
SELECT * FROM #s;
SELECT * FROM #t;SELECT count(case ind when 0 then 0 end) as sum1,count(case ind when 1 then 1 end) as sum2, uid FROM #m group by uid;
--删除测试
drop table #u,#m,#s,#t;
/*uid sum1 sum2
----------- ----------- -----------
1 2 3
2 1 0
3 0 0
4 1 2
5 0 0
6 0 0
7 0 0(7 行受影响)mid uid tid ind vOrder
----------- ----------- ----------- ----------- -----------
1 1 11 0 0
2 1 11 1 1
3 1 11 1 2
4 4 12 0 0
5 4 12 1 1
6 4 12 1 2
7 1 12 1 3
8 1 13 0 0
9 2 14 0 0(9 行受影响)mid
-----------
10
11(2 行受影响)tid mid
----------- -----------
11 10
12 10
13 10
14 11(4 行受影响)sum1 sum2 uid
----------- ----------- -----------
2 3 1
1 0 2
1 2 4
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)要求执行“Delete From [#m] Where mid = 1 and ind=0 后 uid sum1 sum2
----------- ----------- -----------
1 1 1
2 1 0
3 0 0
4 1 2
5 0 0
6 0 0
7 0 0(7 行受影响)mid uid tid ind vOrder
----------- ----------- ----------- ----------- -----------
4 4 12 0 0
5 4 12 1 1
6 4 12 1 2
7 1 12 1 3
8 1 13 0 0
9 2 14 0 0(6 行受影响)mid
-----------
10
11(2 行受影响)tid mid
----------- -----------
12 10
13 10
14 11(3 行受影响)sum1 sum2 uid
----------- ----------- -----------
1 1 1
1 0 2
1 2 4
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)要求执行“Delete From [#m] Where mid = 2 and ind=1 后 uid sum1 sum2
----------- ----------- -----------
1 2 2
2 1 0
3 0 0
4 1 2
5 0 0
6 0 0
7 0 0(7 行受影响)mid uid tid ind vOrder
----------- ----------- ----------- ----------- -----------
1 1 11 0 0
3 1 11 1 1
4 4 12 0 0
5 4 12 1 1
6 4 12 1 2
7 1 12 1 3
8 1 13 0 0
9 2 14 0 0(8 行受影响)mid
-----------
10
11(2 行受影响)tid mid
----------- -----------
11 10
12 10
13 10
14 11(4 行受影响)sum1 sum2 uid
----------- ----------- -----------
2 2 1
1 0 2
1 2 4
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)*/
共二个要求：
一、要求执行“Delete From [#m] Where mid = 1 and ind=0 后
二、要求执行“Delete From [#m] Where mid = 2 and ind=1 后 （涉及到删除后vOrder字段重新排序的问题）可参考
http://topic.csdn.net/u/20090306/20/85b36020-b8a1-4b50-b6d0-c260257204f5.html不是本人不动脑，实在是不感冒。
要求执行“Delete From [#m] Where mid = 2 and ind=1 后 uid sum1 sum2
----------- ----------- -----------
1 2 2
2 1 0
3 0 0
4 1 2
5 0 0
6 0 0
7 0 0(7 行受影响)mid uid tid ind vOrder
----------- ----------- ----------- ----------- -----------
1 1 11 0 0
3 1 11 1 1
4 4 12 0 0
5 4 12 1 1
6 4 12 1 2
7 1 12 1 3
8 1 13 0 0
9 2 14 0 0(8 行受影响)mid
-----------
10
11(2 行受影响)tid mid
----------- -----------
11 10
12 10
13 10
14 11(4 行受影响)sum1 sum2 uid
----------- ----------- -----------
2 2 1
1 0 2
1 2 4
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
要求执行“Delete From [#m] Where mid = 1 and ind=0 后 uid sum1 sum2
----------- ----------- -----------
1 1 1
2 1 0
3 0 0
4 1 2
5 0 0
6 0 0
7 0 0(7 行受影响)mid uid tid ind vOrder
----------- ----------- ----------- ----------- -----------
4 4 12 0 0
5 4 12 1 1
6 4 12 1 2
7 1 12 1 3
8 1 13 0 0
9 2 14 0 0(6 行受影响)mid
-----------
10
11(2 行受影响)tid mid
----------- -----------
12 10
13 10
14 11(3 行受影响)sum1 sum2 uid
----------- ----------- -----------
1 1 1
1 0 2
1 2 4
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
要求执行“Delete From [#m] Where mid = 2 and ind=1 后 uid sum1 sum2
----------- ----------- -----------
1 2 2
2 1 0
3 0 0
4 1 2
5 0 0
6 0 0
7 0 0(7 行受影响)mid uid tid ind vOrder
----------- ----------- ----------- ----------- -----------
1 1 11 0 0
3 1 11 1 1
4 4 12 0 0
5 4 12 1 1
6 4 12 1 2
7 1 12 1 3
8 1 13 0 0
9 2 14 0 0(8 行受影响)mid
-----------
10
11(2 行受影响)tid mid
----------- -----------
11 10
12 10
13 10
14 11(4 行受影响)sum1 sum2 uid
----------- ----------- -----------
2 2 1
1 0 2
1 2 4
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
要求执行“Delete From [#m] Where mid = 1 and ind=0 后 uid sum1 sum2
----------- ----------- -----------
1 1 1
2 1 0
3 0 0
4 1 2
5 0 0
6 0 0
7 0 0(7 行受影响)mid uid tid ind vOrder
----------- ----------- ----------- ----------- -----------
4 4 12 0 0
5 4 12 1 1
6 4 12 1 2
7 1 12 1 3
8 1 13 0 0
9 2 14 0 0(6 行受影响)mid
-----------
10
11(2 行受影响)tid mid
----------- -----------
12 10
13 10
14 11(3 行受影响)sum1 sum2 uid
----------- ----------- -----------
1 1 1
1 0 2
1 2 4
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
From [#m] Where mid = 1 and ind=0
或
From [#m] Where mid = 2 and ind=1
再根据各表的关联字段执行