select power(id,id) from tb where power(id,id)<=(select max(id) from tb)
create table tb(id int) insert into tb values(1) insert into tb values(2) insert into tb values(3) 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 id in (select m.id * n.id from tb m , tb n where m.id = n.id)drop table tb/* id ----------- 1 4 9 16(所影响的行数为 4 行)*/
错了select power(id,2) from tb where power(id,2)<=(select max(id) from tb)
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) id ----------- 1 4 9 16
--> 生成测试数据表: [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 power(id,2) from [Table] where power<=(select Max(id) from [Table])
select power(id,2) from [Table] where power<=(select Max(id) from [Table])
select power(id,id) from tb where power(id,id)<=(select max(id) from tb)
where power(id,id)<=(select max(id) from tb)
insert into tb values(1)
insert into tb values(2)
insert into tb values(3)
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 id in
(select m.id * n.id from tb m , tb n where m.id = n.id)drop table tb/*
id
-----------
1
4
9
16(所影响的行数为 4 行)*/
where power(id,2)<=(select max(id) from tb)
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)
id
-----------
1
4
9
16
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 power(id,2) from [Table] where power<=(select Max(id) from [Table])
power<=(select Max(id) from [Table])
where power(id,id)<=(select max(id) from tb)