统计问题

[fashchina]

一个数据库结构

车辆 进场时间 出场时间
浙AXXX 2009-06-01 12:31:23 2009-06-01 17:31:23
浙BXXX 2009-06-02 12:31:23 2009-06-03 17:31:23


需要:
对每天的车辆分进和出进行分组统计.

如
时间 进 出
2009-06-01 1 1
2009-06-02 1 0
2009-06-03 0 1

最开始的思路

[fashchina]

上面的答案都是有不对的.就是如果某天只有进的或者只有出的，就会有问题了



通过union all ,会有时间为null的.统计后,就出现了一列null的统计数

[pt1314917]

[Quote=引用 8 楼 yxhzj 的回复:]
上面的答案都是有不对的.就是如果某天只有进的或者只有出的，就会有问题了


通过union all ,会有时间为null的.统计后,就出现了一列null的统计数
[/Quote]
2号和3号不就是只有进的或者只有出的么？为什么不行？

--> 测试数据: @a
declare @a table (车辆 varchar(6),进场时间 datetime,出场时间 datetime)
insert into @a
select '浙AXXX','2009-06-01 12:31:23','2009-06-01 17:31:23' union all
select '浙BXXX','2009-06-02 12:31:23','2009-06-03 17:31:23'

select 时间=convert(varchar(10),时间,120),进=sum(case type when 1 then 1 else 0 end),
出=sum(case type when 0 then 1 else 0 end)
from
(select 时间=进场时间,type=1 from @a
union all
select 时间=出场时间,0 from @a)a
group by convert(varchar(10),时间,120)

[fashchina]

已经解决了.谢谢您们，

之前有个地方想错了.

[fashchina]

出的时间,有可能是空的

[liangCK]

-------------------------------------------
--  Author: liangCK 小梁 & angellan 兰儿
--  Date  : 2009-05-22 10:11:20
-------------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (车辆 VARCHAR(6),进场时间 DATETIME,出场时间 DATETIME)
INSERT INTO @T
SELECT '浙AXXX','2009-06-01 12:31:23','2009-06-01 17:31:23' UNION ALL
SELECT '浙BXXX','2009-06-02 12:31:23','2009-06-03 17:31:23'

--SQL查询如下:

SELECT
    CONVERT(VARCHAR(10),时间,120) AS 时间,
    SUM(CASE WHEN flag='IN' THEN 1 ELSE 0 END) AS 进,
    SUM(CASE WHEN flag='OUT' THEN 1 ELSE 0 END) AS 出
FROM (
    SELECT 进场时间 AS 时间,'IN' AS flag FROM @T
    UNION ALL
    SELECT 出场时间,'OUT' FROM @T
) AS T
GROUP BY CONVERT(VARCHAR(10),时间,120)

/*
时间         进           出
---------- ----------- -----------
2009-06-01 1           1
2009-06-02 1           0
2009-06-03 0           1

(3 row(s) affected)
*/

[jwdream2008]

帮顶了，关注。。。。。。。。。

[tianqing52]

来了，就顶一下

[jia_guijun]

declare @t table(Car varchar(10),InTime datetime,OutTime datetime)
insert @t values('浙AXXX','2009-06-01 12:31:23','2009-06-01 17:31:23')
insert @t values('浙BXXX','2009-06-02 12:31:23','2009-06-03 17:31:23')

select Time,[In]=sum(Case when Class='in' then 1 else 0 end),
[Out]=sum(Case when Class='Out' then 1 else 0 end)
From
(select Time=Convert(varchar(10),Intime,120),Class='In',Con=Count(1) from @t Group by Convert(varchar(10),Intime,120)
union all
select Time=Convert(varchar(10),Outtime,120),Class='Out',Con=Count(1) from @t Group by Convert(varchar(10),Outtime,120)) t
group by Time
/*
Time In Out
---------- ----------- -----------
2009-06-01 1 1
2009-06-02 1 0
2009-06-03 0 1
*/

[jia_guijun]

declare @t table(Car varchar(10),InTime datetime,OutTime datetime)
insert @t values('浙AXXX','2009-06-01 12:31:23','2009-06-01 17:31:23')
insert @t values('浙BXXX','2009-06-02 12:31:23','2009-06-03 17:31:23')

select Time,[In]=sum(Case when Class='in' then 1 else 0 end),
	[Out]=sum(Case when Class='Out' then 1 else 0 end)
From
	(select Time=Convert(varchar(10),Intime,120),Class='In',Con=Count(1) from @t Group by Convert(varchar(10),Intime,120)
	union all
	select Time=Convert(varchar(10),Outtime,120),Class='Out',Con=Count(1) from @t Group by Convert(varchar(10),Outtime,120)) t
group by Time
/*
Time       In          Out
---------- ----------- -----------
2009-06-01 1           1
2009-06-02 1           0
2009-06-03 0           1
*/

[-无-为-]

顶一下...

[ai_li7758521]

CREATE table TB
(
	车辆 Nvarchar(20),
	进场时间 smalldatetime,
	出场时间 smalldatetime
)

insert tb
select '浙AXXX', '2009-06-01 12:31:23', '2009-06-01 17:31:23' union all
select '浙BXXX', '2009-06-02 12:31:23', '2009-06-03 17:31:23'


SELECT 时间,SUM(CASE WHEN CONVERT(nvarchar(10),进场时间,120)=时间 then 1 else 0 end) [进]
			,SUM(CASE WHEN CONVERT(nvarchar(10),出场时间,120)=时间 then 1 else 0 end) [出]
FROM
(
	SELECT DISTINCT 时间
	FROM
	(	select CONVERT(nvarchar(10),进场时间,120) 时间 from tb
		union all
		select CONVERT(nvarchar(10),出场时间,120) from tb
	) A
) T,tb
GROUP by 时间

/*
时间         进           出
---------- ----------- -----------
2009-06-01 1           1
2009-06-02 1           0
2009-06-03 0           1
*/