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

allmygody 2014-09-29 07:36:58
数据库表customer_sales
有3个字段
customer:客户名,
week:星期 (例如第1~52周)
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
...全文
3777 点赞 收藏 17
写回复
17 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
xiaoguanzhao 2017-02-21
--列转行 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周销售额'));
回复
wangnan135 2017-01-14
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
回复
拾薪 2016-04-06
是不是要group_by
回复
fcy_n 2016-03-07
行转列显示问题
回复
紫魂一号 2015-09-28
做报表就直接交叉报表吧。。。周期进行列分组
回复
JuanRiquelme 2015-09-24
数据库表customer_sales 有3个字段 customer:客户名, week:星期 (例如第1~52周) 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周销售额" 等等
回复
liujietongxue 2015-09-06
学习学习 交流交流
回复
mqliang282 2015-09-02
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
回复
o_public 2015-03-26
引用 2 楼 WWWWA 的回复:
SELECT 客户名,SUM(IF(week=1,sales,0)) AS 第1周销售额 , SUM(IF(week=2,sales,0)) AS 第2周销售额 .... FROM TT GRUP BY 客户名
回复
o_public 2015-03-26
引用 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
回复
18_岁 2015-01-04
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
回复
根儿哥 2014-11-29
使用DECODE或是CASE WHEN实现都可以的
回复
lord_is_layuping 2014-11-24
SQL> SQL> create table customer_sales( 2 customer varchar2(40),--:客户名, 3 week varchar2(40),--:星期 (例如第1~52周) 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>
回复
humanity 2014-10-18
报表区,如果你是用 Crystal Report,它有个 cross-tab 组件,普通 SQL 拿数据,在 cross-tab 中把客户放在左边行上,把星期放在右边列上。它会帮你生成。
回复
yuanyannan123 2014-10-10
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;
回复
WWWWA 2014-09-29
SELECT 客户名,SUM(IF(week=1,sales,0)) AS 第1周销售额 , SUM(IF(week=2,sales,0)) AS 第2周销售额 .... FROM TT GRUP BY 客户名
回复
ACMAIN_CHM 2014-09-29
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...
回复
相关推荐
发帖
数据库报表
创建于2007-09-28

6099

社区成员

其他数据库开发 数据库报表
申请成为版主
帖子事件
创建了帖子
2014-09-29 07:36
社区公告
暂无公告