sql高手请进！！ 一张仅有3个字段的表，一句sql实现查询。

[allmygody]

数据库表customer_sales
有3个字段
customer：客户名,
week：星期　（例如第１～５２周）
sales：销售额

要求用一句sql语句，展示以下结果：
客户名 第1周销售额 第2周销售额
john 100 200
mark 150 250
tim 140
tom 130

_________________数据内容如下：
john 1 100
john 2 200
mark 1 150
mark 3 350
mark 2 250
tim 1 140
tom 1 130
tim 3 340

[ACMAIN_CHM]

http://blog.csdn.net/acmain_chm/article/details/4283943 MySQL交叉表 在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx( id int primary key, c1 c...

[WWWWA]

SELECT 客户名,SUM(IF(week=1,sales,0)) AS 第1周销售额 , SUM(IF(week=2,sales,0)) AS 第2周销售额 .... FROM TT GRUP BY 客户名

[yuanyannan123]

SELECT t.attribute1, SUM(decode(t.attribute2, '1', t.attribute3)) w1, SUM(decode(t.attribute2, '2', t.attribute3)) w2, .. .. . FROM temp t GROUP BY t.attribute1;

[humanity]

报表区，如果你是用 Crystal Report，它有个 cross-tab 组件，普通 SQL 拿数据，在 cross-tab 中把客户放在左边行上，把星期放在右边列上。它会帮你生成。

[lord_is_layuping]

SQL> SQL> create table customer_sales( 2 customer varchar2(40),--：客户名, 3 week varchar2(40),--：星期　（例如第１～５２周） 4 sales number --：销售额 5 ); Table created SQL> insert into customer_sales VALUES ('john' ,'1','100'); 1 row inserted SQL> insert into customer_sales VALUES ('john' ,'2','200'); 1 row inserted SQL> insert into customer_sales VALUES ('mark' ,'1','150'); 1 row inserted SQL> insert into customer_sales VALUES ('mark' ,'3','350'); 1 row inserted SQL> insert into customer_sales VALUES ('mark' ,'2','250'); 1 row inserted SQL> insert into customer_sales VALUES ('tim' ,'1','140'); 1 row inserted SQL> insert into customer_sales VALUES ('tom' ,'1','130'); 1 row inserted SQL> insert into customer_sales VALUES ('tim' ,'3','340'); 1 row inserted SQL> commit; Commit complete SQL> SQL> SELECT t.customer as 客户名, 2 SUM(decode(t.week, '1', t.sales)) as 第1周销售额, 3 SUM(decode(t.week, '2', t.sales)) as 第2周销售额 4 FROM customer_sales t 5 GROUP BY t.customer 6 ORDER BY t.customer; 客户名 第1周销售额 第2周销售额 ---------------------------------------- ----------- ----------- john 100 200 mark 150 250 tim 140 tom 130 SQL>

[根儿哥]

使用DECODE或是CASE WHEN实现都可以的

[18_岁]

select customer, max (case week when '第一周' then sales else null end) as '第一周', max (case week when '第二周' then sales else null end) as '第二周' from customer_sales group by customer

[o_public]

引用 7 楼 u012602900 的回复:

select customer, max (case week when '第一周' then sales else null end) as '第一周', max (case week when '第二周' then sales else null end) as '第二周' from customer_sales group by customer

[o_public]

引用 2 楼 WWWWA 的回复:

SELECT 客户名,SUM(IF(week=1,sales,0)) AS 第1周销售额 , SUM(IF(week=2,sales,0)) AS 第2周销售额 .... FROM TT GRUP BY 客户名

[墨题斋]

select customer 客户名, sum(case week when 1 then sales else null end)第1周销售额, sum(case week when 1 then sales else null end)第2周销售额, from customer_sales group by customer

[liujietongxue]

学习学习 交流交流

[JuanRiquelme]

