22,209
社区成员
发帖
与我相关
我的任务
分享
create table A ( ID int , name varchar(10))
go
create table B ( ID int , PID int , type int , qty int)
go
insert A
select 1 , '数学'
union select 2 ,'语文'
go
insert B
select 1 , 1 , 1 , 80
union select 2 , 2 , 2 , 98
union select 3 , 1 , 2 , 97
select name , max([1]) , max([2]) from (select a.name , b.* from A left join B on A.ID = B.PID ) a pivot(max(qty) for type in([1],[2])) c
group by name
go
drop table A , B
SELECT 名称,CASE WHEN 类型=1 THEN 数量 ELSE 0 END AS B1数量,
CASE WHEN 类型=2 THEN 数量 ELSE 0 END AS B2数量
FROM A
JOIN B ON A.ID=B.PID
SELECT A.名称,B.数量 AS B1数量,
C.数量 AS B2数量
FROM A
OUTER APPLY (SELECT TOP 1 * FROM B WHERE A.ID=PID AND 类型=1) AS B
OUTER APPLY (SELECT TOP 1 * FROM B WHERE A.ID=PID AND 类型=2) AS C