11,848
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('TreeNode') IS NOT NULL DROP TABLE TreeNode
GO
CREATE TABLE TREENODE (
ID INT NOT NULL UNIQUE
,VAL INT
,LID INT
,RID INT
)
/*
10
/ \
6 14
/ \ / \
4 8 12 16
*/
INSERT INTO TREENODE
SELECT 10,10,6,14 UNION ALL
SELECT 6,6,4,8 UNION ALL
SELECT 14,14,12,16 UNION ALL
SELECT 4,4,NULL,NULL UNION ALL
SELECT 8,8,NULL,NULL UNION ALL
SELECT 12,12,NULL,NULL UNION ALL
SELECT 16,16,NULL,NULL
SELECT * FROM TREENODE
/*
ID VAL LID RID
----------- ----------- ----------- -----------
10 10 6 14
6 6 4 8
14 14 12 16
4 4 NULL NULL
8 8 NULL NULL
12 12 NULL NULL
16 16 NULL NULL
*/
UPDATE T1 SET
LID=(SELECT TOP 1 ID FROM TREENODE T2 WHERE T2.VAL<T1.VAL ORDER BY VAL DESC)
,RID=(SELECT TOP 1 ID FROM TREENODE T2 WHERE T2.VAL>T1.VAL ORDER BY VAL ASC)
FROM TREENODE T1
SELECT * FROM TREENODE ORDER BY ID
/*
ID VAL LID RID
----------- ----------- ----------- -----------
4 4 NULL 6
6 6 4 8
8 8 6 10
10 10 8 12
12 12 10 14
14 14 12 16
16 16 14 NULL
*/
第六题用列来做
CREATE TABLE #TB(NUM INT,SUMNUM INT)
DECLARE @I INT
SELECT @I=0
while @I<10
BEGIN
INSERT INTO #TB VALUES(@I,0)
SELECT @I=@I+1
END
WHILE exists(SELECT b.num FROM #TB B where SUMNUM<>(SELECT COUNT(*) FROM #TB A where A.sumnum=b.num))
BEGIN
DECLARE @NUM INT
DECLARE CUR CURSOR FOR SELECT NUM FROM #TB
OPEN CUR
FETCH NEXT FROM CUR INTO @NUM
WHILE @@fetch_status=0
BEGIN
UPDATE #TB SET SUMNUM=(SELECT COUNT(*) FROM #TB A WHERE A.SUMNUM=B.NUM) FROM #TB B WHERE B.NUM=@NUM
FETCH NEXT FROM CUR INTO @NUM
END
CLOSE CUR
DEALLOCATE CUR
END
SELECT * FROM #TB
DROP TABLE #TB
DECLARE @N1 INT,@N2 INT,@N3 INT,@N4 INT,@N5 INT,@N6 INT,@N7 INT,@N8 INT,@N9 INT,@N10 INT
SELECT @N1=0,@N2=1,@N3=2,@N4=3,@N5=4,@N6=5,@N7=6,@N8=7,@N9=8,@N10=9
;WITH MU AS (
SELECT @N1 AS NUM UNION ALL
SELECT @N2 UNION ALL
SELECT @N3 UNION ALL
SELECT @N4 UNION ALL
SELECT @N5 UNION ALL
SELECT @N6 UNION ALL
SELECT @N7 UNION ALL
SELECT @N8 UNION ALL
SELECT @N9 UNION ALL
SELECT @N10
)
SELECT @N1,@N2,@N3,@N4,@N5,@N6,@N7,@N8,@N9,@N10 UNION ALL
SELECT
T1.NUM,T2.NUM,T3.NUM,T4.NUM,T5.NUM,T6.NUM,T7.NUM,T8.NUM,T9.NUM,T10.NUM
FROM MU T1
INNER JOIN MU T2 ON T1.NUM+T2.NUM<=10
INNER JOIN MU T3 ON T1.NUM+T2.NUM+T3.NUM<=10
INNER JOIN MU T4 ON T1.NUM+T2.NUM+T3.NUM+T4.NUM<=10
INNER JOIN MU T5 ON T1.NUM+T2.NUM+T3.NUM+T4.NUM+T5.NUM<=10
INNER JOIN MU T6 ON T1.NUM+T2.NUM+T3.NUM+T4.NUM+T5.NUM+T6.NUM<=10
INNER JOIN MU T7 ON T1.NUM+T2.NUM+T3.NUM+T4.NUM+T5.NUM+T6.NUM+T7.NUM<=10
INNER JOIN MU T8 ON T1.NUM+T2.NUM+T3.NUM+T4.NUM+T5.NUM+T6.NUM+T7.NUM+T8.NUM<=10
INNER JOIN MU T9 ON T1.NUM+T2.NUM+T3.NUM+T4.NUM+T5.NUM+T6.NUM+T7.NUM+T8.NUM+T9.NUM<=10
INNER JOIN MU T10 ON T1.NUM+T2.NUM+T3.NUM+T4.NUM+T5.NUM+T6.NUM+T7.NUM+T8.NUM+T9.NUM+T10.NUM<=10
WHERE T1.NUM+T2.NUM+T3.NUM+T4.NUM+T5.NUM+T6.NUM+T7.NUM+T8.NUM+T9.NUM+T10.NUM=10
AND T1.NUM=(
CASE WHEN T1.NUM=@N1 THEN 1 ELSE 0 END
+CASE WHEN T2.NUM=@N1 THEN 1 ELSE 0 END
+CASE WHEN T3.NUM=@N1 THEN 1 ELSE 0 END
+CASE WHEN T4.NUM=@N1 THEN 1 ELSE 0 END
+CASE WHEN T5.NUM=@N1 THEN 1 ELSE 0 END
+CASE WHEN T6.NUM=@N1 THEN 1 ELSE 0 END
+CASE WHEN T7.NUM=@N1 THEN 1 ELSE 0 END
+CASE WHEN T8.NUM=@N1 THEN 1 ELSE 0 END
+CASE WHEN T9.NUM=@N1 THEN 1 ELSE 0 END
+CASE WHEN T10.NUM=@N1 THEN 1 ELSE 0 END
)AND T2.NUM=(
CASE WHEN T1.NUM=@N2 THEN 1 ELSE 0 END
+CASE WHEN T2.NUM=@N2 THEN 1 ELSE 0 END
+CASE WHEN T3.NUM=@N2 THEN 1 ELSE 0 END
+CASE WHEN T4.NUM=@N2 THEN 1 ELSE 0 END
+CASE WHEN T5.NUM=@N2 THEN 1 ELSE 0 END
+CASE WHEN T6.NUM=@N2 THEN 1 ELSE 0 END
+CASE WHEN T7.NUM=@N2 THEN 1 ELSE 0 END
+CASE WHEN T8.NUM=@N2 THEN 1 ELSE 0 END
+CASE WHEN T9.NUM=@N2 THEN 1 ELSE 0 END
+CASE WHEN T10.NUM=@N2 THEN 1 ELSE 0 END
)AND T3.NUM=(
CASE WHEN T1.NUM=@N3 THEN 1 ELSE 0 END
+CASE WHEN T2.NUM=@N3 THEN 1 ELSE 0 END
+CASE WHEN T3.NUM=@N3 THEN 1 ELSE 0 END
+CASE WHEN T4.NUM=@N3 THEN 1 ELSE 0 END
+CASE WHEN T5.NUM=@N3 THEN 1 ELSE 0 END
+CASE WHEN T6.NUM=@N3 THEN 1 ELSE 0 END
+CASE WHEN T7.NUM=@N3 THEN 1 ELSE 0 END
+CASE WHEN T8.NUM=@N3 THEN 1 ELSE 0 END
+CASE WHEN T9.NUM=@N3 THEN 1 ELSE 0 END
+CASE WHEN T10.NUM=@N3 THEN 1 ELSE 0 END
)AND T4.NUM=(
CASE WHEN T1.NUM=@N4 THEN 1 ELSE 0 END
+CASE WHEN T2.NUM=@N4 THEN 1 ELSE 0 END
+CASE WHEN T3.NUM=@N4 THEN 1 ELSE 0 END
+CASE WHEN T4.NUM=@N4 THEN 1 ELSE 0 END
+CASE WHEN T5.NUM=@N4 THEN 1 ELSE 0 END
+CASE WHEN T6.NUM=@N4 THEN 1 ELSE 0 END
+CASE WHEN T7.NUM=@N4 THEN 1 ELSE 0 END
+CASE WHEN T8.NUM=@N4 THEN 1 ELSE 0 END
+CASE WHEN T9.NUM=@N4 THEN 1 ELSE 0 END
+CASE WHEN T10.NUM=@N4 THEN 1 ELSE 0 END
)AND T5.NUM=(
CASE WHEN T1.NUM=@N5 THEN 1 ELSE 0 END
+CASE WHEN T2.NUM=@N5 THEN 1 ELSE 0 END
+CASE WHEN T3.NUM=@N5 THEN 1 ELSE 0 END
+CASE WHEN T4.NUM=@N5 THEN 1 ELSE 0 END
+CASE WHEN T5.NUM=@N5 THEN 1 ELSE 0 END
+CASE WHEN T6.NUM=@N5 THEN 1 ELSE 0 END
+CASE WHEN T7.NUM=@N5 THEN 1 ELSE 0 END
+CASE WHEN T8.NUM=@N5 THEN 1 ELSE 0 END
+CASE WHEN T9.NUM=@N5 THEN 1 ELSE 0 END
+CASE WHEN T10.NUM=@N5 THEN 1 ELSE 0 END
)AND T6.NUM=(
CASE WHEN T1.NUM=@N6 THEN 1 ELSE 0 END
+CASE WHEN T2.NUM=@N6 THEN 1 ELSE 0 END
+CASE WHEN T3.NUM=@N6 THEN 1 ELSE 0 END
+CASE WHEN T4.NUM=@N6 THEN 1 ELSE 0 END
+CASE WHEN T5.NUM=@N6 THEN 1 ELSE 0 END
+CASE WHEN T6.NUM=@N6 THEN 1 ELSE 0 END
+CASE WHEN T7.NUM=@N6 THEN 1 ELSE 0 END
+CASE WHEN T8.NUM=@N6 THEN 1 ELSE 0 END
+CASE WHEN T9.NUM=@N6 THEN 1 ELSE 0 END
+CASE WHEN T10.NUM=@N6 THEN 1 ELSE 0 END
)AND T7.NUM=(
CASE WHEN T1.NUM=@N7 THEN 1 ELSE 0 END
+CASE WHEN T2.NUM=@N7 THEN 1 ELSE 0 END
+CASE WHEN T3.NUM=@N7 THEN 1 ELSE 0 END
+CASE WHEN T4.NUM=@N7 THEN 1 ELSE 0 END
+CASE WHEN T5.NUM=@N7 THEN 1 ELSE 0 END
+CASE WHEN T6.NUM=@N7 THEN 1 ELSE 0 END
+CASE WHEN T7.NUM=@N7 THEN 1 ELSE 0 END
+CASE WHEN T8.NUM=@N7 THEN 1 ELSE 0 END
+CASE WHEN T9.NUM=@N7 THEN 1 ELSE 0 END
+CASE WHEN T10.NUM=@N7 THEN 1 ELSE 0 END
)AND T8.NUM=(
CASE WHEN T1.NUM=@N8 THEN 1 ELSE 0 END
+CASE WHEN T2.NUM=@N8 THEN 1 ELSE 0 END
+CASE WHEN T3.NUM=@N8 THEN 1 ELSE 0 END
+CASE WHEN T4.NUM=@N8 THEN 1 ELSE 0 END
+CASE WHEN T5.NUM=@N8 THEN 1 ELSE 0 END
+CASE WHEN T6.NUM=@N8 THEN 1 ELSE 0 END
+CASE WHEN T7.NUM=@N8 THEN 1 ELSE 0 END
+CASE WHEN T8.NUM=@N8 THEN 1 ELSE 0 END
+CASE WHEN T9.NUM=@N8 THEN 1 ELSE 0 END
+CASE WHEN T10.NUM=@N8 THEN 1 ELSE 0 END
)AND T9.NUM=(
CASE WHEN T1.NUM=@N9 THEN 1 ELSE 0 END
+CASE WHEN T2.NUM=@N9 THEN 1 ELSE 0 END
+CASE WHEN T3.NUM=@N9 THEN 1 ELSE 0 END
+CASE WHEN T4.NUM=@N9 THEN 1 ELSE 0 END
+CASE WHEN T5.NUM=@N9 THEN 1 ELSE 0 END
+CASE WHEN T6.NUM=@N9 THEN 1 ELSE 0 END
+CASE WHEN T7.NUM=@N9 THEN 1 ELSE 0 END
+CASE WHEN T8.NUM=@N9 THEN 1 ELSE 0 END
+CASE WHEN T9.NUM=@N9 THEN 1 ELSE 0 END
+CASE WHEN T10.NUM=@N9 THEN 1 ELSE 0 END
)AND T10.NUM=(
CASE WHEN T1.NUM=@N10 THEN 1 ELSE 0 END
+CASE WHEN T2.NUM=@N10 THEN 1 ELSE 0 END
+CASE WHEN T3.NUM=@N10 THEN 1 ELSE 0 END
+CASE WHEN T4.NUM=@N10 THEN 1 ELSE 0 END
+CASE WHEN T5.NUM=@N10 THEN 1 ELSE 0 END
+CASE WHEN T6.NUM=@N10 THEN 1 ELSE 0 END
+CASE WHEN T7.NUM=@N10 THEN 1 ELSE 0 END
+CASE WHEN T8.NUM=@N10 THEN 1 ELSE 0 END
+CASE WHEN T9.NUM=@N10 THEN 1 ELSE 0 END
+CASE WHEN T10.NUM=@N10 THEN 1 ELSE 0 END
)
/*
0 1 2 3 4 5 6 7 8 9
6 2 1 0 0 0 1 0 0 0
*/
IF OBJECT_ID('TreeNode') IS NOT NULL DROP TABLE TreeNode
GO
CREATE TABLE TREENODE (
ID INT NOT NULL UNIQUE
,VAL INT
,LID INT
,RID INT
)
/*
10
/ \
5 12
/ \
4 7
*/
INSERT INTO TREENODE
SELECT 10,10,5,12 UNION ALL
SELECT 5,5,4,7 UNION ALL
SELECT 12,12,NULL,NULL UNION ALL
SELECT 4,4,NULL,NULL UNION ALL
SELECT 7,7,NULL,NULL
DECLARE @NUM INT
SET @NUM=22
;WITH MU AS (
SELECT ID,VAL,LID,RID,VAL AS SUMVAL,1 AS LEVEL,NULL AS PARIENTID
FROM TREENODE T1
UNION ALL
SELECT T1.ID,T1.VAL,T1.LID,T1.RID,T1.VAL+T2.SUMVAL,LEVEL+1,T2.ID AS PARIENTID
FROM TREENODE T1
INNER JOIN MU T2 ON T1.ID=T2.LID OR T1.ID=T2.RID
),MU2 AS (
SELECT *,CONVERT(VARCHAR(MAX),ID) AS [PATH] FROM MU WHERE SUMVAL=22
UNION ALL
SELECT T1.*,LTRIM(T1.ID)+'-'+T2.[PATH]
FROM MU T1
INNER JOIN MU2 T2 ON T1.ID=T2.PARIENTID AND T1.LEVEL=T2.LEVEL-1
)
SELECT PATH FROM MU2 WHERE LEVEL=1
/*
10-5-7
10-12
*/
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
ID INT IDENTITY(1,1)
,VAL INT
)
INSERT INTO TB
SELECT 1 UNION ALL SELECT -2 UNION ALL SELECT 3 UNION ALL SELECT 10 UNION ALL SELECT -4
UNION ALL SELECT 7 UNION ALL SELECT 2 UNION ALL SELECT -5
;WITH MU AS (
SELECT ID,VAL,VAL AS TEMPVAL ,VAL AS SUMVAL
FROM TB WHERE ID=1
UNION ALL
SELECT T1.ID,T1.VAL
,CASE WHEN T2.TEMPVAL<0 THEN T1.VAL ELSE T1.VAL+T2.TEMPVAL END
,CASE WHEN
CASE WHEN T2.TEMPVAL<0 THEN T1.VAL ELSE T1.VAL+T2.TEMPVAL END
>T2.SUMVAL THEN CASE WHEN T2.TEMPVAL<0 THEN T1.VAL ELSE T1.VAL+T2.TEMPVAL END
ELSE T2.SUMVAL END
FROM TB T1
INNER JOIN MU T2 ON T1.ID=T2.ID+1
)
SELECT TOP 1 SUMVAL FROM MU
ORDER BY ID DESC
--18
IF OBJECT_ID('MYSTACK') IS NOT NULL DROP TABLE MYSTACK
GO
CREATE TABLE MYSTACK(
ID INT IDENTITY(1,1)
,VAL INT
,MINVAL INT
,PRIMARY KEY(
ID DESC
)
)
GO
IF OBJECT_ID('MYPUSH') IS NOT NULL DROP PROCEDURE MYPUSH
GO
CREATE PROCEDURE MYPUSH(@VAL INT)
AS
BEGIN
DECLARE @MINVAL INT
SELECT TOP 1 @MINVAL=VAL FROM MYSTACK
INSERT INTO MYSTACK(VAL,MINVAL)
SELECT @VAL,CASE WHEN @VAL<@MINVAL OR @MINVAL IS NULL THEN @VAL ELSE @MINVAL END
END
GO
IF OBJECT_ID('MYPOP') IS NOT NULL DROP PROCEDURE MYPOP
GO
CREATE PROCEDURE MYPOP
AS
BEGIN
DECLARE @NOWID INT,@NOWVAL INT
SELECT TOP 1 @NOWID=ID,@NOWVAL=VAL FROM MYSTACK
DELETE FROM MYSTACK WHERE ID=@NOWID
SELECT @NOWVAL
END
GO
IF OBJECT_ID('MYMIN') IS NOT NULL DROP PROCEDURE MYMIN
GO
CREATE PROCEDURE MYMIN
AS
BEGIN
SELECT TOP 1 MINVAL FROM MYSTACK
END
GO
MYPUSH 7
GO
MYPUSH 4
GO
MYPUSH 5
GO
SELECT * FROM MYSTACK
GO
MYMIN
GO
MYPOP
GO
MYPOP
GO
MYMIN
GO