求一SQL语句

[dawei_jiajia]

表1：t2

SUBMIT_ID USER_ID ....................

1 aaa
2 bbb
3 aaa
表二 t1

SUBMIT_ID HOMEWORK_EVALUATE_TYPE HOMEWORK_EVALUATE_TOTAL

1 1 20
1 2 30
1 3 40
2 1 20
2 2 30
2 3 40
3 1 50
3 2 60
3 3 70

我要得到结果集如下

SUBMIT_ID USER_ID HOMEWORK_EVALUATE_TOTAL HOMEWORK_EVALUATE_TOTAL HOMEWORK_EVALUATE_TOTAL
1 aaa 20 30 40
2 bbb 20 30 40
3 aaa 50 60 70

两个表之间靠 SUBMIT_ID 关联

请高人给出语句谢谢 ！

[开着拖拉机泡妞]

--> 测试数据：[a1]
if object_id('[a1]') is not null drop table [a1]
create table [a1]([SUBMIT_ID] int,[USER_ID] varchar(3))
insert [a1]
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'aaa'
--> 测试数据：[b2]
if object_id('[b2]') is not null drop table [b2]
create table [b2](
[SUBMIT_ID] int,
[HOMEWORK_EVALUATE_TYPE] int,
[HOMEWORK_EVALUATE_TOTAL] int
)
insert [b2]
select 1,1,20 union all
select 1,2,30 union all
select 1,3,40 union all
select 2,1,20 union all
select 2,2,30 union all
select 2,3,40 union all
select 3,1,50 union all
select 3,2,60 union all
select 3,3,70

select
      a.[SUBMIT_ID],a.[USER_ID],
      SUM(case when [HOMEWORK_EVALUATE_TYPE]=1 then [HOMEWORK_EVALUATE_TOTAL] end) [HOMEWORK_EVALUATE_TOTAL],
      SUM(case when [HOMEWORK_EVALUATE_TYPE]=2 then [HOMEWORK_EVALUATE_TOTAL] end) [HOMEWORK_EVALUATE_TOTAL],
      SUM(case when [HOMEWORK_EVALUATE_TYPE]=3 then [HOMEWORK_EVALUATE_TOTAL] end) [HOMEWORK_EVALUATE_TOTAL]
from    
    [b2] b
inner join 
    [a1] a
on a.SUBMIT_ID=b.SUBMIT_ID
group by 
    a.[SUBMIT_ID],a.[USER_ID]
    
/*
SUBMIT_ID	USER_ID	HOMEWORK_EVALUATE_TOTAL	HOMEWORK_EVALUATE_TOTAL	HOMEWORK_EVALUATE_TOTAL
1	aaa	20	30	40
2	bbb	20	30	40
3	aaa	50	60	70
*/

[开着拖拉机泡妞]

--> 测试数据：[a1]
if object_id('[a1]') is not null drop table [a1]
create table [a1]([SUBMIT_ID] int,[USER_ID] varchar(3))
insert [a1]
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'aaa'
--> 测试数据：[b2]
if object_id('[b2]') is not null drop table [b2]
create table [b2](
[SUBMIT_ID] int,
[HOMEWORK_EVALUATE_TYPE] int,
[HOMEWORK_EVALUATE_TOTAL] int
)
insert [b2]
select 1,1,20 union all
select 1,2,30 union all
select 1,3,40 union all
select 2,1,20 union all
select 2,2,30 union all
select 2,3,40 union all
select 3,1,50 union all
select 3,2,60 union all
select 3,3,70
--2000
select
      a.[SUBMIT_ID],a.[USER_ID],
      SUM(case when [HOMEWORK_EVALUATE_TYPE]=1 then [HOMEWORK_EVALUATE_TOTAL] end) [HOMEWORK_EVALUATE_TOTAL],
      SUM(case when [HOMEWORK_EVALUATE_TYPE]=2 then [HOMEWORK_EVALUATE_TOTAL] end) [HOMEWORK_EVALUATE_TOTAL],
      SUM(case when [HOMEWORK_EVALUATE_TYPE]=3 then [HOMEWORK_EVALUATE_TOTAL] end) [HOMEWORK_EVALUATE_TOTAL]
from    
    [b2] b
inner join 
    [a1] a
on a.SUBMIT_ID=b.SUBMIT_ID
group by 
    a.[SUBMIT_ID],a.[USER_ID]
    
