急！！！求一条统计sql语句。

表一
questionid  选项1  选项2  选项3  选项4
1  S  D  I  C
2  D  S  I  C
3  I  D  S  C
4  C  S  I  D
5  S  I  C  D
6  C  D  I  S
表二
nameid  name  questionid 选项
001  小章  1  4
001  小章  2  2
001  小章  3  2
001  小章  4  3
001  小章  5  2
001  小章  6  1
表三
tool  分数
D  5
I  6
S  7
C  8
要求得到的结果为
nameid  name  D总数  I总数  S总数  C总数  总分数
001  小章  1  2  1  2  40

19 个解决方案

#1

transform count(*)
select nameid,name,sum(分数) as 总分数 from (
SELECT *,getvalue(id) as cs
FROM (b2a LEFT JOIN b1 ON [b1].[questionid]=[b2a].[questionid])
left join b3 on getvalue(b2a.id)=b3.tool)
group by nameid,name
pivot cs & '总数'

模块：
Function getvalue(dd As Integer) As String
Set ff = CurrentDb.OpenRecordset("SELECT * from b2a left join b1 on b1.questionid=b2a.questionid where id=" & dd)
getvalue = ff("选项" & ff("选项"))
End Function

#2

上述代码在表二中增加自增字段ID，表结构不合理，只有用VBA+SQL了。

#3

create table 表一 
(
	questionid	integer,
	选项1	char,
	选项2	char,
	选项3	char,
	选项4	char
)

create table 表二 
(
	nameid	varchar(4),
	name 	varchar(20),
	questionid	integer,
	选项	integer
)

create table 表三
(
	tool	char,
	分数	integer
)


TRANSFORM Sum(q1.CNT) AS CNTOfSum
SELECT q1.nameid, q1.name
FROM (
	select a.nameid,a.name,a.questionid, Choose(选项,选项1,b.选项2,b.选项3,b.选项4) & '总数' as CATE,1 as CNT
	from 表二 a inner join 表一 b on a.questionid=b.questionid
	union all
	select a.nameid,a.name,a.questionid,'总分数' as CATE ,dlookup("分数","表三","tool='" & Choose(选项,选项1,b.选项2,b.选项3,b.选项4) & "'" )
	from 表二 a inner join 表一 b on a.questionid=b.questionid
) q1
GROUP BY q1.nameid, q1.name
PIVOT q1.CATE ;


nameid	name	C	D	I	S	总分数
001	小章	2	1	2	1	40

#4

我用的是ACCESS+ASP
表该如何改才合理？第一次自己设计表，不是很会。还望指教！谢谢~

引用 2 楼 WWWWA 的回复:

上述代码在表二中增加自增字段ID，表结构不合理，只有用VBA+SQL了。

#5

你是在ACCESS中，模块、DLOOKUP函数都不能用，只有打开记录集，循环，查找
表一 中对应的选项，
建议修改表结构：
questionid  选项 tool
1           1    s
1           2    d
1           3    i
1           4    c

#6

引用 4 楼 yuzhouer1122 的回复:

我用的是ACCESS+ASP 
表该如何改才合理？第一次自己设计表，不是很会。还望指教！谢谢~ 
引用 2 楼 WWWWA 的回复:
上述代码在表二中增加自增字段ID，表结构不合理，只有用VBA+SQL了。 

ASP则dlookup不可用，SQL会更复杂.

#7

但是disc是不规则的啊。比如：
一、1 富于冒险:愿意面对新事物并敢于下决心掌握的人；D 
2 适应力强:轻松自如适应任何环境 ；S 
3 生动:充满活力,表情生动,多手势；I 
4 善于分析:喜欢研究各部分之间的逻辑和正确的关系。C 

二、1坚持不懈：要完成现有的事才能做新的事情；C 
2喜好娱乐：开心充满乐趣与幽默感；I 
3善于说服：用逻辑和事实而不用威严和权利服人；D 
4平和：在冲突中不受干扰，保持平静。S 

三、1顺服：易接受他人的观点和喜好，不坚持己见；S 
2自我牺牲：为他人利益愿意放弃个人意见；C 
3善于社交：认为与人相处是好玩，而不是挑战或者商业机会；I 
4意志坚定：决心以自己的方式做事。D 

