34,576
社区成员
发帖
与我相关
我的任务
分享计算总数.
id time or state shop price totime
-------------------------------------------------------------------------------------------------
10 2009-09-23 14:06:59.000 1 New 天河店 100.0000 2009-09-22 14:06:58.577
11 2009-09-23 14:06:59.000 2 New 天河店 100.0000 2009-09-22 14:06:58.577
13 2009-09-23 14:06:59.000 4 defer 天河店 100.0000 2009-09-22 14:06:58.577
15 2009-09-23 14:06:59.000 7 New 天河店 100.0000 2009-09-22 14:06:58.577
16 2009-09-23 14:06:59.000 6 New 天河店 100.0000 2009-09-22 14:06:58.577
18 2009-09-23 14:06:59.000 8 assign 天河店 100.0000 2009-09-22 14:06:58.577
20 2009-09-23 14:06:59.000 10 New 天河店 100.0000 2009-09-22 14:06:58.577
22 2009-09-23 14:06:59.000 12 New 天河店 100.0000 2009-09-22 14:06:58.577
请问怎么计算当天的state总数,和各个state的总数
比如
总数 New defer assign
8 6 1 1
-------------------------------------------------------------------------------------------------
10 2009-09-23 14:06:59.000 1 New 天河店 100.0000 2009-09-22 14:06:58.577
11 2009-09-23 14:06:59.000 2 New 天河店 100.0000 2009-09-22 14:06:58.577
13 2009-09-23 14:06:59.000 4 defer 天河店 100.0000 2009-09-22 14:06:58.577
15 2009-09-23 14:06:59.000 7 New 天河店 100.0000 2009-09-22 14:06:58.577
16 2009-09-23 14:06:59.000 6 New 天河店 100.0000 2009-09-22 14:06:58.577
18 2009-09-23 14:06:59.000 8 assign 天河店 100.0000 2009-09-22 14:06:58.577
20 2009-09-23 14:06:59.000 10 New 天河店 100.0000 2009-09-22 14:06:58.577
22 2009-09-23 14:06:59.000 12 New 天河店 100.0000 2009-09-22 14:06:58.577
请问怎么计算当天的state总数,和各个state的总数
比如
总数 New defer assign
8 6 1 1
select count(*) from OrderList tb where ShopName='天河店' and day(AcceptTime)=day(getdate())
...全文
请发表友善的回复…
发表回复
智者潜行 2009-09-23
- 打赏
- 举报
高手真多,结帖!!
htl258_Tony 2009-09-23
- 打赏
- 举报
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-23 14:49:02
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[time] DATETIME,[or] INT,[state] NVARCHAR(10),[shop] NVARCHAR(10),[price] DECIMAL(18,4),[totime] DATETIME)
INSERT [tb]
SELECT 10,N'2009-09-23 14:06:59.000',1,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 11,N'2009-09-23 14:06:59.000',2,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 13,N'2009-09-23 14:06:59.000',4,'defer',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 15,N'2009-09-23 14:06:59.000',7,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 16,N'2009-09-23 14:06:59.000',6,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 18,N'2009-09-23 14:06:59.000',8,'assign',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 20,N'2009-09-23 14:06:59.000',10,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 22,N'2009-09-23 14:06:59.000',12,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
SELECT *
FROM (
SELECT 总数=COUNT(1)OVER(PARTITION BY [shop]),[state]
FROM tb
WHERE [shop]='天河店'
) a
PIVOT(COUNT([state]) FOR [STATE] IN(new,defer,assign))b
/*
总数 new defer assign
----------- ----------- ----------- -----------
8 6 1 1
(1 行受影响)
*/
ws_hgo 2009-09-23
- 打赏
- 举报
CREATE TABLE [tb]([id] INT,[time] DATETIME,[or] INT,[state] NVARCHAR(10),[shop] NVARCHAR(10),[price] DECIMAL(18,4),[totime] DATETIME)
INSERT [tb]
SELECT 10,N'2009-09-23 14:06:59.000',1,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 11,N'2009-09-23 14:06:59.000',2,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 13,N'2009-09-23 14:06:59.000',4,'defer',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 15,N'2009-09-23 14:06:59.000',7,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 16,N'2009-09-23 14:06:59.000',6,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 18,N'2009-09-23 14:06:59.000',8,'assign',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 20,N'2009-09-23 14:06:59.000',10,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 22,N'2009-09-23 14:06:59.000',12,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577'
GO
select count([id]) '总数',
sum(case when [state]='New' then 1 else 0 end) 'New',
sum(case when [state]='defer' then 1 else 0 end) 'defer',
sum(case when [state]='assign' then 1 else 0 end) 'assign'
from [tb] where convert(varchar(10),[time],120)=convert(varchar(10),getdate(),120)ws_hgo 2009-09-23
- 打赏
- 举报
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
CREATE TABLE [tb]([id] INT,[time] DATETIME,[or] INT,[state] NVARCHAR(10),[shop] NVARCHAR(10),[price] DECIMAL(18,4),[totime] DATETIME)
INSERT [tb]
SELECT 10,N'2009-09-23 14:06:59.000',1,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 11,N'2009-09-23 14:06:59.000',2,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 13,N'2009-09-23 14:06:59.000',4,'defer',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 15,N'2009-09-23 14:06:59.000',7,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 16,N'2009-09-23 14:06:59.000',6,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 18,N'2009-09-23 14:06:59.000',8,'assign',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 20,N'2009-09-23 14:06:59.000',10,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 22,N'2009-09-23 14:06:59.000',12,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577'
GO
select count([id]) '总数',
sum(case when [state]='New' then 1 else 0 end) 'New',
sum(case when [state]='defer' then 1 else 0 end) 'defer',
sum(case when [state]='assign' then 1 else 0 end) 'assign'
from [tb]
总数 New defer assign
----------- ----------- ----------- -----------
8 6 1 1
--小F-- 2009-09-23
- 打赏
- 举报
...
7761098 2009-09-23
- 打赏
- 举报
正解
htl258_Tony 2009-09-23
- 打赏
- 举报
-->SQL查询如下:
SELECT
COUNT([state]) 总数,
SUM(CASE [state] WHEN 'new' THEN 1 ELSE 0 END) [new],
SUM(CASE [state] WHEN 'defer' THEN 1 ELSE 0 END) [defer],
SUM(CASE [state] WHEN 'assign' THEN 1 ELSE 0 END) [assign]
FROM tb
WHERE ShopName='天河店' AND DATEDIFF(dd,TIME,GETDATE())=0
feixianxxx 2009-09-23
- 打赏
- 举报
select
总数=count(*),
New=sum(case [state] when 'New' then 1 else 0 end),
defer=sum(case [state] when 'defer' then 1 else 0 end),
assign=sum(case [state] when 'assign' then 1 else 0 end)
from OrderList
where ShopName='天河店' and datepart(day,AcceptTime)=datepart(day,getdate()) 漏掉括号了
转转漏了个字母..
htl258_Tony 2009-09-23
- 打赏
- 举报
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-23 14:49:02
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[time] DATETIME,[or] INT,[state] NVARCHAR(10),[shop] NVARCHAR(10),[price] DECIMAL(18,4),[totime] DATETIME)
INSERT [tb]
SELECT 10,N'2009-09-23 14:06:59.000',1,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 11,N'2009-09-23 14:06:59.000',2,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 13,N'2009-09-23 14:06:59.000',4,'defer',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 15,N'2009-09-23 14:06:59.000',7,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 16,N'2009-09-23 14:06:59.000',6,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 18,N'2009-09-23 14:06:59.000',8,'assign',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 20,N'2009-09-23 14:06:59.000',10,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577' UNION ALL
SELECT 22,N'2009-09-23 14:06:59.000',12,'New',N'天河店',100.0000,N'2009-09-22 14:06:58.577'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
SELECT
COUNT([state]) 总数,
SUM(CASE [state] WHEN 'new' THEN 1 ELSE 0 END) [new],
SUM(CASE [state] WHEN 'defer' THEN 1 ELSE 0 END) [defer],
SUM(CASE [state] WHEN 'assign' THEN 1 ELSE 0 END) [assign]
FROM tb
WHERE DATEDIFF(dd,TIME,GETDATE())=0
/*
总数 new defer assign
----------- ----------- ----------- -----------
8 6 1 1
(1 行受影响)
*/
lass_name 2009-09-23
- 打赏
- 举报
顶ls
feixianxxx 2009-09-23
- 打赏
- 举报
select
总数=count(*),
New=sum(case [state] when 'New' then 1 else 0 end),
defer=sum(case [state] when 'defer' then 1 else 0 end),
assig=sum(case [state] when 'assign' then 1 else 0 end)
from tbname
where ShopName='天河店' and datepart(day,AcceptTime)=datepart(day,getdate(
子陌红尘 2009-09-23
- 打赏
- 举报
select
总数 =count(*),
New =sum(case state when 'New' then 1 else 0 end),
defer =sum(case state when 'defer' then 1 else 0 end),
assign =sum(case state when 'assign' then 1 else 0 end)
from
表
where
ShopName='天河店' and datediff(dd,AcceptTime,getdate())=0
