我数据库中有2张表
一张表TA结构如下:id为自增,date为月份,d1-d31为1号到31号,如果当前月没有对应的日期则为null
id date d1 d2 d3 d4 d5 ··· d31
1 2010-04-01 1 1 1 2 1 ··· null
2 2010-05-01 2 2 1 1 1 ··· 31第二张表TB结构如下:
id date
66 2010-04-19
67 2010-04-20
68 2010-05-1
69 2010-05-2现在我想统计出在TA表中4月对应日期为值为1的日期在TB表中存在的记录数以及不存在的记录数,应该怎么处理?
如果我需要统计在TA表中所有月份的呢?
一张表TA结构如下:id为自增,date为月份,d1-d31为1号到31号,如果当前月没有对应的日期则为null
id date d1 d2 d3 d4 d5 ··· d31
1 2010-04-01 1 1 1 2 1 ··· null
2 2010-05-01 2 2 1 1 1 ··· 31第二张表TB结构如下:
id date
66 2010-04-19
67 2010-04-20
68 2010-05-1
69 2010-05-2现在我想统计出在TA表中4月对应日期为值为1的日期在TB表中存在的记录数以及不存在的记录数,应该怎么处理?
如果我需要统计在TA表中所有月份的呢?
然后再统计
convert(varchar(7),date,120),
sum(case d1 when 1 then 1 else 0 end) as num
from
(
select convert(varchar(7),date,120) as [date],d1 from tb1
union all
select convert(varchar(7),date,120) as [date],d2 as d1 from tb1
....
)a,
tb2 b
where
convert(varchar(7),a.date,120)=convert(varchar(7),b.date,120)
select ta.* ,(select count(1) from tb where date = convert(varchar(8),ta.date,120) + '-01') from taselect ta.* ,
(select count(1) from tb where date = convert(varchar(8),ta.date,120) + '-01') ,
(select count(1) from tb where date = convert(varchar(8),ta.date,120) + '-02') ,
...
from ta
或者:最好给出完整的表结构,测试数据,计算方法和正确结果.发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
-- Author : htl258(Tony)
-- Date : 2010-04-30 11:49:29
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:taIF NOT OBJECT_ID('[ta]') IS NULL
DROP TABLE [ta]
GO
CREATE TABLE [ta]([id] INT,[date] DATETIME,[d1] INT,[d2] INT,[d3] INT,[d4] INT,[d5] INT)
INSERT [ta]
SELECT 1,N'2010-04-01',1,1,1,2,1 UNION ALL
SELECT 2,N'2010-05-01',2,2,1,1,1
GO
--SELECT * FROM [ta]--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[date] DATETIME)
INSERT [tb]
SELECT 66,N'2010-04-19' UNION ALL
SELECT 67,N'2010-04-20' UNION ALL
SELECT 68,N'2010-05-1' UNION ALL
SELECT 69,N'2010-05-2'
GO
--SELECT * FROM [tb]-->SQL查询如下:
select a.*,ISNULL(ap.cnt,0) cnt
from tb a
outer apply(
select cnt
from ta unpivot(cnt for b in([d1],[d2],[d3],[d4],[d5]))tb
where cast(CONVERT(char(8),date,23)+STUFF(b,1,1,'') as datetime)=a.date) ap
/*
id date cnt
----------- ----------------------- -----------
66 2010-04-19 00:00:00.000 0
67 2010-04-20 00:00:00.000 0
68 2010-05-01 00:00:00.000 2
69 2010-05-02 00:00:00.000 2(4 行受影响)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-30 11:53:53
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([id] int,[date] datetime,[d1] int,[d2] int,[d3] int,[d4] int,[d5] int,[d6] int)
insert [tb1]
select 1,'2010-04-01',1,1,1,2,1,null union all
select 2,'2010-05-01',2,2,1,1,1,2
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([id] int,[date] datetime)
insert [tb2]
select 66,'2010-04-1' union all
select 67,'2010-04-2' union all
select 68,'2010-05-1' union all
select 69,'2010-05-2'
--------------开始查询--------------------------
select
convert(varchar(7),a.date,120),
sum(case d1 when 1 then 1 else 0 end) as num
from
(
select convert(varchar(7),date,120) as [date],d1 from tb1
union all
select convert(varchar(7),date,120) as [date],d2 as d1 from tb1
union all
select convert(varchar(7),date,120) as [date],d3 as d1 from tb1
union all
select convert(varchar(7),date,120) as [date],d4 as d1 from tb1
union all
select convert(varchar(7),date,120) as [date],d5 as d1 from tb1
union all
select convert(varchar(7),date,120) as [date],d6 as d1 from tb1
)a,
tb2 b
where
convert(varchar(7),a.date,120)=convert(varchar(7),b.date,120)
group by
convert(varchar(7),a.date,120)
----------------结果----------------------------
/* num
------- -----------
2010-04 8
2010-05 6(2 行受影响)*/
第一张表TA:
id date d1 d2 d3 d4 d5 ··· d31
1 2010-04-01 1 1 1 2 1 全部为2 null
2 2010-05-01 2 2 1 1 2 全部为1 1第二张表TB:
id date
66 2010-04-2
67 2010-04-3
68 2010-04-4
69 2010-04-5
70 2010-05-1
71 2010-05-2
72 2010-05-3
73 2010-05-4
74 2010-05-5
75 2010-05-7
76 2010-05-31
正确结果应该是
4月份,ta表中日期对应值为1的在tb中有3个(66,67,69)
5月份,ta表中日期对应值为1的在tb中有4个(72,73,75,76)
现在应该算是将明白了吧
高手们再支招