7,714
社区成员
发帖
与我相关
我的任务
分享急！！！求一条统计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
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
...全文
请发表友善的回复…
发表回复
ACMAIN_CHM 2009-01-19
- 打赏
- 举报
文件db2.mdb和test2.asp 已经上传到 http://www.accessbbs.cn/bbs/viewthread.php?tid=18076&extra=page%3D1&frombbs=1
ACMAIN_CHM 2009-01-19
- 打赏
- 举报
[Quote=引用 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…
[/Quote]
q1,a,b 为表的别名，没有特别意义，只是为了方便书写。SQL语句中临时给表或字段起的名字。
谢谢。能注释下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…
[/Quote]
q1,a,b 为表的别名，没有特别意义，只是为了方便书写。SQL语句中临时给表或字段起的名字。
ACMAIN_CHM 2009-01-19
- 打赏
- 举报
以下代码测试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
%>
yuzhouer1122 2009-01-19
- 打赏
- 举报
谢谢。能注释下q1,a,b,分别是什么吗？
[Quote=引用 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…
[/Quote]
[Quote=引用 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…
[/Quote]
WWWWA 2009-01-19
- 打赏
- 举报
SQL该怎么写呢？
已经出来了
[Quote=引用 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：表三
[/Quote]
已经出来了
[Quote=引用 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：表三
[/Quote]
yuzhouer1122 2009-01-19
- 打赏
- 举报
改成这样了
SQL该怎么写呢？
[Quote=引用 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 …
[/Quote]
SQL该怎么写呢？
[Quote=引用 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 …
[/Quote]
WWWWA 2009-01-19
- 打赏
- 举报
根据上述思路：
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：表三
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：表三
ACMAIN_CHM 2009-01-19
- 打赏
- 举报
在你的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 ;
然后应该可以了。
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 ;
WWWWA 2009-01-19
- 打赏
- 举报
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
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
ACMAIN_CHM 2009-01-19
- 打赏
- 举报
很奇怪，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.CloseWWWWA 2009-01-19
- 打赏
- 举报
我的意思是将
表一
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
表一
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
yuzhouer1122 2009-01-19
- 打赏
- 举报
而且DISC是不可见的。
yuzhouer1122 2009-01-19
- 打赏
- 举报
但是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来确定此选项的的类型。
[Quote=引用 5 楼 WWWWA 的回复:]
你是在ACCESS中，模块、DLOOKUP函数都不能用，只有打开记录集，循环，查找
表一 中对应的选项，
建议修改表结构：
questionid 选项 tool
1 1 s
1 2 d
1 3 i
1 4 c
[/Quote]
一、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来确定此选项的的类型。
[Quote=引用 5 楼 WWWWA 的回复:]
你是在ACCESS中，模块、DLOOKUP函数都不能用，只有打开记录集，循环，查找
表一 中对应的选项，
建议修改表结构：
questionid 选项 tool
1 1 s
1 2 d
1 3 i
1 4 c
[/Quote]
ACMAIN_CHM 2009-01-19
- 打赏
- 举报
[Quote=引用 4 楼 yuzhouer1122 的回复:]
我用的是ACCESS+ASP
表该如何改才合理？第一次自己设计表，不是很会。还望指教！谢谢~
引用 2 楼 WWWWA 的回复:
上述代码在表二中增加自增字段ID，表结构不合理，只有用VBA+SQL了。
[/Quote]
ASP则dlookup不可用，SQL会更复杂.
我用的是ACCESS+ASP
表该如何改才合理？第一次自己设计表，不是很会。还望指教！谢谢~
引用 2 楼 WWWWA 的回复:
上述代码在表二中增加自增字段ID，表结构不合理，只有用VBA+SQL了。
[/Quote]
ASP则dlookup不可用，SQL会更复杂.
WWWWA 2009-01-19
- 打赏
- 举报
你是在ACCESS中，模块、DLOOKUP函数都不能用，只有打开记录集，循环，查找
表一 中对应的选项，
建议修改表结构：
questionid 选项 tool
1 1 s
1 2 d
1 3 i
1 4 c
表一 中对应的选项，
建议修改表结构：
questionid 选项 tool
1 1 s
1 2 d
1 3 i
1 4 c
yuzhouer1122 2009-01-19
- 打赏
- 举报
我用的是ACCESS+ASP
表该如何改才合理？第一次自己设计表，不是很会。还望指教！谢谢~
[Quote=引用 2 楼 WWWWA 的回复:]
上述代码在表二中增加自增字段ID，表结构不合理，只有用VBA+SQL了。
[/Quote]
表该如何改才合理？第一次自己设计表，不是很会。还望指教！谢谢~
[Quote=引用 2 楼 WWWWA 的回复:]
上述代码在表二中增加自增字段ID，表结构不合理，只有用VBA+SQL了。
[/Quote]
ACMAIN_CHM 2009-01-19
- 打赏
- 举报
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 40WWWWA 2009-01-19
- 打赏
- 举报
上述代码在表二中增加自增字段ID，表结构不合理，只有用VBA+SQL了。
WWWWA 2009-01-19
- 打赏
- 举报
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
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
