有表T,该表只有一列i,该列i信息(数据)如下:
1
NULL
2
3
8
用SQL语句求如下的结果集(一列):
0
1
2
3
4
要求:使用一条语句得到结果(不得使用子查询) Create Table T
(
i int
)
Go
Insert T(i)
Select 1
Union Select null
Union Select 2
Union Select 3
Union Select 8
Go多谢
1
NULL
2
3
8
用SQL语句求如下的结果集(一列):
0
1
2
3
4
要求:使用一条语句得到结果(不得使用子查询) Create Table T
(
i int
)
Go
Insert T(i)
Select 1
Union Select null
Union Select 2
Union Select 3
Union Select 8
Go多谢
Drop table [T]
Go
Create Table T
(
i int
)
Go
Insert T(i)
Select 1
Union Select null
Union Select 2
Union Select 3
Union Select 8
Go
--查询如下:
select I=(select COUNT(1) from T where i<=a.i) from T a
/*
I
-----------
0
1
2
3
4(5 行受影响)*/
(select count(1) from (select isnull(i,0) as i from t) a where i<t.i)
from
(select isnull(i,0) as i from t) t/**
-----------
0
1
2
3
4(所影响的行数为 5 行)
**/
Create Table T
(
i int
)
Go
Insert T(i)
Select 1
Union Select null
Union Select 2
Union Select 3
Union Select 8 select DISTINCT number
from master..spt_values
where number between 0 and (select COUNT(*) from t )-1 number
-----------
0
1
2
3
4
Create Table T
(
i int
)
Go
Insert T(i)
Select 1
Union Select null
Union Select 2
Union Select 3
Union Select 8 select ROW_NUMBER() over (order by getdate())-1
from T
--------------------
0
1
2
3
4
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-02 18:40:58
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (col INT)
INSERT INTO @T
SELECT 1 UNION ALL
SELECT null UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 8--SQL查询如下:SELECT COUNT(*)-1 AS col
FROM @T AS A
JOIN @T AS B
ON A.col <= B.col OR B.col IS NULL
GROUP BY A.col
ORDER BY 1/*
col
-----------
0
1
2
3
4(5 row(s) affected)
*/