楼主发过了吧create table tb(id int) insert tb select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16select id=power(id,2) from tb where power(id,2)<=(select max(id) from tb)
select * from tb where sqrt(id)=round(sqrt(id))
-- 呵呵!select * from t where sqrt(id)=round(sqrt(id),1)
create table tb(id int) insert into tb values(1) insert into tb values(4) insert into tb values(5) insert into tb values(6) insert into tb values(7) insert into tb values(8) insert into tb values(9) insert into tb values(10) insert into tb values(11) insert into tb values(12) insert into tb values(13) insert into tb values(14) insert into tb values(15) insert into tb values(16) goselect * from tb where SQRT(id) - floor(SQRT(id)) = 0drop table tb/* id ----------- 1 4 9 16(所影响的行数为 4 行)*/
--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([ID] [int]) INSERT INTO [tb] SELECT '1' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL SELECT '9' UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '12' UNION ALL SELECT '13' UNION ALL SELECT '14' UNION ALL SELECT '15' UNION ALL SELECT '16'-->SQL查询如下: SELECT * FROM [tb] WHERE SQRT(id)-floor(SQRT(id))=0 /* ID ----------- 1 4 9 16(4 行受影响) */
select id from tb where id in (select id*id from tb)
1
4
5
6
7
8
9
10
11
12
13
14
15
16
--- 查找ID为自然数的平方的记录行! 结果如下:---
1
4
9
16
insert tb select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 15
union all select 16select id=power(id,2) from tb
where power(id,2)<=(select max(id) from tb)
from tb
where sqrt(id)=round(sqrt(id))
-- 呵呵!select *
from t
where sqrt(id)=round(sqrt(id),1)
insert into tb values(1)
insert into tb values(4)
insert into tb values(5)
insert into tb values(6)
insert into tb values(7)
insert into tb values(8)
insert into tb values(9)
insert into tb values(10)
insert into tb values(11)
insert into tb values(12)
insert into tb values(13)
insert into tb values(14)
insert into tb values(15)
insert into tb values(16)
goselect * from tb where SQRT(id) - floor(SQRT(id)) = 0drop table tb/*
id
-----------
1
4
9
16(所影响的行数为 4 行)*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int])
INSERT INTO [tb]
SELECT '1' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '7' UNION ALL
SELECT '8' UNION ALL
SELECT '9' UNION ALL
SELECT '10' UNION ALL
SELECT '11' UNION ALL
SELECT '12' UNION ALL
SELECT '13' UNION ALL
SELECT '14' UNION ALL
SELECT '15' UNION ALL
SELECT '16'-->SQL查询如下:
SELECT * FROM [tb] WHERE SQRT(id)-floor(SQRT(id))=0
/*
ID
-----------
1
4
9
16(4 行受影响)
*/
select id from tb
where id in (select id*id from tb)