描述:数据库中字段和数字如下所示详情
其中status字段:o代表未清、l代表损失、w代表赢取
cardcode------------------cardcaname-------------status
c00001----------------------新巍峨-----------------o
c00001----------------------新巍峨-----------------o
c00001----------------------新巍峨-----------------l
c00001----------------------新巍峨-----------------w
c00002----------------------美帝------------------l
c00002----------------------美帝------------------w
c00003---------------------华夏-------------------w
c00003---------------------华夏-------------------w用sql语句得到如下结果:
伙伴代码-----------------伙伴名称---------------总计-----------未清总计----------赢取总计--------损失总计
c00001-------------------新巍峨-----------------4----------------2----------------1----------------1
c00002-------------------美帝-------------------2----------------0----------------1----------------1
c00003-------------------华夏-------------------2----------------0----------------2----------------0求详解。各位大虾帮帮忙。
其中status字段:o代表未清、l代表损失、w代表赢取
cardcode------------------cardcaname-------------status
c00001----------------------新巍峨-----------------o
c00001----------------------新巍峨-----------------o
c00001----------------------新巍峨-----------------l
c00001----------------------新巍峨-----------------w
c00002----------------------美帝------------------l
c00002----------------------美帝------------------w
c00003---------------------华夏-------------------w
c00003---------------------华夏-------------------w用sql语句得到如下结果:
伙伴代码-----------------伙伴名称---------------总计-----------未清总计----------赢取总计--------损失总计
c00001-------------------新巍峨-----------------4----------------2----------------1----------------1
c00002-------------------美帝-------------------2----------------0----------------1----------------1
c00003-------------------华夏-------------------2----------------0----------------2----------------0求详解。各位大虾帮帮忙。
INSERT INTO @t
SELECT 'c00001','新巍峨','o'
UNION ALL SELECT 'c00001','新巍峨','o'
UNION ALL SELECT 'c00001','新巍峨','l'
UNION ALL SELECT 'c00001','新巍峨','w'
UNION ALL SELECT 'c00002','美帝','-l'
UNION ALL SELECT 'c00002','美帝','-w'
UNION ALL SELECT 'c00003','华夏','w'
UNION ALL SELECT 'c00003','华夏','w'SELECT cardcode,cardcaname,COUNT(cardcode) AS '总数',
sum(CASE STATUS WHEN 'o' THEN 1 ELSE 0 end) AS '未清',
sum(CASE STATUS WHEN 'l' THEN 1 ELSE 0 end) AS '赢取总计',
sum(CASE STATUS WHEN 'w' THEN 1 ELSE 0 end) AS '赢取总计'
FROM @t
GROUP BY cardcode,cardcaname
/*****************************/(所影响的行数为 8 行)cardcode cardcaname 总数 未清 赢取总计 赢取总计
---------- ---------- ----------- ----------- ----------- -----------
c00003 华夏 2 0 0 2
c00002 美帝 2 0 0 0
c00001 新巍峨 4 2 1 1(所影响的行数为 3 行)
sum(case when [type]='进货' then materialNum end) as 进货数量,
sum(case when [type]='出货' then materialNum end) as 出货数量,
sum(case when [type]='进货' then materialNum end)-sum(case when [type]='出货' then materialNum end) as 库存数量 from Material group by materialName按照这个思路来可以搞定
drop table tb_test
create table tb_test(cardcode varchar(6),cardcaname varchar(6),status varchar(1))
delete from tb_test
insert into tb_test(cardcode,cardcaname,status) select 'c00001','新巍峨','0' union all
select 'c00002','新巍峨','0' union all
select 'c00001','新巍峨','1' union all
select 'c00001','新巍峨','w' union all
select 'c00002','美帝','1' union all
select 'c00002','美帝','w' union all
select 'c00003','华夏','w' union all
select 'c00003','华夏','w'select * from tb_testselect a.*,isnull(b.未清,0)as 未清总计,isnull(c.损失,0)as 损失总计,isnull(d.赢利,0) as 赢利 from (select cardcode as 伙伴名称,count(1) as 总计 from tb_test group by cardcode)a
left join
(
select cardcode as 伙伴名称,count(1)as 未清 from tb_test where status='0' group by cardcode
) b on a.伙伴名称=b.伙伴名称
left join
(
select cardcode as 伙伴名称,count(1)as 损失 from tb_test where status='1' group by cardcode
) c on a.伙伴名称=c.伙伴名称
left join
(
select cardcode as 伙伴名称,count(1)as 赢利 from tb_test where status='w' group by cardcode
) d on a.伙伴名称=d.伙伴名称 伙伴名称 总计 未清总计 损失总计 赢利
c00001 3 1 1 1
c00002 3 1 1 1
c00003 2 0 0 2
if exists(select 1 from sys.objects where object_id=object_id('tb_test') and type='u')
drop table tb_test
create table tb_test(ID int identity(1,1),开始时间 datetime,结束时间 datetime)
delete from tb_test
insert into tb_test(结束时间,开始时间)
select cast('2010-05-29 00:04:02' as datetime),cast('2010-05-29 00:04:12' as datetime) union all
select cast('2010-05-29 00:04:02' as datetime),cast('2010-05-29 00:04:7' as datetime) union all
select cast('2010-05-29 00:03:58' as datetime),cast('2010-05-29 00:04:8' as datetime) union all
select cast('2010-05-29 00:03:02' as datetime),cast('2010-05-29 00:03:05' as datetime) union all
select cast('2010-04-29 00:04:02' as datetime),cast('2010-04-29 00:04:12' as datetime) union all
select cast('2010-04-29 00:04:02' as datetime),cast('2010-04-29 00:04:07' as datetime) union all
select cast('2010-04-29 00:03:58' as datetime),cast('2010-04-29 00:04:08' as datetime) union all
select cast('2010-03-29 00:04:02' as datetime),cast('2010-03-29 00:04:12' as datetime) union all
select cast('2010-03-29 00:04:02' as datetime),cast('2010-03-29 00:04:07' as datetime) union all
select cast('2010-03-29 00:03:58' as datetime),cast('2010-03-29 00:04:03' as datetime)存放所有月份表
if exists(select 1 from sys.objects where object_id=object_id('tb_month') and type='u')
drop table tb_month
create table tb_month(intmonth int)
delete from tb_month
insert into tb_month(intmonth)
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 一个用来返回两个日期间相差的秒数(我这个只能做到秒级别的,如需要毫秒自己加)
create function GetDateSpanTotalSecondOf2Days(@begintime datetime,@endtime datetime)
returns int
begin
declare @TimeSpan datetime
set @TimeSpan=@endtime-@begintime
declare @TimeSpanYear int
declare @TimeSpanMonth Int
declare @TimeSpanDay int
declare @TimeSpanHour int
declare @TimeSpanMinite int
declare @TimeSpanSecond int
set @TimeSpanYear=datepart(yy,@TimeSpan)-1900
set @TimeSpanMonth=datepart(mm,@TimeSpan)-1
set @TimeSpanDay=datepart(dd,@TimeSpan)-1
set @TimeSpanHour=datepart(hh,@TimeSpan)
set @TimeSpanMinite=datepart(n,@TimeSpan)
set @TimeSpanSecond =datepart(ss,@TimeSpan)
declare @TotalSeconds int
set @TotalSeconds=@TimeSpanYear*365*24*60*60+@TimeSpanMonth*30*24*60*60+@TimeSpanDay*24*60*60+ @TimeSpanHour*60*60+@TimeSpanMinite*60+@TimeSpanSecond
return @TotalSeconds
end
查询语句
select
cast(tm.intmonth as varchar)+'月' as 月份,
isnull(tt.相差5秒的次数,0)as 相差5秒的次数,
isnull(tt.相差10秒的次数,0)as 相差10秒的次数
from
tb_month tm
left join
(
select
datepart(mm,开始时间)as 月份,
count(case dbo.GetDateSpanTotalSecondOf2Days(开始时间,结束时间) when 5 then 1 else 0 end)as 相差5秒的次数,
count(case dbo.GetDateSpanTotalSecondOf2Days(开始时间,结束时间) when 10 then 1 else 0 end)as 相差10秒的次数
from tb_test
group by datepart(mm,开始时间)
)
tt
on tm.intmonth=tt.月份
结果月份 相差5秒的次数 相差10秒的次数
1月 0 0
2月 0 0
3月 3 3
4月 3 3
5月 4 4
6月 0 0
7月 0 0
8月 0 0
9月 0 0
10月 0 0
11月 0 0
12月 0 0以上测试sql2005通过