数据库中存在一张表:Account。该表至少有一个字段AccountID,类型是整数型,并且是主键。
表中的部分数据如下(按照AccountID进行排序):Table: Account
--------------------------------
|AccountID | 其他字段略
|-------------------------------
| 1 |
| 2 |
| 3 |
| 4 |
| 9 |
|10 |
|11 |
|20 |
--------------------------------
可以看出,目前表中的AccountID的数值不是连续的。
要求:
(1)请编写一个SQL语句,找出表中缺少的AccountID中,最小的一个。上表中缺少的最小的AccountID应该是5。
(2)使用C#,编写一段或一组程序,找出表中缺少的AccountID中,最大的一个。上表中缺少的,最大的AccountID应该是19。
若能提供多种方法/算法并简要评价各种方法的优劣更佳。
表中的部分数据如下(按照AccountID进行排序):Table: Account
--------------------------------
|AccountID | 其他字段略
|-------------------------------
| 1 |
| 2 |
| 3 |
| 4 |
| 9 |
|10 |
|11 |
|20 |
--------------------------------
可以看出,目前表中的AccountID的数值不是连续的。
要求:
(1)请编写一个SQL语句,找出表中缺少的AccountID中,最小的一个。上表中缺少的最小的AccountID应该是5。
(2)使用C#,编写一段或一组程序,找出表中缺少的AccountID中,最大的一个。上表中缺少的,最大的AccountID应该是19。
若能提供多种方法/算法并简要评价各种方法的优劣更佳。
insert into tb values(1 )
insert into tb values(2 )
insert into tb values(3 )
insert into tb values(4 )
insert into tb values(9 )
insert into tb values(10 )
insert into tb values(11 )
insert into tb values(20 )
goselect min(t1.account) + 1 最小 from
(SELECT *,px=(SELECT COUNT(DISTINCT account) FROM tb WHERE account < a.account) + 1 FROM tb a) t1,
(SELECT *,px=(SELECT COUNT(DISTINCT account) FROM tb WHERE account < a.account) + 1 FROM tb a) t2
where t1.px = t2.px - 1 and t1.account <> t2.account - 1drop table tb/*
最小
-----------
5(所影响的行数为 1 行)
*/
insert into tb values(1 )
insert into tb values(2 )
insert into tb values(3 )
insert into tb values(4 )
insert into tb values(9 )
insert into tb values(10 )
insert into tb values(11 )
insert into tb values(20 )
goselect max(t2.account) - 1 最大 from
(SELECT *,px=(SELECT COUNT(DISTINCT account) FROM tb WHERE account < a.account) + 1 FROM tb a) t1,
(SELECT *,px=(SELECT COUNT(DISTINCT account) FROM tb WHERE account < a.account) + 1 FROM tb a) t2
where t1.px = t2.px - 1 and t1.account <> t2.account - 1drop table tb/*
最大
-----------
19(所影响的行数为 1 行)
*/
select top 1 accountid+1 from
(select accountid from account union select 0)
a where not exists(select * from account where accountid=a.accountid+1)
order by 1最大的数
select top 1 accountid-1 from account a where not exists(select * from account where accountid=a.accountid-1)
order by 1 desc
from tableName
where (ID + 1) not in(select ID
from table)类似可以找出最大的
select Max(ID) - 1
from tableName
where (ID - 1) not in(select ID
from table)不知道有没有语法错误C#就不懂了
(select accountid from account union select 0)
a where not exists(select * from account where accountid=a.accountid+1)
order by 1 最大的数
select top 1 accountid-1 from account a where not exists(select * from account where accountid=a.accountid-1)
order by 1 desc
insert into @a values(1 )
insert into @a values(2 )
insert into @a values(3 )
insert into @a values(4 )
insert into @a values(9 )
insert into @a values(10 )
insert into @a values(11 )
insert into @a values(20 )
--缺失的最大值
SELECT CASE WHEN NOT EXISTS(SELECT * FROM @a WHERE Account = 1) THEN 1 ELSE Max(Account) + 1 END FROM @a AS A WHERE NOT EXISTS (SELECT * FROM @a AS B WHERE B.Account = A.Account + 1)
--缺失的最小值
SELECT CASE WHEN NOT EXISTS(SELECT * FROM @a WHERE Account = 1) THEN 1 ELSE MIN(Account) + 1 END FROM @a AS A WHERE NOT EXISTS (SELECT * FROM @a AS B WHERE B.Account = A.Account + 1)
insert into @a values(1 )
insert into @a values(2 )
insert into @a values(3 )
insert into @a values(4 )
insert into @a values(9 )
insert into @a values(18 )
insert into @a values(19 )
insert into @a values(20 )
--缺失的最大值,这里实测稍改动.
SELECT CASE WHEN NOT EXISTS(SELECT * FROM @a WHERE Account = 1) THEN 1 ELSE Max(Account) - 1 END FROM @a AS A WHERE NOT EXISTS (SELECT * FROM @a AS B WHERE B.Account = A.Account - 1)
--缺失的最小值
SELECT CASE WHEN NOT EXISTS(SELECT * FROM @a WHERE Account = 1) THEN 1 ELSE MIN(Account) + 1 END FROM @a AS A WHERE NOT EXISTS (SELECT * FROM @a AS B WHERE B.Account = A.Account + 1)
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 a
ORDER BY Place
结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8