借人气 求SQL

[fencole]

表结构字段和数据如下：
CustomerName ArriveTime FactTime --客户名称 应到达时间 实际到达时间
联想集团 08:00 08:15
康佳 09:00 09:30
康佳 09:00 09:00
康佳 09:00 09:20
联想集团 08:00 08;00
TCL 08:30 09;00

如果 ArriveTime小于FactTime就代表迟到
要求汇总结果如下
客户名称 迟到次数 总次数
联想集团 1 2
康佳 2 3
TCL 1 1

[nicochang]

你是什么数据库，你的时间字段是什么类型的

[weizhuangzhi]

up

[smile9961]

select x.customerName, x.late, y.total
from
(
select customerName, count(1) as late
from
(
select *
from yourtable
where arrivetime < facttime
) a
group by customerName
) x
inner join
(
select customerName, count(1) as total
from yourtable
group by customerName
) y
on x.customerName = y.customerName

[smile9961]

create table yourtable(customerName nvarchar(10), arrivetime nvarchar(5), facttime nvarchar(5))

insert into yourtable
select N'联想集团', '08:00' ,'08:15' union all
select N'康佳' , '09:00' ,'09:30' union all
select N'康佳' , '09:00' ,'09:00' union all
select N'康佳' , '09:00' ,'09:20' union all
select N'联想集团' , '08:00' ,'08:00' union all
select N'TCL', '08:30', '09:00'

select x.customerName, x.late, y.total
from
(
select customerName, count(1) as late
from
(
select *
from yourtable
where arrivetime < facttime
) a
group by customerName
) x
inner join
(
select customerName, count(1) as total
from yourtable
group by customerName
) y
on x.customerName = y.customerName

感觉可以更简单些;先吃饭去了.

[smile9961]

修改一下：
select customerName, count(1) as late, (select count(1) from yourtable where customerName = x.customerName group by customerName) as total
from yourtable x
where arrivetime < facttime
group by customerName

[xinfan]

借用一下上面创建的表 语句如下
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
select a.customerName as 客户名称,sum(a.flat) as 迟到次数 ,count(customerName) as 总次数 from
(select customerName,case when arrivetime<facttime then 1 else 0 end as flat
from yourtable) as a
group by a.customerName

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
我解释一下语句
select customerName,case when arrivetime<facttime then 1 else 0 end as flat
from yourtable
得出每条记录得迟到情况 迟到为1 否则为0

然后对其分组 求和 求次数 就可以了

[xinfan]

如果需要根据customerName排序 可以在最后加上 order by a.customerName desc

[paoluo]

是不是都想複雜了，這個語句不需要用到子查詢的，一個Select就可以實現的


Select
CustomerName,
SUM(Case When ArriveTime < FactTime Then 1 Else 0 End) As 迟到次数,
Count(*) As 总次数
From
TEST
Group By
CustomerName

[paoluo]

--創建測試環境
Create Table TEST(CustomerName Nvarchar(10), ArriveTime Varchar(5), FactTime Varchar(5))
--插入數據
Insert Into TEST
Select N'联想集团', '08:00', '08:15' Union All
Select N'康佳', '09:00', '09:30' Union All
Select N'康佳', '09:00', '09:00' Union All
Select N'康佳', '09:00', '09:20' Union All
Select N'联想集团', '08:00', '08:00' Union All
Select N'TCL', '08:30', '09:00'
GO
--測試
Select
CustomerName,
SUM(Case When ArriveTime < FactTime Then 1 Else 0 End) As 迟到次数,
Count(*) As 总次数
From
TEST
Group By
CustomerName
GO
--刪除測試環境
Drop Table TEST
--結果
/*
CustomerName 迟到次数 总次数
TCL 1 1
康佳 2 3
联想集团 1 2
*/

[smile9961]

呵呵，是想多了；还是paoluo的方法好。

[weizhuangzhi]

学习