只有根据前面的1234来确定此选项的的类型。

引用 5 楼 WWWWA 的回复:

你是在ACCESS中，模块、DLOOKUP函数都不能用，只有打开记录集，循环，查找 
表一 中对应的选项， 
建议修改表结构： 
questionid  选项 tool 
1          1    s 
1          2    d 
1          3    i 
1          4    c

#8

而且DISC是不可见的。

#9

我的意思是将
表一
questionid  选项1  选项2  选项3  选项4
1  S  D  I  C
2  D  S  I  C
3  I  D  S  C
4  C  S  I  D
5  S  I  C  D
6  C  D  I  S 

中的记录内容从行->列，与工作表连接就好处理了

questionid  选项 tool
1          1    s
1          2    d
1          3    i
1          4    c 
2          1    d
2          2    s
2          3    i 
2          4    c

#10

很奇怪，VB中通过JET-SQL竟然能用这些函数，你在你的ASP中试一下。

Public Sub test()
    Dim sConn As String
    Dim sSQL As String
    
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\access\db2.mdb;User Id=admin;Password=;"
    conn.Open sConn
    
    sSQL = " TRANSFORM Sum(q1.CNT) AS CNTOfSum" _
        & " SELECT q1.nameid, q1.name" _
        & " FROM (" _
        & "     select a.nameid,a.name,a.questionid, Choose(Ñ¡Ïî,Ñ¡Ïî1,b.Ñ¡Ïî2,b.Ñ¡Ïî3,b.Ñ¡Ïî4) & '×ÜÊý' as CATE,1 as CNT" _
        & "     from ±í¶þ a inner join ±íÒ» b on a.questionid=b.questionid" _
        & "     union all" _
        & "     select a.nameid,a.name,a.questionid,'×Ü·ÖÊý' as CATE ,dlookup('·ÖÊý','±íÈý','tool=""' & Choose(Ñ¡Ïî,Ñ¡Ïî1,b.Ñ¡Ïî2,b.Ñ¡Ïî3,b.Ñ¡Ïî4) & '"" )" _
        & "     from ±í¶þ a inner join ±íÒ» b on a.questionid=b.questionid" _
        & " ) q1" _
        & " GROUP BY q1.nameid, q1.name" _
        & " PIVOT q1.CATE"
    
    rs.Open "query1", conn
    Do While Not rs.EOF
        Debug.Print rs.Fields(0).Value, rs.Fields(1).Value
        rs.MoveNext
    Loop
    rs.Close
    conn.Close

#11

questionid  选项 tool
1          1    s
1          2    d
1          3    i
1          4    c
2          1    d
2          2    s
2          3    i
2          4    c 
3          1    i
3          2    d
3          3    s
3          4    c 
4          1    c
4          2    s
4          3    i
4          4    d 
5          1    s
5          2    i
5          3    c
5          4    d 
6          1    c
6          2    d
6          3    i
6          4    s 

#12

在你的MDB文件中建个查询，

然后应该可以了。

TRANSFORM Sum(q1.CNT) AS CNTOfSum
SELECT q1.nameid, q1.name
FROM (
select a.nameid,a.name,a.questionid, Choose(选项,选项1,b.选项2,b.选项3,b.选项4) & '总数' as CATE,1 as CNT
from 表二 a inner join 表一 b on a.questionid=b.questionid
union all
select a.nameid,a.name,a.questionid,'总分数' as CATE ,dlookup("分数","表三","tool='" & Choose(选项,选项1,b.选项2,b.选项3,b.选项4) & "'" )
from 表二 a inner join 表一 b on a.questionid=b.questionid
) q1
GROUP BY q1.nameid, q1.name
PIVOT q1.CATE ;

#13

根据上述思路：
transform count(*)
select nameid,name,sum(分数) from (
SELECT * from (b2a left join b2b on b2a.questionid=b2b.questionid and b2a.选项=b2b.选项)
left join b3 on b2b.tool=b3.tool) group by nameid,name
pivot b3.tool

b2a:表二 
b2b:修改后的表一
B3：表三 

#14

改成这样了
SQL该怎么写呢？

引用 11 楼 WWWWA 的回复:

