我有一个A表如下:
姓名 选项1 选项2 选项3 选项4
张三 ABCD A C D
李四 AB AB BC AB
....................................
王五 ABC B A ACD最后我要统计选项1,选项2,选项3,选项4中的A、B、C、D选项各有多少,其查询的表如下:
选项 选项1 选项2 选项3 选项4
A 3 2 1 2
B 3 2 1 1
C 2 0 2 1
D 1 0 0 2请用一条sql的查询语句完成.
哈哈,也可以在以下论坛回答该问题,我个人主页上的论坛。
http://www.hepost.com/cgi-bin/bbs3000/bbs.cgi?id=200203231232
姓名 选项1 选项2 选项3 选项4
张三 ABCD A C D
李四 AB AB BC AB
....................................
王五 ABC B A ACD最后我要统计选项1,选项2,选项3,选项4中的A、B、C、D选项各有多少,其查询的表如下:
选项 选项1 选项2 选项3 选项4
A 3 2 1 2
B 3 2 1 1
C 2 0 2 1
D 1 0 0 2请用一条sql的查询语句完成.
哈哈,也可以在以下论坛回答该问题,我个人主页上的论坛。
http://www.hepost.com/cgi-bin/bbs3000/bbs.cgi?id=200203231232
解决方案 »
- UTF-8转换成GB2312的问题
- 关于一个string里的字符换行问题
- 一个关于DLL的问题!
- 【紧急向高手求助!!】 如何实现一个如文所述的简单DCOM??感激不尽!!!!!100分酬谢!
- 有关第三方浏览器
- 我正做火车售票系统,遇到点困难,帮帮忙!!!!!
- 这里好冷~~好冷~~
- 郁闷中...,放分喽!以后不会很多时间来了,分留着没用,需要可用分的就说个数!斑竹留情,我想也许技术区才有人需要可用分!(顺便提升
- 努力学习,天天向上。
- Active Movie 控件 导致 Form无法保存,为什么?
- 关于Delphi5与Sql Server2000兼容问题?
- ★★★还是关于那个特殊的for循环!你们说的不对!!这是答案,看看吧!!
adoquery1.sql.add('select sum(case when upper(选项1) like ''%A%'' end) as 选项A,sum(case when upper(选项1) like ''%B%'' end) as 选项B,sum(case when upper(选项1) like ''%C%'' end) as 选项 C...... from A表')
(Select isnull(count(*),0) from a where 选项1 like "%A%") as 选项1,
(Select isnull(count(*),0) from a where 选项2 like "%B%") as 选项2,
(Select isnull(count(*),0) from a where 选项3 like "%C%") as 选项3,
(Select isnull(count(*),0) from a where 选项4 like "%D%") as 选项4
From a AA
选项1=(select count(*) from A where 选项1 like '%A%' ),
选项2=(select count(*) from A where 选项2 like '%A%' ),
选项3=(select count(*) from A where 选项3 like '%A%' ),
选项4=(select count(*) from A where 选项4 like '%A%' )
union
select 选项='B' ,
选项1=(select count(*) from A where 选项1 like '%B%' ),
选项2=(select count(*) from A where 选项2 like '%B%' ),
选项3=(select count(*) from A where 选项3 like '%B%' ),
选项4=(select count(*) from A where 选项4 like '%B%' )
union
.
.
.
(select count(*) from ttt where charindex('A',a2)>0),
(select count(*) from ttt where charindex('A',a3)>0),
(select count(*) from ttt where charindex('A',a4)>0),
(select count(*) from ttt where charindex('A',a5)>0)
from ttt
union
select 'B',
(select count(*) from ttt where charindex('B',a2)>0),
(select count(*) from ttt where charindex('B',a3)>0),
(select count(*) from ttt where charindex('B',a4)>0),
(select count(*) from ttt where charindex('B',a5)>0)
from ttt
union
select 'C',
(select count(*) from ttt where charindex('C',a2)>0),
(select count(*) from ttt where charindex('C',a3)>0),
(select count(*) from ttt where charindex('C',a4)>0),
(select count(*) from ttt where charindex('C',a5)>0)
from ttt
union
select 'D',
(select count(*) from ttt where charindex('D',a2)>0),
(select count(*) from ttt where charindex('D',a3)>0),
(select count(*) from ttt where charindex('D',a4)>0),
(select count(*) from ttt where charindex('D',a5)>0)
from ttt
(select count(*) from ttt where charindex('A',a2)>0),
(select count(*) from ttt where charindex('A',a3)>0),
(select count(*) from ttt where charindex('A',a4)>0),
(select count(*) from ttt where charindex('A',a5)>0)
from ttt
union
select 'B',
(select count(*) from ttt where charindex('B',a2)>0),
(select count(*) from ttt where charindex('B',a3)>0),
(select count(*) from ttt where charindex('B',a4)>0),
(select count(*) from ttt where charindex('B',a5)>0)
from ttt
union
select 'C',
(select count(*) from ttt where charindex('C',a2)>0),
(select count(*) from ttt where charindex('C',a3)>0),
(select count(*) from ttt where charindex('C',a4)>0),
(select count(*) from ttt where charindex('C',a5)>0)
from ttt
union
select 'D',
(select count(*) from ttt where charindex('D',a2)>0),
(select count(*) from ttt where charindex('D',a3)>0),
(select count(*) from ttt where charindex('D',a4)>0),
(select count(*) from ttt where charindex('D',a5)>0)
from ttt