/*
SUBMIT_ID	USER_ID	HOMEWORK_EVALUATE_TOTAL	HOMEWORK_EVALUATE_TOTAL	HOMEWORK_EVALUATE_TOTAL
1	aaa	20	30	40
2	bbb	20	30	40
3	aaa	50	60	70
*/

--2005以上版本
select 
      a.*,
      t.[1] as [HOMEWORK_EVALUATE_TOTAL1],
      t.[2] as [HOMEWORK_EVALUATE_TOTAL2],
      t.[3] as [HOMEWORK_EVALUATE_TOTAL3]
from(
select 
     *
from 
     [b2]
pivot
    (max([HOMEWORK_EVALUATE_TOTAL]) 
    for [HOMEWORK_EVALUATE_TYPE] in([1],[2],[3]))b
    )t
inner join 
     [a1] a
on
     a.[SUBMIT_ID]=t.[SUBMIT_ID]
     
/*
SUBMIT_ID	USER_ID	HOMEWORK_EVALUATE_TOTAL1	HOMEWORK_EVALUATE_TOTAL2	HOMEWORK_EVALUATE_TOTAL3
1	aaa	20	30	40
2	bbb	20	30	40
3	aaa	50	60	70
*/

[快溜]

行转列后内连接。搜精华贴。

[我腫了]

--SUBMIT_ID USER_ID HOMEWORK_EVALUATE_TOTAL HOMEWORK_EVALUATE_TOTAL HOMEWORK_EVALUATE_TOTAL
--1 aaa 20 30 40
--2 bbb 20 30 40
--3 aaa 50 60 70

if object_id('t2') is not null 
	drop table t2
Go
if object_id('t1') is not null 
	drop table t1
go

Create table t2([SUBMIT_ID] smallint,[USER_ID] nvarchar(3))
Insert into t2
Select 1,N'aaa'
Union all Select 2,N'bbb'
Union all Select 3,N'aaa'


Create table t1([SUBMIT_ID] smallint,[HOMEWORK_EVALUATE_TYPE] smallint,[HOMEWORK_EVALUATE_TOTAL] smallint)
Insert into t1
Select 1,1,20
Union all Select 1,2,30
Union all Select 1,3,40
Union all Select 2,1,20
Union all Select 2,2,30
Union all Select 2,3,40
Union all Select 3,1,50
Union all Select 3,2,60
Union all Select 3,3,70


;WITH c as (
	 select a.[SUBMIT_ID],a.[USER_ID],b.[HOMEWORK_EVALUATE_TYPE],b.[HOMEWORK_EVALUATE_TOTAL] from t2 AS a
	INNER JOIN t1 AS b ON a.[SUBMIT_ID]=b.[SUBMIT_ID]
)
SELECT 
		[SUBMIT_ID]
		,[USER_ID]
		,[1] AS [HOMEWORK_EVALUATE_TOTAL]
		,[2] AS [HOMEWORK_EVALUATE_TOTAL]
		,[3] AS [HOMEWORK_EVALUATE_TOTAL] 
	FROM c
	PIVOT (
		SUM([HOMEWORK_EVALUATE_TOTAL]) FOR [HOMEWORK_EVALUATE_TYPE] IN([1],[2],[3])
	) AS p

[anzhiqiang_touzi]

[Quote=引用 2 楼 的回复:]
SQL code

--> 测试数据：[a1]
if object_id('[a1]') is not null drop table [a1]
create table [a1]([SUBMIT_ID] int,[USER_ID] varchar(3))
insert [a1]
select 1,'aaa' union all
select 2,'bbb' union all
s……
[/Quote]

[wangxiaofeiwuqiao]

SELECT b.*,a.USER_ID 
from
(SELECT t1.SUBMIT_ID,
SUM(CASE WHEN HOMEWORK_EVALUATE_TYPE=1 THEN HOMEWORK_EVALUATE_TOTAL END) AS HOMEWORK_EVALUATE_TOTAL1,
SUM(CASE WHEN HOMEWORK_EVALUATE_TYPE=2 THEN HOMEWORK_EVALUATE_TOTAL END) AS HOMEWORK_EVALUATE_TOTAL2,
SUM(CASE WHEN HOMEWORK_EVALUATE_TYPE=3 THEN HOMEWORK_EVALUATE_TOTAL END) AS HOMEWORK_EVALUATE_TOTAL3
FROM t1 GROUP BY SUBMIT_ID
) b
INNER JOIN t2 a
ON a.SUBMIT_ID=b.SUBMIT_ID