if exists (select * from sysobjects where id = object_id(N'XXXAAA') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table XXXAAA--仓库月截数
GO
CREATE TABLE XXXAAA (
AAA INT NULL,
BBB VARCHAR(6) NULL,
CCC VARCHAR(6) NULL,
)
GO
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','X')
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','A')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','T')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','Y')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','S')
GO------------------------------------问题一(期望得到以下结果,要求用UPDATE实现)
AAA BBB CCC
----------- ------ ------
1 A X
2 A A
3 B T
4 B Y
5 B S------------------------------------问题二(以BBB字段,自然排序结果,最后一次出现的"A","B"那一行
AAA BBB CCC
----------- ------ ------
2 A A
5 B S
--------
AAA BBB CCC
----------- ------ ------
1 A X
2 A A(A,重复的最后一行)
3 B T
4 B Y
5 B S(B,重复的最后一行)
drop table XXXAAA--仓库月截数
GO
CREATE TABLE XXXAAA (
AAA INT NULL,
BBB VARCHAR(6) NULL,
CCC VARCHAR(6) NULL,
)
GO
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','X')
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','A')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','T')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','Y')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','S')
GO------------------------------------问题一(期望得到以下结果,要求用UPDATE实现)
AAA BBB CCC
----------- ------ ------
1 A X
2 A A
3 B T
4 B Y
5 B S------------------------------------问题二(以BBB字段,自然排序结果,最后一次出现的"A","B"那一行
AAA BBB CCC
----------- ------ ------
2 A A
5 B S
--------
AAA BBB CCC
----------- ------ ------
1 A X
2 A A(A,重复的最后一行)
3 B T
4 B Y
5 B S(B,重复的最后一行)
解决方案 »
- SQL能直接获取到计算机的MAC地吗吗
- 请问如何实现不同数据库间表的操作
- sql server 2000中的表导出的文本文件没有生产
- 请问如何实现 用 t-sql 语句 插入 "000001" 至 "999999" 的 字符串???????????急!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 各路高手,能把所有create function整理一下,发布如何?
- 数据排序问题
- 求一sql实现
- 插入超时,请帮帮我
- 紧急求助!关于复杂流水号
- nononono兄,那句SQL根本就没办法再优化了,我白花了50分
- sql的网站后台出错了.大家帮忙看下.谢谢
- 请问 这句话是什么意思?是sql语句?谢谢
AAA BBB CCC
----------- ------ ------
2 A A
5 B S
得到这个结果就OK.
add id int identity(1,1)
CREATE TABLE XXXAAA (
AAA INT NULL,
BBB VARCHAR(6) NULL,
CCC VARCHAR(6) NULL,
)
GO
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','X')
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','A')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','T')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','Y')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','S')
GO select aaa = identity(int,1,1) , bbb,ccc into tmp from xxxaaaupdate xxxaaa
set aaa = b.aaa
from xxxaaa a , tmp b
where a.bbb = b.bbb and a.ccc = b.cccselect * from xxxaaadrop table xxxaaa,tmp/*
AAA BBB CCC
----------- ------ ------
1 A X
2 A A
3 B T
4 B Y
5 B S(所影响的行数为 5 行)
*/
where not exists(select 1 from t where a.BBB=BBB and CCC<a.CCC )
AAA INT NULL,
BBB VARCHAR(6) NULL,
CCC VARCHAR(6) NULL,
)
GO
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','X')
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','A')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','T')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','Y')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','S')
GO select aaa = identity(int,1,1) , bbb,ccc into tmp from xxxaaaupdate xxxaaa
set aaa = b.aaa
from xxxaaa a , tmp b
where a.bbb = b.bbb and a.ccc = b.cccselect * from xxxaaa
/*
AAA BBB CCC
----------- ------ ------
1 A X
2 A A
3 B T
4 B Y
5 B S(所影响的行数为 5 行)
*/--如果按bbb,ccc升序
SELECT aaa=(SELECT COUNT(1) FROM xxxaaa WHERE (bbb < a.bbb) or (bbb=a.bbb and ccc<a.ccc)) + 1 , bbb,ccc FROM xxxaaa a ORDER BY aaa
/*
aaa bbb ccc
----------- ------ ------
1 A A
2 A X
3 B S
4 B T
5 B Y(所影响的行数为 5 行)
*/--如果按bbb asc,ccc desc
SELECT aaa=(SELECT COUNT(1) FROM xxxaaa WHERE (bbb < a.bbb) or (bbb=a.bbb and ccc>a.ccc)) + 1 , bbb,ccc FROM xxxaaa a ORDER BY aaa
/*
aaa bbb ccc
----------- ------ ------
1 A X
2 A A
3 B Y
4 B T
5 B S(所影响的行数为 5 行)
*/
drop table xxxaaa,tmp
AAA INT NULL,
BBB VARCHAR(6) NULL,
CCC VARCHAR(6) NULL,
)
GO
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','X')
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','A')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','T')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','Y')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','S')
GO select aaa = identity(int,1,1) , bbb,ccc into tmp from xxxaaaupdate xxxaaa
set aaa = b.aaa
from xxxaaa a , tmp b
where a.bbb = b.bbb and a.ccc = b.cccselect * from xxxaaa
/*
AAA BBB CCC
----------- ------ ------
1 A X
2 A A
3 B T
4 B Y
5 B S(所影响的行数为 5 行)
*/--如果按bbb,ccc升序
SELECT aaa=(SELECT COUNT(1) FROM xxxaaa WHERE (bbb < a.bbb) or (bbb=a.bbb and ccc<a.ccc)) + 1 , bbb,ccc FROM xxxaaa a ORDER BY aaa
/*
aaa bbb ccc
----------- ------ ------
1 A A
2 A X
3 B S
4 B T
5 B Y(所影响的行数为 5 行)
*/--如果按bbb asc,ccc desc
SELECT aaa=(SELECT COUNT(1) FROM xxxaaa WHERE (bbb < a.bbb) or (bbb=a.bbb and ccc>a.ccc)) + 1 , bbb,ccc FROM xxxaaa a ORDER BY aaa
/*
aaa bbb ccc
----------- ------ ------
1 A X
2 A A
3 B Y
4 B T
5 B S(所影响的行数为 5 行)
*/
--问题二(以BBB字段,自然排序结果,最后一次出现的"A","B"那一行,借助上面那个临时表
select a.* from tmp a where aaa = (select max(aaa) from tmp where bbb = a.bbb) order by a.bbb
/*
aaa bbb ccc
----------- ------ ------
2 A A
5 B S(所影响的行数为 2 行)
*/drop table xxxaaa,tmp
CREATE TABLE XXXAAA (
AAA INT NULL,
BBB VARCHAR(6) NULL,
CCC VARCHAR(6) NULL,
)
GO
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','X')
INSERT INTO XXXAAA(BBB,CCC) VALUES('A','A')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','T')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','Y')
INSERT INTO XXXAAA(BBB,CCC) VALUES('B','S')
GO alter table XXXAAA
add id int identity(1,1)update XXXAAA
set AAA=IDSELECT AAA,BBB,CCC FROM XXXAAA
/*
AAA BBB CCC
----------- ------ ------
1 A X
2 A A
3 B T
4 B Y
5 B S(所影响的行数为 5 行)
*/
select AAA,BBB,CCC from XXXAAA a
where not exists(select 1 from XXXAAA where a.BBB=BBB and CCC <a.CCC )/*
AAA BBB CCC
----------- ------ ------
2 A A
5 B S(所影响的行数为 2 行)
*/