请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。
table1
月份mon 部门dep 业绩yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门dep 部门名称dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 （result）
部门dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
表已建立，记录已插，dep部门，yi业绩，mon月份
select a.dep as 部门,b.yi as 一月份,c.yi as 二月份,d.yi as 三月份
from ceshi2 as a,ceshi as b,ceshi as c,ceshi as d
where a.dep=b.dep and b.mon='一月份' and
a.dep=c.dep and c.mon='二月份' and
a.dep=d.dep and d.mon='三月份'
为什么搜不到？正确的写法是？谢谢各位大神。
------解决方案--------------------
- SQL code
--> 测试数据：[table1]if object_id('[table1]') is not null drop table [table1]create table [table1]([mon] varchar(6),[dep] varchar(2),[yj] int)insert [table1]select '一月份','01',10 union allselect '一月份','02',10 union allselect '一月份','03',5 union allselect '二月份','02',8 union allselect '二月份','04',9 union allselect '三月份','03',8--> 测试数据：[table2]if object_id('[table2]') is not null drop table [table2]create table [table2]([dep] varchar(2),[dname] varchar(12))insert [table2]select '01','国内业务一部' union allselect '02','国内业务二部' union allselect '03','国内业务三部' union allselect '04','国际业务部'select b.dname 部门,SUM(case when [mon]='一月份' then [yj] end) as 一月份,SUM(case when [mon]='二月份' then [yj] end) as 二月份,SUM(case when [mon]='三月份' then [yj] end) as 三月份from table1 a inner join table2 bon a.dep=b.dep group by b.dname/*部门 一月份 二月份 三月份国际业务部 NULL 9 NULL国内业务二部 10 8 NULL国内业务三部 5 NULL 8国内业务一部 10 NULL NULL*/
------解决方案--------------------
- SQL code
CREATE TABLE table1([月份mon] VARCHAR(20),[部分dep] VARCHAR(10),[业绩yj] INT)INSERT INTO table1SELECT '一月份', '01', 10 UNION ALLSELECT '一月份', '02', 10 UNION ALLSELECT '一月份', '03' ,5 UNION ALLSELECT '二月份', '02' ,8 UNION ALLSELECT '二月份', '04' ,9 UNION ALLSELECT '三月份', '03' ,8CREATE TABLE table2(部门dep VARCHAR(20), 部门名称dname VARCHAR(20))INSERT INTO table2SELECT '01', '国内业务一部' UNION ALL SELECT '02', '国内业务二部' UNION ALLSELECT '03', '国内业务三部' UNION ALLSELECT '04', '国际业务部'SELECT [部分dep],[一月份], [二月份], [三月份]FROM table1PIVOT ( SUM([业绩yj]) FOR [月份mon] IN ([一月份], [二月份], [三月份]) ) AS p/*部分dep 一月份 二月份 三月份---------- ----------- ----------- -----------01 10 NULL NULL02 10 8 NULL03 5 NULL 804 NULL 9 NULL(4 行受影响)*/
------解决方案--------------------
- SQL code
USE tempdb;GOIF OBJECT_ID('table1') IS NOT NULL DROP TABLE table1;GOIF OBJECT_ID('table2') IS NOT NULL DROP TABLE table2;GOCREATE TABLE table1(mon VARCHAR(20), dep CHAR(5) , yj INT );GOCREATE TABLE table2(dep CHAR(5), dname VARCHAR(20)); GO INSERT INTO table1 VALUES('一月份','01',10); INSERT INTO table1 VALUES('一月份','02',10); INSERT INTO table1 VALUES('一月份','03',5); INSERT INTO table1 VALUES('二月份','02',8); INSERT INTO table1 VALUES('三月份','03',8); INSERT INTO table1 VALUES('二月份','04',9);INSERT INTO table2 VALUES('01','国内业务一部');INSERT INTO table2 VALUES('02','国内业务二部');INSERT INTO table2 VALUES('03','国内业务三部');INSERT INTO table2 VALUES('04','国际业务部');--查询SELECT DISTINCT t1.dep,t2.yj AS '一月份',t3.yj AS '二月份',t4.yj AS '三月份' FROM table1 AS t1 LEFT JOIN (SELECT mon,dep,SUM(yj) AS yj FROM table1 WHERE mon='一月份' GROUP BY mon,dep,yj) AS t2 ON t1.dep=t2.depLEFT JOIN (SELECT mon,dep,SUM(yj) AS yj FROM table1 WHERE mon='二月份' GROUP BY mon,dep,yj) AS t3 ON t1.dep=t3.depLEFT JOIN (SELECT mon,dep,SUM(yj) AS yj FROM table1 WHERE mon='三月份' GROUP BY mon,dep,yj) AS t4 ON t1.dep=t4.dep;/*结果dep 一月份 二月份 三月份01 10 NULL NULL02 10 8 NULL03 5 NULL 804 NULL 9 NULL*/