questionid  选项 tool 
1          1    s 
1          2    d 
1          3    i 
1          4    c 
2          1    d 
2          2    s 
2          3    i 
2          4    c 
3          1    i 
3          2    d 
3          3    s 
3          4    c 
4          1    c 
4          2    s 
4          3    i 
4          4    d 
5          1    s 
5          2    i 
5          3  …

#15

SQL该怎么写呢？ 
已经出来了

引用 13 楼 WWWWA 的回复:

根据上述思路：
transform count(*)
select nameid,name,sum(分数) from (
SELECT * from (b2a left join b2b on b2a.questionid=b2b.questionid and b2a.选项=b2b.选项)
left join b3 on b2b.tool=b3.tool) group by nameid,name
pivot b3.tool

b2a:表二
b2b:修改后的表一
B3：表三

#16

谢谢。能注释下q1,a,b,分别是什么吗？

引用 12 楼 ACMAIN_CHM 的回复:

在你的MDB文件中建个查询， 

然后应该可以了。 

TRANSFORM Sum(q1.CNT) AS CNTOfSum 
SELECT q1.nameid, q1.name 
FROM ( 
select a.nameid,a.name,a.questionid, Choose(选项,选项1,b.选项2,b.选项3,b.选项4) & '总数' as CATE,1 as CNT 
from 表二 a inner join 表一 b on a.questionid=b.questionid 
union all 
select a.nameid,a.name,a.questionid,'总分数' as CATE ,dlookup("分数","表三","tool='" & Choose…

#17

以下代码测试OK，在ASP，IIS6,ACCESS2003 中,不知道为什么DLOOKUP竟然可以用，一直以为不行

<%
	dim strsql,conn,rs
	dim connstring
	connstring = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db2.mdb")

	set conn = server.createObject("ADODB.Connection")
	conn.open connstring

	Set rs = Server.CreateObject("ADODB.Recordset")

    strsql = " TRANSFORM Sum(q1.CNT) AS CNTOfSum" _
        & " SELECT q1.nameid, q1.name" _
        & " FROM (" _
        & "     select a.nameid,a.name,a.questionid, Choose(选项,选项1,b.选项2,b.选项3,b.选项4) & '总数' as CATE,1 as CNT" _
        & "     from 表二 a inner join 表一 b on a.questionid=b.questionid" _
        & "     union all" _
        & "     select a.nameid,a.name,a.questionid,'总分数' as CATE ,dlookup('分数','表三','tool=""' & Choose(选项,选项1,b.选项2,b.选项3,b.选项4) & '""' )" _
        & "     from 表二 a inner join 表一 b on a.questionid=b.questionid" _
        & " ) q1" _
        & " GROUP BY q1.nameid, q1.name" _
        & " PIVOT q1.CATE"

	rs.Open strsql, conn

	do while not rs.eof
		response.write rs.fields(0).value
		response.write "<br/>"
		response.write rs.fields(1).value
		response.write "<br/>"
		response.write rs.fields(2).value
		rs.MoveNext
	loop
	rs.close
	conn.close
	set rs = nothing
	set conn = nothing

%>

#18

引用 16 楼 yuzhouer1122 的回复:

谢谢。能注释下q1,a,b,分别是什么吗？ 
引用 12 楼 ACMAIN_CHM 的回复:
在你的MDB文件中建个查询， 

然后应该可以了。 

TRANSFORM Sum(q1.CNT) AS CNTOfSum 
SELECT q1.nameid, q1.name 
FROM ( 
select a.nameid,a.name,a.questionid, Choose(选项,选项1,b.选项2,b.选项3,b.选项4) & '总数' as CATE,1 as CNT 
from 表二 a inner join 表一 b on a.questionid=b.questionid 
union all 
select a.nameid,a.name,a…

q1,a,b 为表的别名，没有特别意义，只是为了方便书写。SQL语句中临时给表或字段起的名字。

#19

文件db2.mdb和test2.asp 已经上传到 http://www.accessbbs.cn/bbs/viewthread.php?tid=18076&extra=page%3D1&frombbs=1

#20

• 怎样一条SQL语句统计该年每月的数据个数
• SQL 一条SQL语句 统计 各班总人数、男女各总人数 、该班级男女 比例