数据库表customer_sales 有3个字段 customer：客户名, week：星期　（例如第１～５２周） sales：销售额 要求用一句sql语句，展示以下结果： 客户名 第1周销售额 第2周销售额 john 100 200 mark 150 250 tim 140 tom 130 _________________数据内容如下： john 1 100 john 2 200 mark 1 150 mark 3 350 mark 2 250 tim 1 140 tom 1 130 tim 3 340 WITH X0 AS (SELECT 'john' AS CUSTOMER, 1 AS WEEK, 100 AS SALES FROM DUAL UNION ALL SELECT 'john' AS CUSTOMER, 2 AS WEEK, 200 AS SALES FROM DUAL UNION ALL SELECT 'mark' AS CUSTOMER, 1 AS WEEK, 150 AS SALES FROM DUAL UNION ALL SELECT 'mark' AS CUSTOMER, 3 AS WEEK, 350 AS SALES FROM DUAL UNION ALL SELECT 'mark' AS CUSTOMER, 2 AS WEEK, 250 AS SALES FROM DUAL UNION ALL SELECT 'tim' AS CUSTOMER, 1 AS WEEK, 140 AS SALES FROM DUAL UNION ALL SELECT 'tom' AS CUSTOMER, 1 AS WEEK, 130 AS SALES FROM DUAL UNION ALL SELECT 'tim' AS CUSTOMER, 3 AS WEEK, 340 AS SALES FROM DUAL) SELECT CUSTOMER, MAX(CASE WHEN WEEK = 1 THEN SALES END) AS 第1周销售额, MAX(CASE WHEN WEEK = 2 THEN SALES END) AS 第2周销售额, MAX(CASE WHEN WEEK = 3 THEN SALES END) AS 第3周销售额 FROM X0 GROUP BY CUSTOMER ORDER BY CUSTOMER; WITH X0 AS (SELECT 'john' AS CUSTOMER, 1 AS WEEK, 100 AS SALES FROM DUAL UNION ALL SELECT 'john' AS CUSTOMER, 2 AS WEEK, 200 AS SALES FROM DUAL UNION ALL SELECT 'mark' AS CUSTOMER, 1 AS WEEK, 150 AS SALES FROM DUAL UNION ALL SELECT 'mark' AS CUSTOMER, 3 AS WEEK, 350 AS SALES FROM DUAL UNION ALL SELECT 'mark' AS CUSTOMER, 2 AS WEEK, 250 AS SALES FROM DUAL UNION ALL SELECT 'tim' AS CUSTOMER, 1 AS WEEK, 140 AS SALES FROM DUAL UNION ALL SELECT 'tom' AS CUSTOMER, 1 AS WEEK, 130 AS SALES FROM DUAL UNION ALL SELECT 'tim' AS CUSTOMER, 3 AS WEEK, 340 AS SALES FROM DUAL) SELECT * FROM (SELECT CUSTOMER, WEEK, SALES FROM X0) PIVOT (MAX(SALES) FOR WEEK IN(1 AS "第1周销售额", 2 AS "第2周销售额", 3 AS "第3周销售额")) ORDER BY 1; 如果有52个周，第二个方法适合，因为不用写那么多case when 语句，直接 4 AS "第4周销售额"，5 AS "第5周销售额" 等等

[紫魂一号]

做报表就直接交叉报表吧。。。周期进行列分组

[fcy_n]

行转列显示问题

[拾薪]

是不是要group_by

[wangnan135]

SELECT customer AS '客户名', SUM(CASE WHEN cweek='1' THEN sales END) AS '第一周销售额', SUM(CASE WHEN cweek='2' THEN sales END) AS '第二周销售额' FROM customer_salse GROUP BY customer

[xiaoguanzhao]

--列转行 with a as ( select 'john' customer,'1' week,'100' sales from dual union all select 'john' customer,'1' week,'200' sales from dual union all select 'john' customer,'2' week,'200' sales from dual union all select 'mark' customer,'1' week,'150' sales from dual union all select 'mark' customer,'2' week,'250' sales from dual union all select 'mark' customer,'3' week,'350' sales from dual union all select 'tim' customer,'1' week,'140' sales from dual union all select 'tom' customer,'1' week,'130' sales from dual union all select 'test' customer,'5' week,'130' sales from dual union all select 'tim' customer,'3' week,'340' sales from dual ), b as ( select rownum num,'第'||rownum||'周销售额' text from dual connect by rownum<=52 ) select * from (select customer, text,sales from a left join b on a.week=b.num ) pivot (sum(sales) for text in ('第1周销售额','第2周销售额','第3周销售额','第4周销售额'，'第5周销售额')）;