DBA_Huangzj进

[waCtr]

有表table1
tid itmed
101 部门A
101 部门B
102 部门C
102 部门B
102 部门B
103 部门C

还有个表table2
tid timedate
101 2012-12-01
102 2012-12-03
103 2012-11-01

我要统计12年12月份 的 数据 条件怎么加呢？

[發糞塗牆]

两表先关联，先用where条件筛选你要统计的数据，然后放到那个#t里面

[waCtr]

declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename(itmed)+'=count(case when [itmed]='+quotename(itmed,'''')+' then 1 else null end)' from #t GROUP BY itmed exec('select COUNT( itmed) [所有部门]'+@s+' from #t ') 条件怎么加呢？

[發糞塗牆]

--CREATE TABLE #t (tid INT,      itmed VARCHAR(10))
 --INSERT  INTO #t
 
 --SELECT 101   ,     '部门A'
 --union all select 101  ,      '部门B'
 --union all select 102   ,     '部门C'
 --union all select 102  ,      '部门B'
 --union all select 102  ,      '部门B'
 --union all select 103  ,      '部门C'
 --CREATE TABLE #t2(tid INT ,        timedate DATE)
 --INSERT INTO #t2
 --SELECT 101,         '2012-12-01'
 --UNION ALL 
 --SELECT 102,         '2012-12-03'
 --UNION ALL 
 --SELECT 103,         '2012-11-01'
 
 SELECT * INTO #t3
 FROM #t a WHERE EXISTS (SELECT 1 FROM #t2 b WHERE a.tid=b.tid AND b.timedate BETWEEN '2012-12-01' AND '2012-12-31')
 
 
 declare @s nvarchar(4000)
 set @s=''
 Select     @s=@s+','+quotename(itmed)+'=count(case when [itmed]='+quotename(itmed,'''')+' then 1 else null end)'
 from #t3 GROUP BY itmed
 exec('select COUNT( itmed) [所有部门]'+@s+' from #t3 ')
 
 
 /*
 
 (5 行受影响)
 所有部门        部门A         部门B         部门C
 ----------- ----------- ----------- -----------
 5           1           3           1
 警告: 聚合或其他 SET 操作消除了 Null 值。
 
 (1 行受影响)
 
 */

[waCtr]

引用 1 楼 DBA_Huangzj 的回复:

两表先关联，先用where条件筛选你要统计的数据，然后放到那个#t里面

知道了

[發糞塗牆]

其实都换汤不换药而已。写的比较粗糙，你自己按照实际需要修改一下吧

[waCtr]

引用 3 楼 DBA_Huangzj 的回复:

SQL code?123456789101112131415161718192021222324252627282930313233343536373839--CREATE TABLE #t (tid INT, itmed VARCHAR(10)) --INSERT INTO #t --SELECT 101 , '部门A' --union al……

#t3 是零时表么？？

[發糞塗牆]

对，这样放到存储过程执行就不会提示表已经存在。你也可以用cte。目的都是一样，把你要进行转换的结果先存放到一个地方。

[發糞塗牆]

#开头的是局部临时表，##开头的是全局临时表。

[waCtr]

引用 5 楼 DBA_Huangzj 的回复:

其实都换汤不换药而已。写的比较粗糙，你自己按照实际需要修改一下吧

照你3楼的那样写 那日期条件不起作用？

[waCtr]

SELECT * INTO #t3 FROM #t a WHERE EXISTS (SELECT 1 FROM #t2 b WHERE a.tid=b.tid AND b.timedate BETWEEN '2012-12-01' AND '2012-12-31') declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename(itmed)+'=count(case when [itmed]='+quotename(itmed,'''')+' then 1 else null end)' from #t3 GROUP BY itmed exec('select COUNT( itmed) [所有部门]'+@s+' from #t3 ') 这些可以放在一个存储过程中么？

[waCtr]

declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename(itmed)+'=count(case when [itmed]='+quotename(itmed,'''')+' then 1 else null end)' from #t3 GROUP BY itmed exec('select COUNT( itmed) [所有部门]'+@s+' from #t3 ') 要是这里查 #t3的话 直接报对象名无效... itemd 肯定也没有

[發糞塗牆]

SELECT * INTO #t3 FROM #t a WHERE EXISTS (SELECT 1 FROM #t2 b WHERE a.tid=b.tid AND b.timedate BETWEEN '2012-12-01' AND '2012-12-31') declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename(itmed)+'=count(case when [itmed]='+quotename(itmed,'''')+' then 1 else null end)' from #t3 GROUP BY itmed exec('select COUNT( itmed) [所有部门]'+@s+' from #t3 ') 红字部分已经筛选了。另外你放到存储过程试一下就知道拉

[發糞塗牆]

SELECT * INTO #t3 FROM #t a WHERE EXISTS (SELECT 1 FROM #t2 b WHERE a.tid=b.tid AND b.timedate BETWEEN '2012-12-01' AND '2012-12-31') declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename(itmed)+'=count(case when [itmed]='+quotename(itmed,'''')+' then 1 else null end)' from #t3 GROUP BY itmed exec('select COUNT( itmed) [所有部门]'+@s+' from #t3 ')这些一起执行才可以，

[longai123]

什么时候我也可以做版主啊.....

[waCtr]

引用 13 楼 DBA_Huangzj 的回复:

SELECT * INTO #t3 FROM #t a WHERE EXISTS (SELECT 1 FROM #t2 b WHERE a.tid=b.tid AND b.timedate BETWEEN '2012-12-01' AND '2012-12-31') declare @s nvarchar(4000) set @s='' Select @s……

哦 那就是写在一个存储过程中见呗？

[發糞塗牆]

大概样子：

CREATE PROC test1 AS 
 
 SELECT * INTO #t3
 FROM #t a WHERE EXISTS (SELECT 1 FROM #t2 b WHERE a.tid=b.tid AND b.timedate BETWEEN '2012-12-01' AND '2012-12-31')
 
 
 declare @s nvarchar(4000)
 set @s=''
 Select     @s=@s+','+quotename(itmed)+'=count(case when [itmed]='+quotename(itmed,'''')+' then 1 else null end)'
 from #t3 GROUP BY itmed
 exec('select COUNT( itmed) [所有部门]'+@s+' from #t3 ')
 

注意#t、#t2这两个是你自己的实体表

[waCtr]

引用 15 楼 DBA_Huangzj 的回复:

大概样子：SQL code?123456789101112CREATE PROC test1 AS SELECT * INTO #t3 FROM #t a WHERE EXISTS (SELECT 1 FROM #t2 b WHERE a.tid=b.tid AND b.timedate BETWEEN '2012-12-01' AND '2012-12-31') ……

好了 谢谢！！！！

[發糞塗牆]

结贴睡觉