表内有一字段为集装箱的箱型,表的记录代表一次客户业务,箱型有5种(暂定为 箱型1、箱型2),还有一个字段是该业务的登记时间。
如何用查询找到某用户在某段时间内进行的某种箱型的业务数目,出来结果如下:
客户 箱型1 箱型2 箱型3
jack 0 2 1
lucy 1 0 3
....
我是SQL新手,请教。。
如何用查询找到某用户在某段时间内进行的某种箱型的业务数目,出来结果如下:
客户 箱型1 箱型2 箱型3
jack 0 2 1
lucy 1 0 3
....
我是SQL新手,请教。。
2006-7-21 jack 箱型1
2006-7-22 jack 箱型2
2006-7-23 jack 箱型1
....
希望在查询7-21到7-22之间 用户jack的业务时得到
tran_client_id 箱型1 箱型2 箱型3 箱型4 箱型5
jack 2 1 0 0 0
大概就是这个结构
谢谢了
建议改成如下:客户 选择的箱型 业务数目 业务日期统计的语句如下
select 客户,count(*) from [表名]
where 业务日期>=指定开始日期 and 业务日期<=指定结束日期
group by 客户
CASE WHEN tran_cargo_size = '箱型1' THEN num END,
CASE WHEN tran_cargo_size = '箱型2' THEN num END,
CASE WHEN tran_cargo_size = '箱型3' THEN num END,
CASE WHEN tran_cargo_size = '箱型4' THEN num END,
CASE WHEN tran_cargo_size = '箱型5' THEN num END
FROM (SELECT tran_client_id, COUNT(*) AS num, tran_cargo_size
FROM [表]
WHERE tran_register_date >= '2006-7-21' AND
tran_register_date <= '2006-7-22'
GROUP BY tran_client_id, tran_cargo_size) a
GROUP BY tran_client_id, tran_cargo_size
(
tran_register_date varchar(10),
tran_client_id varchar(10),
tran_cargo_size varchar(10)
)
insert into @test
select '2006-7-21','jack','size1' union all
select '2006-7-22','jack','size2' union all
select '2006-7-23','jack','size1'select
tran_client_id,
sum(case when tran_cargo_size='size1' then 1 else 0 end) as [size1],
sum(case when tran_cargo_size='size2' then 1 else 0 end) as [size2],
sum(case when tran_cargo_size='size3' then 1 else 0 end) as [size3],
sum(case when tran_cargo_size='size4' then 1 else 0 end) as [size4],
sum(case when tran_cargo_size='size5' then 1 else 0 end) as [size5]
from
@test
where
cast(tran_register_date as datetime) between '2006-07-21' and '2006-07-23'
group by tran_client_id/*tran_client_id size1 size2 size3 size4 size5
-------------- ----------- ----------- ----------- ----------- -----------
jack 2 1 0 0 0*/
CASE WHEN tran_cargo_size = '箱型1' THEN num END,
CASE WHEN tran_cargo_size = '箱型2' THEN num END,
CASE WHEN tran_cargo_size = '箱型3' THEN num END,
CASE WHEN tran_cargo_size = '箱型4' THEN num END,
CASE WHEN tran_cargo_size = '箱型5' THEN num END
FROM (SELECT tran_client_id, COUNT(*) AS num, tran_cargo_size
FROM [表]
WHERE tran_register_date >= '2006-7-21' AND
tran_register_date <= '2006-7-22'
GROUP BY tran_client_id, tran_cargo_size) a
GROUP BY tran_client_id上面的多了个垃圾字段。这个即OK。
另一个带有子查询,一般速度更慢些。==================================
= CSDN助手 全面支持CSDN论坛 =
= 监视、收藏、历史、签名走马灯 =
==================================
sum(CASE WHEN tran_cargo_size = '箱型1' THEN 1 ELSE 0 END) as 箱型1数量,
sum(CASE WHEN tran_cargo_size = '箱型2' THEN 1 ELSE 0 END) as 箱型2数量,
sum(CASE WHEN tran_cargo_size = '箱型3' THEN 1 ELSE 0 END) as 箱型3数量,
sum(CASE WHEN tran_cargo_size = '箱型4' THEN 1 Else 0 END) as 箱型4数量 ,
sum(CASE WHEN tran_cargo_size = '箱型5' THEN 1 ElSE 0 END) as 箱型5数量
FROM [a]
WHERE tran_register_date >= '2006-7-21' AND
tran_register_date <= '2006-7-22'
group by a.tran_client_id简单些.
用行专列动态sqldeclare @s varchar(8000)
set @s = ''
select @s = @s + ',' + 'sum(case when tran_cargo_size = ''' + tran_cargo_size + ''' then 1 else 0 end ) as ' +tran_cargo_size
from tab
group by tran_cargo_size exec (' select tran_client_id ' + @s + ' from tabWHERE tran_register_date >= ''2006-7-21'' AND
tran_register_date <= ''2006-7-22''
group by a.tran_client_id')
(
id int primary key identity(1,1) not null,
client_name varchar(30) not null,
container_type varchar(10),
container_num int
)
goinsert into t1
select 'jack','箱型1',2
union all
select 'jack','箱型2',1
union all
select 'jack','箱型3',3
union all
select 'jack','箱型4',5
union all
select 'jack','箱型5',6
union all
select 'lucy','箱型1',2
union all
select 'lucy','箱型2',1
union all
select 'lucy','箱型3',3
union all
select 'lucy','箱型4',5
union all
select 'lucy','箱型5',2
union all
select 'lucy','箱型5',6
gocreate function func1(@c_name varchar(30),@c_type varchar(30))
returns int
as
begin
declare @func1 int
select @func1=sum(container_num) from t1 where client_name=@c_name and container_type=@c_type
return @func1
end
go
select client_name as 客户姓名,dbo.func1(client_name,'箱型1') as 箱型1
,dbo.func1(client_name,'箱型2') as 箱型2
,dbo.func1(client_name,'箱型3') as 箱型3
,dbo.func1(client_name,'箱型4') as 箱型4
,dbo.func1(client_name,'箱型5') as 箱型5from t1
group by client_name不知道我这个符合你的要求吗?
就是说,集装箱箱型很多,但从容量上有“自然箱”和“标箱”两个概念,所谓“自然箱”就是一个集装箱,不管大小就算一个自然箱,而对于某些箱型来说,它可以相当于2个或者3个更多的“标箱”。现在就是需要统计总共进行了多少“标箱”的业务,进行了多少“自然箱”的业务,也就是要把以上统计出来的数据,每个箱型再乘以一个系数(自然箱就是1,标箱的系数则要查表),然后再求和,算出用户某段时间内总共做了多少自然箱和多少标箱。箱型和其相当于多少标箱的比例我存储成一个张表cargo_size, 字段包括size_name 和 ratio,分别代表箱型的名称和其相当于几个标箱。
请各位SQL高手帮忙!
With RS
.Open strsql, CN, adOpenKeyset, adLockReadOnly
rsXCount = .RecordCount
If rsXCount > 0 Then
.MoveFirst
ReDim arrX(1, rsXCount - 1) As String
For i = 1 To rsXCount
arrX(0, i - 1) = RS("UseType") '用于页面按列排序时传列名
arrX(1, i - 1) = RS("UseTypeName") '保存X轴到返回数据
strX = strX & ",sum(case UseType when '" & RS("usetype") & "' then [UseScore] else 0 end) as " & RS("usetype")
.MoveNext
Next
Else
strErr = "系统无法取得X轴的项目"
GoTo err:
End If
.Close
End With'-/查询用户使用类型数据
strErr = "系统在统计相关数据时异常结束"
'区分统计对象
If LCase(msSortObject) = "company" Then
strsql = "select uit.company " & strX & " from (select u.userid,c.companyid,c.company from userinfotable u,(select companyid,company from companytable union select cast(companyid as int) as companyid,company from systemcompanytable) c " & _
"where u.companyid=c.companyid) uit left join useruserecord uuc on uit.userid=uuc.userid group by uit.company " & strOrderBy
Else
strsql = "select uit.UserId " & strX & " from UserInfoTable uit left join UserUseRecord uur on uit.UserID=uur.UserID group by uit.UserID " & strOrderBy
End If
现在就是需要统计总共进行了多少“标箱”的业务,进行了多少“自然箱”的业务,也就是要把以上统计出来的数据,每个箱型再乘以一个系数(自然箱就是1,标箱的系数则要查表)
”
自然箱、标箱的倍数关系都可以放到cargo_size表里,做统一处理比较好点
create table #tb(tran_register_date datetime,tran_client_id varchar(20),tran_cargo_size varchar(20))insert into #tb Select '2006-7-21','jack','箱型1' union all
Select '2006-7-22','jack','箱型3' union all
Select '2006-7-23','jack','箱型1' union all
Select '2006-7-23','jack','箱型4' union all
Select '2006-7-21','mike','箱型1' union all
Select '2006-7-22','mike','箱型1' union all
Select '2006-7-23','mike','箱型3' union all
Select '2006-7-21','jim','箱型2' union all
Select '2006-7-21','jim','箱型5'
declare @s varchar(8000),
@size varchar(20)set @s = ''
Declare t cursor local for
select distinct tran_cargo_size from #tb
open t
fetch next from t into @size
while @@fetch_status=0
begin
select @s = @s + ',' + 'sum(case when tran_cargo_size=''' + @size + ''' then 1 else 0 end) as ' + @size
fetch next from t into @size
end
close t
deallocate tset @s = stuff(@s,1,1,'')
set @s = 'select tran_client_id,'+ @s + ' from #tb
where tran_register_date between ''2006-7-21'' and ''2006-7-23''
group by tran_client_id'print @s
execute (@s)
go
--执行查询
declare @s varchar(4000)set @s=''
select @s=@s+','+xtype+'=isnull((select sum(xnum*'+xsize+') from xmake where xtype='''+xtype+''' And PersonName=a.PersonName ),0)' from xtypetableset @s='select PersonName '+@s+' from xmake a Group by PersonName 'exec(@s)--删除测试环境
drop table xtypetable
drop table xmake
不对啊,
李四应该总共由3笔自然箱业务
张三有4笔
select 客户 ,
sum(case when 箱型 = '箱型1' then 1 else 0 end ) as 箱型1,
sum(case when 箱型 = '箱型2' then 1 else 0 end ) as 箱型2,
sum(case when 箱型 = '箱型3' then 1 else 0 end ) as 箱型3,
sum(case when 箱型 = '箱型4' then 1 else 0 end ) as 箱型4,
sum(case when 箱型 = '箱型5' then 1 else 0 end ) as 箱型5
from tab
group by 客户