a f d b c e aa bb cc 1 a 1 2 b 4 3 c 5 4 d 3 5 e 6 6 f 2
aa bb cc 1 a 1 2 b 3 3 c 4 4 d 2 5 e 5 变为:aa bb cc 1 a 1 2 b 4 3 c 5 4 d 3 5 e 6 6 f 2 看不懂怎么变的?
--> --> (让你望见影子的墙)生成測試數據
if not object_id('tb') is null drop table tb Go Create table tb([aa] int,[bb] nvarchar(1),[cc] int) Insert tb select 1,N'a',1 union all select 2,N'b',3 union all select 3,N'c',4 union all select 4,N'd',2 union all select 5,N'e',5 Go Select * from tbcreate trigger t1 on tb for insert,update as update tb set cc=cc+1 where cc>=(select cc from inserted) and aa !=(select aa from inserted)insert into tb values(6 ,'f', 2)select * from tb 1 a 1 2 b 4 3 c 5 4 d 3 5 e 6 6 f 2
create trigger t1 on tb for insert,update as update tb set cc=cc+1 where cc>=(select cc from inserted) and aa !=(select aa from inserted)insert into tb values(6 ,'f', 2)select * from tb 1 a 1 2 b 4 3 c 5 4 d 3 5 e 6 6 f 2
declare @tb table (aa int,bb nvarchar(10),cc int) insert into @tb select 1,'a',1 union all select 2,'b',3 union all select 3,'c',4 union all select 4,'d',2 union all select 5,'e',5 --select * from @tb ------------------ select * from @tb a pivot ( sum(cc) for bb in (a,b,c,d,e)) b 1 1 NULL NULL NULL NULL 2 NULL 3 NULL NULL NULL 3 NULL NULL 4 NULL NULL 4 NULL NULL NULL 2 NULL 5 NULL NULL NULL NULL 5
DECLARE @T TABLE ( aa INT, bb VARCHAR(10), cc INT )INSERT @T SELECT 1, 'a', 1 UNION ALL SELECT 2, 'b', 3 UNION ALL SELECT 3, 'c', 4 UNION ALL SELECT 4, 'd', 2 UNION ALL SELECT 5, 'e', 5DECLARE @sql VARCHAR(8000) SET @sql = '' SELECT @sql = @sql + bb + ' ' FROM @T ORDER BY [cc] SELECT @sql
--测试环境 declare @t table(aa int identity(1,1),bb varchar(32),cc varchar(32)) insert into @t(bb,cc) select 'a','1' union all select 'b','3' union all select 'c','4' union all select 'd','2' union all select 'e','5' --没插入数据前 select * from @t --插入数据 insert into @t values('f','2')select * from @t order by cc asc,aa desc
那么 f的 aa为6 cc为2 d的cc变为3 依次类推
怎么写这条语句阿
这句话一下子没明白
aa bb cc
1 a 1
2 b 4
3 c 5
4 d 3
5 e 6
6 f 2
1 a 1
2 b 3
3 c 4
4 d 2
5 e 5 变为:aa bb cc
1 a 1
2 b 4
3 c 5
4 d 3
5 e 6
6 f 2 看不懂怎么变的?
--> --> (让你望见影子的墙)生成測試數據
if not object_id('tb') is null
drop table tb
Go
Create table tb([aa] int,[bb] nvarchar(1),[cc] int)
Insert tb
select 1,N'a',1 union all
select 2,N'b',3 union all
select 3,N'c',4 union all
select 4,N'd',2 union all
select 5,N'e',5
Go
Select * from tbcreate trigger t1 on tb
for insert,update
as
update tb
set cc=cc+1
where cc>=(select cc from inserted)
and aa !=(select aa from inserted)insert into tb
values(6 ,'f', 2)select * from tb
1 a 1
2 b 4
3 c 5
4 d 3
5 e 6
6 f 2
for insert,update
as
update tb
set cc=cc+1
where cc>=(select cc from inserted)
and aa !=(select aa from inserted)insert into tb
values(6 ,'f', 2)select * from tb 1 a 1
2 b 4
3 c 5
4 d 3
5 e 6
6 f 2
declare @tb table (aa int,bb nvarchar(10),cc int)
insert into @tb select 1,'a',1
union all select 2,'b',3
union all select 3,'c',4
union all select 4,'d',2
union all select 5,'e',5
--select * from @tb
------------------
select * from @tb a pivot ( sum(cc) for bb in (a,b,c,d,e)) b
1 1 NULL NULL NULL NULL
2 NULL 3 NULL NULL NULL
3 NULL NULL 4 NULL NULL
4 NULL NULL NULL 2 NULL
5 NULL NULL NULL NULL 5
DECLARE @T TABLE
(
aa INT,
bb VARCHAR(10),
cc INT
)INSERT @T
SELECT 1, 'a', 1
UNION ALL SELECT 2, 'b', 3
UNION ALL SELECT 3, 'c', 4
UNION ALL SELECT 4, 'd', 2
UNION ALL SELECT 5, 'e', 5DECLARE @sql VARCHAR(8000)
SET @sql = ''
SELECT @sql = @sql + bb + ' ' FROM @T ORDER BY [cc]
SELECT @sql
declare @t table(aa int identity(1,1),bb varchar(32),cc varchar(32))
insert into @t(bb,cc)
select 'a','1'
union all select 'b','3'
union all select 'c','4'
union all select 'd','2'
union all select 'e','5'
--没插入数据前
select * from @t
--插入数据
insert into @t values('f','2')select * from @t order by cc asc,aa desc