如果你想用光标来做到这一点:
CREATE PROCEDURE try
AS
SET NOCOUNT ON
Declare @dname nvarchar(50)
DECLARE curP CURSOR FOR
SELECT d.dname FROM dept d
OPEN curP
FETCH NEXT FROM curP INTO @dname
WHILE @@Fetch_Status = 0
BEGIN
SELECT 0 AS OrderBy, @dname , '' AS sal
union
SELECT 1,
ename ,
CONVERT(VARCHAR,sal) AS sal
FROM emp
WHERE deptno = @dname
ORDER BY OrderBy
FETCH NEXT FROM curP INTO @dname
End
Close curP
Deallocate curP
上述结果将导致:
╔══════════╦═══╗
║ Tech ║ ║
║ Vikrant ║ 5 ║
║ Nimesh ║ 2 ║
╚══════════╩═══╝
╔══════════╦═══╗
║ Creative ║ ║
║ Memo ║ 6 ║
║ Sabir ║ 5 ║
╚══════════╩═══╝
会有另一种思考方式:
;WITH DeptsCTE AS
(
SELECT deptno,
dname,
ROW_NUMBER() OVER (ORDER BY deptno) AS rn
FROM dept
)
SELECT dname,
sal
FROM
(
SELECT D.rn,
0 AS drn,
dname,
'' AS sal
FROM DeptsCTE D
UNION
SELECT D.rn,
1 AS drn,
ename ,
CONVERT(VARCHAR,sal) AS sal
FROM emp E
JOIN DeptsCTE D
ON D.deptno = E.deptno
) AS T
ORDER BY rn,drn