现有一个表T,表中有字段 Name,D/N,F/T,Y/N;没有主键,字段D/N只有D和N两种数据,字段F/T与字段Y/N雷同于字段D/N;
现有3个计数统计查询
SELECT Name,[D/N],Count([D/N]) as 计数1
FROM T
GROUP BY Name,[D/N];SELECT Name,[D/N],[F/T],Count([F/T]) as 计数2
FROM T
WHERE ([F/T]='T')
GROUP BY Name,[D/N],[F/T];SELECT Name,[D/N],[Y/N],Count([Y/N]) as 计数3
FROM T
WHERE ([Y/N]='Y')
GROUP BY Name,[D/N],[Y/N];现在想把这3个查询并成单个查询,输出结果:
标题行: Name D/N 计数1 计数2 计数3
1 D 0 0 0
1 N 0 0 0
2 D 0 0 0
2 N 0 0 0
. . . . .
. . . . .
. . . . .计数项如果为0,最好显示出来,不显示也要紧
现有3个计数统计查询
SELECT Name,[D/N],Count([D/N]) as 计数1
FROM T
GROUP BY Name,[D/N];SELECT Name,[D/N],[F/T],Count([F/T]) as 计数2
FROM T
WHERE ([F/T]='T')
GROUP BY Name,[D/N],[F/T];SELECT Name,[D/N],[Y/N],Count([Y/N]) as 计数3
FROM T
WHERE ([Y/N]='Y')
GROUP BY Name,[D/N],[Y/N];现在想把这3个查询并成单个查询,输出结果:
标题行: Name D/N 计数1 计数2 计数3
1 D 0 0 0
1 N 0 0 0
2 D 0 0 0
2 N 0 0 0
. . . . .
. . . . .
. . . . .计数项如果为0,最好显示出来,不显示也要紧
解决方案 »
- sql2005附加问题!
- 一个很疑惑的查询语句,帮我分析一下,谢谢!!
- 几个表折腾数据的效率问题
- 请教大师,一个存储过程的写法
- sql查询10笔存款天数
- sql查询问题
- 没分了!是因为我太菜!但请各路英雄帮帮忙,不然过明天我又要加入失业军团了,Boss给三天时间为期限,今天已是第二天了!帮帮忙吧,看在
- 两个数据库数据的导入问题,有一定的挑战难度。
- order排序!!!
- 挑战绝对高手:AIX(Unix)下Oracle8i的操作,急!急!!急!!!
- server name写错前后两次执行返回不同错误信息
- sql server 的image字段如果保存一张同样的bmp和jpeg(由bmp转换而来)图片占用的空间是否差不多大呢?
isnull((select count(1) from t where [D/N] = m.[D/N]),0) 计数1,
isnull((select count(1) from t where [D/N] = m.[D/N] and [F/T] = m.[F/T]),0) 计数2,
isnull((select count(1) from t where [D/N] = m.[D/N] and [Y/N] = m.[Y/N]),0) 计数3
from t
group by m.name,m.[D/N]
isnull((select count(1) from t where [D/N] = m.[D/N]),0) 计数1,
isnull((select count(1) from t where [D/N] = m.[D/N] and [F/T] = m.[F/T]),0) 计数2,
isnull((select count(1) from t where [D/N] = m.[D/N] and [Y/N] = m.[Y/N]),0) 计数3
from t
group by m.name,m.[D/N]--orselect isnull(isnull(t1.name , t2.name),t3.name) name ,
isnull(isnull(t1.[D/N],t2.[D/N]),t3.[D/N]) [D/N],
isnull(t1.计数1,0) 计数1,
isnull(t2.计数2,0) 计数2,
isnull(t3.计数3,0) 计数3
from
(
SELECT Name,[D/N],Count([D/N]) as 计数1
FROM T
GROUP BY Name,[D/N];
) t1
full join
(
SELECT Name,[D/N],[F/T],Count([F/T]) as 计数2
FROM T
WHERE ([F/T]='T')
GROUP BY Name,[D/N],[F/T];
) t2 on t1.name = t2.name and t1.[D/N] = t2.[D/N]
full join
(
SELECT Name,[D/N],[Y/N],Count([Y/N]) as 计数3
FROM T
WHERE ([Y/N]='Y')
GROUP BY Name,[D/N],[Y/N];
) t3
on isnull(t1.name , t2.name) = t3.name and isnull(t1.[D/N],t2.[D/N]) = t3.[D/N]
这里的m,指那个表?要建一个表m吗
isnull((select count(1) from t where [D/N] = m.[D/N]),0) 计数1,
isnull((select count(1) from t where [D/N] = m.[D/N] and [F/T] = m.[F/T]),0) 计数2,
isnull((select count(1) from t where [D/N] = m.[D/N] and [Y/N] = m.[Y/N]),0) 计数3
from t m
group by m.name,m.[D/N]