查询结果显示问题

[fengwei19823]

我在查询每个员工 初步联系,预约看场, 已看场有意向, 已看场不考虑, 签约客户 的数量
<%
sql = "select name, count(cus_state) as counts,cus_state from cus group by name, cus_state order by name"
set rs = server.createobject("adodb.recordset")
rs.open sql,conn,1,1
while not rs.eof
Response.write rs("name") & " " & rs("cus_state") & " " & rs("counts") & "次<br>"
rs.movenext
wend
rs.close
%>
现在显示结果是列显示:
王五 初步联系 1
王五 签约客户 3
小刘 已看场不考虑 3
小刘 已看场有意向 1
张三 初步联系 2
张三 签约客户 1
张三 预约看场 2

如果我想以行来显示该怎么修改 可以实现吗? 像如下显示
/*name 初步联系 签约客户 已看场不考虑 已看场有意向 预约看场
---- ----------- ----------- ----------- ----------- -----------
王五 1 3 0 0 0
小刘 0 0 3 1 0
张三 2 1 0 0 2

表如下:
name(姓名) cus_name(客户名) cus_state(客户状态)
张三 肯德基 初步联系
张三 按时的 预约看场
张三 翻跟斗 预约看场
张三 粗色的 初步联系
张三 粗豆腐干的 签约客户
小刘 挥洒的 已看场有意向
小刘 士大夫 已看场不考虑
小刘 gf的 已看场不考虑
小刘 搞活风格 已看场不考虑
王五 反对感发 签约客户
王五 环境燃放法 签约客户
王五 我日的感发 签约客户
王五 地方搞活 初步联系

[liuwei_IT_love]

<%
str="select * from cus order by id desc"
set rs=db.execute(str)
if not rs.bof and not rs.eof then
%>
<table>
<tr>
<td>name</td>
<td>初步联系 </td>
<td>签约客户</td>
<td>已看场不考虑 </td>
<td>已看场有意向</td>
<td>预约看场 </td>
</tr>
<%
do while not rs.eof
%>
<tr>
<td><%=rs("列名")%></td>
</tr>
<%
rs.movenext
loop
%>
</table>
<%
end if
%>

[fengwei19823]

不对``

[三楼の郎]

MS SQL

select a.[name],b.c1 as [初步联系],c.c1 as [预约看场],d.c1 as [签约客户] and e.c1 as [已看场有意向],f.c1 as [已看场不考虑]
from (select distinct [name] from cus) as a
left join (select [name],count(name) as c1 from cus where cus_state='初步联系' group by [name]) as b on a.[name]=b.[name]
left join (select [name],count(name) as c1 from cus where cus_state='预约看场' group by [name]) as c on a.[name]=c.[name]
left join (select [name],count(name) as c1 from cus where cus_state='签约客户' group by [name]) as d on a.[name]=d.[name]
left join (select [name],count(name) as c1 from cus where cus_state='已看场有意向' group by [name]) as e on a.[name]=e.[name]
left join (select [name],count(name) as c1 from cus where cus_state='已看场不考虑' group by [name]) as f on a.[name]=f.[name]

Access

select a.[name],b.c1 as [初步联系],c.c1 as [预约看场],d.c1 as [签约客户] and e.c1 as [已看场有意向],f.c1 as [已看场不考虑]
from ((((((select distinct [name] from cus) as a
left join (select [name],count(name) as c1 from cus where cus_state='初步联系' group by [name]) as b on a.[name]=b.[name])
left join (select [name],count(name) as c1 from cus where cus_state='预约看场' group by [name]) as c on a.[name]=c.[name])
left join (select [name],count(name) as c1 from cus where cus_state='签约客户' group by [name]) as d on a.[name]=d.[name])
left join (select [name],count(name) as c1 from cus where cus_state='已看场有意向' group by [name]) as e on a.[name]=e.[name])
left join (select [name],count(name) as c1 from cus where cus_state='已看场不考虑' group by [name]) as f on a.[name]=f.[name])

[fengwei19823]

楼上的可以写全吗??详细的该改哪里````完整的` 需要在asp页面上显示出来
<%
sql = "select name, count(cus_state) as counts,cus_state from cus group by name, cus_state order by name"
set rs = server.createobject("adodb.recordset")
rs.open sql,conn,1,1
while not rs.eof
Response.write rs("name") & " " & rs("cus_state") & " " & rs("counts") & "次 <br>"
rs.movenext
wend
rs.close
%>

[三楼の郎]

<%  
sql = "用我上面的对应的SQL语句替代" 
set rs  = server.createobject("adodb.recordset")  
rs.open sql,conn,1,1 
while not rs.eof 
    Response.write rs("name") & " " & rs("初步联系") & " " & rs("预约看场") & " " & rs("签约客户") & " " & rs("已看场有意向") & " " & rs("已看场不考虑") & "次 <br>" 
    rs.movenext 
wend 
rs.close 
%> 

[fengwei19823]

试了 把你写的SQL语句我是连着一行写完了 但提示

SELECT 子句中包含一个保留字、拼写错误或丢失的参数，或标点符号不正确。

如果直接按照你的格式,提示的是:

未结束的字符串常量

是怎么回事呢

[三楼の郎]

count(name)
改成：
count([name])

[三楼の郎]

用这个SQL语句吧，上面写的有个小错

MSSQL

select a.[name],b.c1 as [初步联系],c.c1 as [预约看场],d.c1 as [签约客户], e.c1 as [已看场有意向],f.c1 as [已看场不考虑]
from (select distinct [name] from cus) as a
left join (select [name],count([name]) as c1 from cus where cus_state='初步联系' group by [name]) as b on a.[name]=b.[name]
left join (select [name],count([name]) as c1 from cus where cus_state='预约看场' group by [name]) as c on a.[name]=c.[name]
left join (select [name],count([name]) as c1 from cus where cus_state='签约客户' group by [name]) as d on a.[name]=d.[name]
left join (select [name],count([name]) as c1 from cus where cus_state='已看场有意向' group by [name]) as e on a.[name]=e.[name]
left join (select [name],count([name]) as c1 from cus where cus_state='已看场不考虑' group by [name]) as f on a.[name]=f.[name]

Access

select a.[name],b.c1 as [初步联系],c.c1 as [预约看场],d.c1 as [签约客户],e.c1 as [已看场有意向],f.c1 as [已看场不考虑]
from ((((((select distinct [name] from cus) as a
left join (select [name],count([name]) as c1 from cus where cus_state='初步联系' group by [name]) as b on a.[name]=b.[name])
left join (select [name],count([name]) as c1 from cus where cus_state='预约看场' group by [name]) as c on a.[name]=c.[name])
left join (select [name],count([name]) as c1 from cus where cus_state='签约客户' group by [name]) as d on a.[name]=d.[name])
left join (select [name],count([name]) as c1 from cus where cus_state='已看场有意向' group by [name]) as e on a.[name]=e.[name])
left join (select [name],count([name]) as c1 from cus where cus_state='已看场不考虑' group by [name]) as f on a.[name]=f.[name])

[fengwei19823]

没人知道吗``??