查询语句如下:
WITH T1 AS(
SELECT (ROW_NUMBER() OVER(ORDER BY ID)-1)/3 ID1, --ID1的值为123,123,123……
(case when (ROW_NUMBER() OVER(ORDER BY ID)%3)=0 then 3 else (ROW_NUMBER() OVER(ORDER BY ID)%3) end) ID2, ----ID2的值为111,222,333……
ID,getid,data1,newid
FROM table1)
SELECT ID,getid,data1,newid FROM T1
WHERE ID1 IN (SELECT ID1 FROM T1 a JOIN table2 b on a.ID2=b.ID and a.newid=b.newid GROUP BY a.ID1 HAVING COUNT(*)=3)
想将此查询的结果生成表,如何做?
WITH T1 AS(
SELECT (ROW_NUMBER() OVER(ORDER BY ID)-1)/3 ID1, --ID1的值为123,123,123……
(case when (ROW_NUMBER() OVER(ORDER BY ID)%3)=0 then 3 else (ROW_NUMBER() OVER(ORDER BY ID)%3) end) ID2, ----ID2的值为111,222,333……
ID,getid,data1,newid
FROM table1)
SELECT ID,getid,data1,newid FROM T1
WHERE ID1 IN (SELECT ID1 FROM T1 a JOIN table2 b on a.ID2=b.ID and a.newid=b.newid GROUP BY a.ID1 HAVING COUNT(*)=3)
想将此查询的结果生成表,如何做?
SELECT (ROW_NUMBER() OVER(ORDER BY ID)-1)/3 ID1, --ID1的值为123,123,123……
(case when (ROW_NUMBER() OVER(ORDER BY ID)%3)=0 then 3 else (ROW_NUMBER() OVER(ORDER BY ID)%3) end) ID2, ----ID2的值为111,222,333……
ID,getid,data1,newid
FROM table1)
SELECT ID,getid,data1,newid into #tb---这里
FROM T1
WHERE ID1 IN (SELECT ID1 FROM T1 a JOIN table2 b on a.ID2=b.ID and a.newid=b.newid GROUP BY a.ID1 HAVING COUNT(*)=3)
使用用户临时表来缓存数据...临时表有两中类型:
1)用户临时表:用户临时表的名称以单个数字符号(#)开头;
用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.
当创建它的进程消失时这个临时表就自动删除.
2)系统临时表:系统临时表的名称以数字符号(##)开头
全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.
ID,getid,data1,newid into #t
FROM
T1
WHERE
ID1
IN (SELECT ID1 FROM T1 a JOIN table2 b on a.ID2=b.ID and a.newid=b.newid GROUP BY a.ID1 HAVING COUNT(*)=3)
SELECT (ROW_NUMBER() OVER(ORDER BY ID)-1)/3 ID1, --ID1的值为123,123,123……
(case when (ROW_NUMBER() OVER(ORDER BY ID)%3)=0 then 3 else (ROW_NUMBER() OVER(ORDER BY ID)%3) end) ID2, ----ID2的值为111,222,333……
ID,getid,data1,newid
FROM table1)
SELECT ID,getid,data1,newid into #table FROM T1
WHERE ID1 IN (SELECT ID1 FROM T1 a JOIN table2 b on a.ID2=b.ID and a.newid=b.newid GROUP BY a.ID1 HAVING COUNT(*)=3)
SELECT (ROW_NUMBER() OVER(ORDER BY ID)-1)/3 ID1, --ID1的值为123,123,123……
(case when (ROW_NUMBER() OVER(ORDER BY ID)%3)=0 then 3 else (ROW_NUMBER() OVER(ORDER BY ID)%3) end) ID2, ----ID2的值为111,222,333……
ID,getid,data1,newid
FROM table1)
SELECT ID,getid,data1,newidInto #TempTableFROM T1
WHERE ID1 IN (SELECT ID1 FROM T1 a JOIN table2 b on a.ID2=b.ID and a.newid=b.newid GROUP BY a.ID1 HAVING COUNT(*)=3)
SELECT (ROW_NUMBER() OVER(ORDER BY ID)-1)/3 ID1, --ID1的值为123,123,123……
(case when (ROW_NUMBER() OVER(ORDER BY ID)%3)=0 then 3 else (ROW_NUMBER() OVER(ORDER BY ID)%3) end) ID2, ----ID2的值为111,222,333……
ID,getid,data1,newid
FROM table1)
SELECT ID,getid,data1,newidInto #TempTableFROM T1
WHERE ID1 IN (SELECT ID1 FROM T1 a JOIN table2 b on a.ID2=b.ID and a.newid=b.newid GROUP BY a.ID1 HAVING COUNT(*)=3)