s****e
发帖数: 1180 | 1 有如下data:
date sales
11/01/2012 110
12/01/2012 200
01/03/2013 120
01/04/2014 300
需要，从这组数据，得到如下report:
day
month mon tues wed thurs fri sat sun
jan sales sales sales ...
feb ...
march ...
... ...
有没有会的，能不能指导一下，多谢！：） | z*****u
发帖数: 3010 | 2 我看了一下 给跪了
感觉用java什么的 很方便 | d***e
发帖数: 793 | 3 select
Month(date),
case when WEEKDAY(date) = 0 then sales else 0 end as Mon,
case when WEEKDAY(date) = 1 then sales else 0 end as Tue,
case when WEEKDAY(date) = 2 then sales else 0 end as Wed,
case when WEEKDAY(date) = 3 then sales else 0 end as Thu,
case when WEEKDAY(date) = 4 then sales else 0 end as Fri,
case when WEEKDAY(date) = 5 then sales else 0 end as Sat,
case when WEEKDAY(date) = 6 then sales else 0 end as Sun
from table
group by 1 | d***e
发帖数: 793 | 4 function可能每个engine不一样，你用什么，可以上网查一下 | m******t
发帖数: 6905 | 5 Hi all
I created a table and tried the solution above but it failed. who can help
--Creat Table and loading data by using bulk insert
Drop Table Test20140317
Create Table Test20140317
(SalesDate date,
Sales Decimal)
Bulk insert Test20140317
from 'C:\filepath\Book1.txt'
with
(ROWTERMINATOR = '\n',
firstrow = 2)
select * from Test20140317
--Here is the above solution, I used DateName function to display week days.
select
Month(SalesDate),
case when DATENAME(WEEKDAY,(SalesDate)) = 0 then sales else 0 end as
Mon,
case when DATENAME(WEEKDAY,(SalesDate)) = 1 then sales else 0 end as
Tue,
case when DATENAME(WEEKDAY,(SalesDate)) = 2 then sales else 0 end as
Wed,
case when DATENAME(WEEKDAY,(SalesDate)) = 3 then sales else 0 end as
Thu,
case when DATENAME(WEEKDAY,(SalesDate)) = 4 then sales else 0 end as
Fri,
case when DATENAME(WEEKDAY,(SalesDate)) = 5 then sales else 0 end as
Sat,
case when DATENAME(WEEKDAY,(SalesDate)) = 6 then sales else 0 end as
Sun
from Test20140317
group by 1
--Here is the error message
Msg 8120, Level 16, State 1, Line 3
Column 'Test20140317.Sales' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause. | d***e
发帖数: 793 | 6 忘加sum（）了
因为开始没想加group，觉得month应该是年月，后来觉得既然人家问month，还是加个
group，所以sum也要加上 | d***e
发帖数: 793 | 7 sum（case when DATENAME(WEEKDAY,(SalesDate)) = 0 then sales else 0 end） | s**********o
发帖数: 14359 | 8 这样行吗,是不是要弄个SUBQUERY
【在 d***e 的大作中提到】
: sum（case when DATENAME(WEEKDAY,(SalesDate)) = 0 then sales else 0 end）
| d***e
发帖数: 793 | 9 为什么？
【在 s**********o 的大作中提到】
: 这样行吗,是不是要弄个SUBQUERY
| s**********o
发帖数: 14359 | 10 CASE就是一个一个的试吗，你还要SUM CASE，那么多CASE，不行吧
【在 d***e 的大作中提到】
: 为什么？
| | | d***e
发帖数: 793 | 11 case是per row的，但是它的作用是对Monday到说，把Tuesday to Sun的0加上去。对
sum是没有影响的。
刚才发的那个说错了，所以我删了
【在 s**********o 的大作中提到】
: CASE就是一个一个的试吗，你还要SUM CASE，那么多CASE，不行吧
| d****n
发帖数: 12461 | 12 如果是oracle可以pivot
【在 d***e 的大作中提到】
: select
: Month(date),
: case when WEEKDAY(date) = 0 then sales else 0 end as Mon,
: case when WEEKDAY(date) = 1 then sales else 0 end as Tue,
: case when WEEKDAY(date) = 2 then sales else 0 end as Wed,
: case when WEEKDAY(date) = 3 then sales else 0 end as Thu,
: case when WEEKDAY(date) = 4 then sales else 0 end as Fri,
: case when WEEKDAY(date) = 5 then sales else 0 end as Sat,
: case when WEEKDAY(date) = 6 then sales else 0 end as Sun
: from table
| c*****d
发帖数: 6045 | 13 Oracle里用decode+group by
【在 d****n 的大作中提到】
: 如果是oracle可以pivot
| B*****g
发帖数: 34098 | 14 you should start to use case instead of decode
【在 c*****d 的大作中提到】
: Oracle里用decode+group by
| b*****o
发帖数: 284 | 15 Select year(date) as Year,month(date) as Month,
sum(sales*(dateofweek(date)=0)) as Sunday,
sum(sales*(dateofweek(date)=1)) as Monday,
sum(sales*(dateofweek(date)=2)) as Tuesday,
sum(sales*(dateofweek(date)=3)) as Wednesday,
sum(sales*(dateofweek(date)=4)) as Thursday,
sum(sales*(dateofweek(date)=5)) as Friday,
sum(sales*(dateofweek(date)=6)) as Satday
From yourTable
Group By 1,2
/*replace about functions with relevant ones in your database*/ | d***e
发帖数: 793 | 16 wow, didn't know that it can be used like that. no if(,,)needed. Thanks
however for dayofweek() function, mysql returns values from 1 to 7 which is
different from weekday()
【在 b*****o 的大作中提到】
: Select year(date) as Year,month(date) as Month,
: sum(sales*(dateofweek(date)=0)) as Sunday,
: sum(sales*(dateofweek(date)=1)) as Monday,
: sum(sales*(dateofweek(date)=2)) as Tuesday,
: sum(sales*(dateofweek(date)=3)) as Wednesday,
: sum(sales*(dateofweek(date)=4)) as Thursday,
: sum(sales*(dateofweek(date)=5)) as Friday,
: sum(sales*(dateofweek(date)=6)) as Satday
: From yourTable
: Group By 1,2
| c*****d
发帖数: 6045 | 17 我们作oracle就是这样和sql server区别开的
哈哈
【在 B*****g 的大作中提到】
: you should start to use case instead of decode
| g********s
发帖数: 3652 | 18 Oracle 11g 增加了一个 pivot, 把列的数据轻易变成 对照表。你们用pivot 来解决这
个问题吧
run this one, what you get as output ?
SELECT *
FROM (SELECTdeptno, job, sal FROM scott.emp)
PIVOT (SUM (sal) --<-- pivot_clause
FORdeptno --<-- pivot_for_clause
IN (10, 20, 30, 40) --<-- pivot_in_clause
); | B*****g
发帖数: 34098 | 19 要不说oracle dba最招人恨呢
【在 c*****d 的大作中提到】
: 我们作oracle就是这样和sql server区别开的
: 哈哈
| m*********u
发帖数: 1491 | 20 这是老土的学生们做出来的答案, 用的是SQL Server：
=========================================
use test1
go
if object_id('t1') is not null
drop table t1
create table t1(
saleDate smalldatetime,
sales money)
insert into t1 values('11/01/2012',110), ('12/01/2012', 200), ('01/03/2013',
120),('01/04/2014',300)
select Month, [Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun]
from (select datepart(month, saleDate) as Dmonth,
left(datename(M,saleDate), 3) as Month,
left(datename(dw,saleDate),3) as day,
sales from t1 ) as a
pivot (sum (sales) for day in ([Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun]))
as b
order by Dmonth
results:
Month Mon Tue Wed Thu Fri Sat Sun
Jan NULL NULL NULL 120 NULL 300 NULL
Nov NULL NULL NULL 110 NULL NULL NULL
Dec NULL NULL NULL NULL NULL 200 NULL
========================================================= | A*******n
发帖数: 625 | 21 改一下 table t里面就可以了
select m,[Monday],[Tusday],[Wednesday],[Thursday],[Friday],[Saturday],[
Sunday]
from
(
select month('06/05/2014') as m,DATENAME(weekday,('06/05/2014')) as da,100
as sales) as t
pivot
(
sum(sales)
for t.da in ([Monday],[Tusday],[Wednesday],[Thursday],[Friday],[Saturday],[
Sunday])
) as pivottable |
|
