数据依照时间统计

2013-08-04

数据按照时间统计本人小菜现在遇到一个问题。现有数据库LocationCodeScanTime OSI-11-31-N2013-07-17 04:00

数据按照时间统计
本人小菜现在遇到一个问题。

现有数据库
LocationCode            ScanTime
OSI-11-31-N       2013-07-17 04:00:18.000
OSI-11-31-N       2013-07-17 05:21:55.000
OSI-11-31-N       2013-07-17 06:00:18.000
VAD-07-01-B       2013-07-17 07:00:18.000
OSI-11-31-N       2013-07-17 08:00:18.000
VAD-07-01-B       2013-07-17 09:00:18.000
OSI-11-31-N       2013-07-18 10:00:18.000
OSI-11-31-N       2013-07-18 11:00:18.000
VAD-07-01-B       2013-07-18 12:00:18.000
OSI-11-31-N       2013-07-18 13:00:18.000
OSI-11-31-N       2013-07-18 14:00:18.000

现在要按照ScanTime统计每天不同LocationCode的数据条数，需要得到的数据如下：
              OSI-11-31-N         VAD-07-01-B
2013-07-17       4                     2
2013-07-18       4                     1

貌似很难，求大牛指点
[解决办法]



create table test(LocationCode nvarchar(15),ScanTime datetime)
insert into test values ('OSI-11-31-N','2013-07-17 04:00:18.000')
insert into test values ('OSI-11-31-N','2013-07-17 05:21:55.000')
insert into test values ('OSI-11-31-N','2013-07-17 06:00:18.000')
insert into test values ('VAD-07-01-B','2013-07-17 07:00:18.000')
insert into test values ('OSI-11-31-N','2013-07-17 08:00:18.000')
insert into test values ('VAD-07-01-B','2013-07-17 09:00:18.000') 
 
insert into test values ('OSI-11-31-N','2013-07-18 10:00:18.000')
insert into test values ('OSI-11-31-N','2013-07-18 11:00:18.000')
insert into test values ('VAD-07-01-B','2013-07-18 12:00:18.000')
insert into test values ('OSI-11-31-N','2013-07-18 13:00:18.000')
insert into test values ('OSI-11-31-N','2013-07-18 14:00:18.000')

select convert(varchar(10),ScanTime,120) ScanTime,
[OSI-11-31-N]=SUM(case when LocationCode='OSI-11-31-N' then 1 else 0 end),
[VAD-07-01-B]=SUM(case when LocationCode='VAD-07-01-B' then 1 else 0 end)
from test
group by convert(varchar(10),ScanTime,120)

/*
ScanTimeOSI-11-31-NVAD-07-01-B
2013-07-1742
2013-07-1841
*/

[解决办法]

----------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-08-01 14:08:44
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
--Jun 10 2013 20:09:10 
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([LocationCode] varchar(11),[ScanTime] datetime)
insert [huang]
select 'OSI-11-31-N','2013-07-17 04:00:18.000' union all
select 'OSI-11-31-N','2013-07-17 05:21:55.000' union all
select 'OSI-11-31-N','2013-07-17 06:00:18.000' union all
select 'VAD-07-01-B','2013-07-17 07:00:18.000' union all
select 'OSI-11-31-N','2013-07-17 08:00:18.000' union all
select 'VAD-07-01-B','2013-07-17 09:00:18.000' union all
select 'OSI-11-31-N','2013-07-18 10:00:18.000' union all
select 'OSI-11-31-N','2013-07-18 11:00:18.000' union all
select 'VAD-07-01-B','2013-07-18 12:00:18.000' union all
select 'OSI-11-31-N','2013-07-18 13:00:18.000' union all
select 'OSI-11-31-N','2013-07-18 14:00:18.000'
--------------开始查询--------------------------
SELECT ScanTime,'OSI-11-31-N'=MAX(CASE WHEN LocationCode='OSI-11-31-N' THEN [count] END) ,'VAD-07-01-B'=MAX(CASE WHEN LocationCode='VAD-07-01-B' THEN [count] END)  
 
FROM (
SELECT LocationCode,CONVERT(DATE,[ScanTime])[ScanTime],COUNT(LocationCode)[COUNT]
FROM [huang]
GROUP BY LocationCode,CONVERT(DATE,[ScanTime]))a
GROUP BY ScanTime
----------------结果----------------------------
/* 
ScanTime   OSI-11-31-N VAD-07-01-B
---------- ----------- -----------
2013-07-17 4           2
2013-07-18 4           1
*/

[解决办法]

IF object_id('tempdb..#cu1') is not null
DROP table #cu1;
IF object_id('tempdb..#cu2') is not null
DROP table #cu2;

WITH a1 (LocationCode,ScanTime ) as
(
SELECT 'OSI-11-31-N','2013-07-17 04:00:18.000' UNION all  
SELECT 'OSI-11-31-N','2013-07-17 05:21:55.000' UNION all  
SELECT 'OSI-11-31-N','2013-07-17 06:00:18.000' UNION all  
SELECT 'VAD-07-01-B','2013-07-17 07:00:18.000' UNION all  
SELECT 'OSI-11-31-N','2013-07-17 08:00:18.000' UNION all  
SELECT 'VAD-07-01-B','2013-07-17 09:00:18.000' UNION all  
SELECT 'OSI-11-31-N','2013-07-18 10:00:18.000' UNION all  
SELECT 'OSI-11-31-N','2013-07-18 11:00:18.000' UNION all  
SELECT 'VAD-07-01-B','2013-07-18 12:00:18.000' UNION all  
SELECT 'OSI-11-31-N','2013-07-18 13:00:18.000' UNION all  
SELECT 'OSI-11-31-N','2013-07-18 14:00:18.000'   
)
,a2 AS
(
SELECT LocationCode,CONVERT(CHAR(10),ScanTime,20) ScanTime FROM a1
)
SELECT LocationCode,ScanTime,COUNT(*) AS qty
INTO #cu1
FROM a2
GROUP BY LocationCode,ScanTime

select distinct LocationCode into #cu2 from #cu1

declare @sql varchar(max),@sql2 varchar(max)
select @sql = isnull(@sql + '],[' , '') + LocationCode from #cu2
set @sql = '[' + @sql + ']'
select @sql2 = isnull(@sql2 + ',' , ',') + 'isnull(['+LocationCode+'],0) ['+LocationCode+']' from #cu2
set @sql='select ScanTime'+@sql2+' from #cu1 a pivot (max(qty) for LocationCode in (' + @sql + ')) b'
exec (@sql)

[解决办法]

人呢？

列固定的话就是行列转换 1，3楼都可以
列不固定的话，动态列4楼应该可以网上好多例子的
[解决办法]
经过千辛万苦，我终于帮你写出来了，你试试看行不行，尽快联系我


select ScanTime,[OSI-11-31-N],[VAD-07-01-B]
from
(
select  [LocationCode],ScanTime,count(*) as total
from
(
  SELECT [LocationCode],convert(varchar(10),[ScanTime],120) as ScanTime
  FROM [master].[dbo].[Table_1]
 ) a
 group by a.[LocationCode],a.ScanTime
 ) b
 pivot
 (
    sum(total) for b.[LocationCode] in ([OSI-11-31-N],[VAD-07-01-B])
 )
as pvt

热点排行