34,590
社区成员
发帖
与我相关
我的任务
分享看着比较烦的SQL
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)
insert into #m values (1,1,11,0)
insert into #m values (2,1,11,1)
insert into #m values (3,1,11,1)
insert into #m values (4,4,12,0)
insert into #m values (5,4,12,1)
insert into #m values (6,4,12,1)
insert into #m values (7,1,12,1)
insert into #m values (8,1,13,0)
insert into #m values (9,2,14,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)
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
----------- ----------- ----------- -----------
1 1 11 0
2 1 11 1
3 1 11 1
4 4 12 0
5 4 12 1
6 4 12 1
7 1 12 1
8 1 13 0
9 2 14 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
(3 行受影响)
*/
要求执行“Delete From [#s] Where mid = 10后
/*
(1 行受影响)
uid sum1 sum2
----------- ----------- -----------
1 0 0
2 1 0
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0
(7 行受影响)
mid uid tid ind
----------- ----------- ----------- -----------
9 2 14 0
(1 行受影响)
mid
-----------
11
(1 行受影响)
tid mid
----------- -----------
14 11
(1 行受影响)
sum1 sum2 uid
----------- ----------- -----------
1 0 2
(1 行受影响)
*/
...全文
请发表友善的回复…
发表回复
yyixin 2009-03-07
- 打赏
- 举报
谢谢大家，结贴啦
dawugui 2009-03-06
- 打赏
- 举报
[Quote=引用 11 楼 HEROWANG 的回复:]
既然P梁写了，偶就看看
[/Quote].
既然P梁写了，偶就看看
[/Quote].
百年树人 2009-03-06
- 打赏
- 举报
[Quote=引用 8 楼 liangCK 的回复:]
欢迎加入QQ群：31800957
[/Quote]
严禁卖广告...
欢迎加入QQ群：31800957
[/Quote]
严禁卖广告...
让你望见影子的墙 2009-03-06
- 打赏
- 举报
既然P梁写了，偶就看看
新丁11111 2009-03-06
- 打赏
- 举报
呵呵，看着是比较多
yyixin 2009-03-06
- 打赏
- 举报
小梁，你是神吗！
liangCK 2009-03-06
- 打赏
- 举报
欢迎加入QQ群：31800957
liangCK 2009-03-06
- 打赏
- 举报
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)
insert into #m values (1,1,11,0)
insert into #m values (2,1,11,1)
insert into #m values (3,1,11,1)
insert into #m values (4,4,12,0)
insert into #m values (5,4,12,1)
insert into #m values (6,4,12,1)
insert into #m values (7,1,12,1)
insert into #m values (8,1,13,0)
insert into #m values (9,2,14,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语句如下：
DECLARE @mid int;
SET @mid=10
DECLARE @s table(mid int)
DELETE #s
OUTPUT deleted.mid INTO @s
WHERE mid=@mid
DECLARE @t TABLE(tid int)
DELETE A
OUTPUT deleted.tid INTO @t
FROM #t AS A
JOIN @s AS B
ON A.mid=B.mid
DECLARE @m TABLE(uid int,ind int)
DELETE A
OUTPUT deleted.uid,deleted.ind INTO @m
FROM #m AS A
JOIN @t AS B
ON A.tid=B.tid
UPDATE A SET
sum1=A.sum1-B.sum1,
sum2=A.sum2-B.sum2
FROM #u AS A
JOIN (
SELECT
uid,
SUM(CASE WHEN ind=0 THEN 1 ELSE 0 END) AS sum1,
SUM(CASE WHEN ind=1 THEN 1 ELSE 0 END) AS sum2
FROM @m
GROUP BY uid
) AS B
ON A.uid=B.uid
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 0 0
2 1 0
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0
(7 行受影响)
mid uid tid ind
----------- ----------- ----------- -----------
9 2 14 0
(1 行受影响)
mid
-----------
11
(1 行受影响)
tid mid
----------- -----------
14 11
(1 行受影响)
sum1 sum2 uid
----------- ----------- -----------
1 0 2
(1 行受影响)
*/yyixin 2009-03-06
- 打赏
- 举报
[Quote=引用 4 楼 HEROWANG 的回复:]
第一个表中数据是怎么变化的？
[/Quote]
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;
//这个是提示第一个表的数据来源
第一个表中数据是怎么变化的？
[/Quote]
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;
//这个是提示第一个表的数据来源
liangCK 2009-03-06
- 打赏
- 举报
DECLARE @mid int;
SET @mid=10
DECLARE @s table(mid int)
DELETE #s
OUTPUT deleted.mid INTO @s
WHERE mid=@mid
DECLARE @t TABLE(tid int)
DELETE A
OUTPUT deleted.tid INTO @t
FROM #t AS A
JOIN @s AS B
ON A.mid=B.mid
DECLARE @m TABLE(uid int,ind int)
DELETE A
OUTPUT deleted.uid,deleted.ind INTO @m
FROM #m AS A
JOIN @t AS B
ON A.tid=B.tid
UPDATE A SET
sum1=A.sum1-B.sum1,
sum2=A.sum2-B.sum2
FROM #u AS A
JOIN (
SELECT
uid,
SUM(CASE WHEN ind=0 THEN 1 ELSE 0 END) AS sum1,
SUM(CASE WHEN ind=1 THEN 1 ELSE 0 END) AS sum2
FROM @m
GROUP BY uid
) AS B
ON A.uid=B.uid让你望见影子的墙 2009-03-06
- 打赏
- 举报
第一个表中数据是怎么变化的？
yyixin 2009-03-06
- 打赏
- 举报
小梁，请再帮帮，谢谢！
yyixin 2009-03-06
- 打赏
- 举报
想写成存储过程，传入参数为mid，谢谢！
liangCK 2009-03-06
- 打赏
- 举报
就是几个关联的DELETE和UPDATE
