classid fenke
601 1
602 1
603 1
604 1
605 1
606 2
607 2
608 2
609 2
610 2
611 0
612 0
613 0
614 0
615 0-----------------------
怎样实现如下结果
文科 理科 公共科
606 601 611
607 602 612
608 603 613
609 604 614
610 605 615
601 1
602 1
603 1
604 1
605 1
606 2
607 2
608 2
609 2
610 2
611 0
612 0
613 0
614 0
615 0-----------------------
怎样实现如下结果
文科 理科 公共科
606 601 611
607 602 612
608 603 613
609 604 614
610 605 615
,case when fenke=2 then classid end as 理科
,case when fenke=0 then classid end as 公共科
from tb
INSERT @TB
SELECT 601, 1 UNION ALL
SELECT 602, 1 UNION ALL
SELECT 603, 1 UNION ALL
SELECT 604, 1 UNION ALL
SELECT 605, 1 UNION ALL
SELECT 606, 2 UNION ALL
SELECT 607, 2 UNION ALL
SELECT 608, 2 UNION ALL
SELECT 609, 2 UNION ALL
SELECT 610, 2 UNION ALL
SELECT 611, 0 UNION ALL
SELECT 612, 0 UNION ALL
SELECT 613, 0 UNION ALL
SELECT 614, 0 UNION ALL
SELECT 615, 0SELECT *,ID=IDENTITY(INT,1,1) INTO #T1 FROM @TB WHERE fenke=2
SELECT *,ID=IDENTITY(INT,1,1) INTO #T2 FROM @TB WHERE fenke=1
SELECT *,ID=IDENTITY(INT,1,1) INTO #T3 FROM @TB WHERE fenke=0SELECT WK,LK,C.classid AS GK
FROM
(SELECT ISNULL(A.ID,B.ID) AS ID,A.classid AS WK,B.classid AS LK
FROM #T1 AS A FULL JOIN #T2 AS B ON A.ID=B.ID) T
FULL JOIN #T3 AS C ON C.ID=T.IDDROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3
/*
WK LK GK
----------- ----------- -----------
606 601 611
607 602 612
608 603 613
609 604 614
610 605 615
*/
INSERT @TB
SELECT 601, 1 UNION ALL
SELECT 602, 1 UNION ALL
SELECT 603, 1 UNION ALL
SELECT 604, 1 UNION ALL
SELECT 605, 1 UNION ALL
SELECT 606, 2 UNION ALL
SELECT 607, 2 UNION ALL
SELECT 608, 2 UNION ALL
SELECT 609, 2 UNION ALL
SELECT 610, 2 UNION ALL
SELECT 611, 0 UNION ALL
SELECT 612, 0 UNION ALL
SELECT 613, 0 UNION ALL
SELECT 614, 0 UNION ALL
SELECT 615, 0
;
with t
as
(select *,rid = row_number() over (partition by fenke order by getdate())
from @tb
)
select
max(case when fenke = 1 then classid end) as c1,
max(case when fenke = 2 then classid end) as c2,
max(case when fenke = 0 then classid end) as c3
from t
group by rid
/*
c1 c2 c3
----------- ----------- -----------
601 606 611
602 607 612
603 608 613
604 609 614
605 610 615
警告: 聚合或其他 SET 操作消除了空值。(5 行受影响)*/
select * id 是啥意思能直接用select一句查询出来吗
,case when fenke=2 then classid end as 理科
,case when fenke=0 then classid end as 公共科
from tb
,case when fenke=2 then classid end as 理科
,case when fenke=0 then classid end as 公共科
from tb