表A1 aid bigint
year bigint
month bigint
day bigint
total bigint(销售额)表b2 bid bigint
aid bigint
name bigint
求某一年按季度统计姓名销售额排名的sql语句。
year bigint
month bigint
day bigint
total bigint(销售额)表b2 bid bigint
aid bigint
name bigint
求某一年按季度统计姓名销售额排名的sql语句。
为什么要用bigint呢?
SQL Server在整数值超过 int 数据类型支持的范围时,将使用 bigint 数据类型。
year month day 用int就行,或者用datetime类型也不错,这样做的话更便于统计数据。
下面我简单写一个查询语句
select name,
sum
(
bigint
)
from table
where month in('1','2','3','4')
group by name
order by sum(bigint) desc
a.[year],
a.[month],
b.[name],
sum(a.total)
from A1 a join B1 b on a.aid = b.aid
group by
a.[year],
a.[month],
b.[name]
order by
a.[year],
a.[month],
sum(a.total) desc
create table A1
(aid bigint,
[year] bigint,
[month] bigint,
[day] bigint,
total bigint)
insert into A1
select 1,2011,1,1,1000 union all
select 2,2011,1,1,2000 union all
select 3,2011,1,1,1000 union all
select 4,2011,1,1,2000 union all
select 5,2012,1,1,3000 union all
select 6,2012,3,1,4000 union all
select 7,2012,4,1,5000 union all
select 8,2012,7,1,6000 union all
select 9,2012,8,1,7000 union all
select 10,2012,11,1,8000
create table B2
(
bid bigint,
aid bigint,
name nvarchar(10)
)
insert into B2
select 1,1,'张1' union all
select 2,2,'张1' union all
select 3,3,'张1' union all
select 4,4,'张2' union all
select 5,5,'张2' union all
select 6,6,'张3' union all
select 7,7,'张3' union all
select 8,8,'张3' union all
select 9,9,'张4' union all
select 10,10,'张4'
select a.[year],'季度1' AS [季度],b.name ,sum(a.total) AS [销售额] from A1 a inner join B2 b on a.aid =b.aid where a.[month] between 1 and 3 group by a.[year],b.name
union all
select a.[year],'季度2' AS [季度],b.name ,sum(a.total) AS [销售额] from A1 a inner join B2 b on a.aid =b.aid where a.[month] between 4 and 6 group by a.[year],b.name
union all
select a.[year],'季度3' AS [季度],b.name ,sum(a.total) AS [销售额] from A1 a inner join B2 b on a.aid =b.aid where a.[month] between 7 and 9 group by a.[year],b.name
union all
select a.[year],'季度4' AS [季度],b.name ,sum(a.total) AS [销售额] from A1 a inner join B2 b on a.aid =b.aid where a.[month] between 10 and 12 group by a.[year],b.name
面试题!就这么设计的,!sum(bigint)这算什么,,,,