;WITH CTE AS(
SELECT ROW_NUMBER()OVER(PARTITION BY 姓名 ORDER BY GETDATE())RN
,COUNT(1)OVER(PARTITION BY 姓名)c
,*
FROM TB
)
SELECT CASE WHEN RN>1 THEN '' ELSE 姓名 END 姓名
, CASE WHEN C>1 THEN CAST(RN AS VARCHAR)+'.'+检查内容 ELSE 检查内容 END 检查内容
FROM CTE
SELECT ROW_NUMBER()OVER(PARTITION BY 姓名 ORDER BY GETDATE())RN
,COUNT(1)OVER(PARTITION BY 姓名)c
,*
FROM TB
)
SELECT CASE WHEN RN>1 THEN '' ELSE 姓名 END 姓名
, CASE WHEN C>1 THEN CAST(RN AS VARCHAR)+'.'+检查内容 ELSE 检查内容 END 检查内容
FROM CTE
,CAST(STUFF((
SELECT CHAR(13)+CAST(ROW_NUMBER()OVER(PARTITION BY 姓名 ORDER BY GETDATE()) AS VARCHAR)
+'.'+检查内容
FROM TB T2
WHERE T1.姓名=T2.姓名
FOR XML PATH('')
),1,6,'')AS XML).value('.','varchar(MAX)')
FROM TB T1
GROUP BY 姓名
,STUFF((
SELECT ','+检查内容
FROM TB T2
WHERE T1.姓名=T2.姓名
FOR XML PATH('')
),1,6,'')
FROM TB T1
GROUP BY 姓名可以简化成这样,结构上差不多,但没那么复杂
</HEAD><BODY>
<div align="center">
<!--#include file="conn.asp" -->
<span class="style17"> </span>
<table width="587" border="0" align="center" cellpadding="0" cellspacing="1">
<tr bgcolor="#3399CC">
<!-- 用户名 -->
<td width="58" height="25"><div align="center" class="style29 style19" style="color: #FFFFFF">
<div align="center">检查人员</div>
</div></td>
<td width="285"><div align="center" class="style29 style19" style="color: #FFFFFF">
<div align="center">问题处理情况</div>
</div></td>
<!--备份 -->
</tr>
</table>
</div>
<% set rs=conn.execute("SELECT fxr,STUFF((SELECT ','+wtnr FROM wtk T2 WHERE T1.fxr=T2.fxr FOR XML PATH('')),1,6,'')wtnr FROM wtk T1 GROUP BY fxr")
rs.open sql,conn,2,2 'RS参数设定为执行SQL的查询语法,数据库文件里的employee表中的数据,无论任何条件,全部设计为查询的对象,并由RS作为管理的参数。if rs.EOF or rs.BOF then
Response.Write " 请输入所要查询的时间段进行查询"
else
do while not rs.EOF or rs.BOF
%> <table width="588" border="1" align="center" cellpadding="0" cellspacing="1">
<tr>
<!-- 用户名 -->
<td width="56" height="25"><div align="center" class="style19">
<div align="center"><%=rs("fxr")%></div>
</div></td>
<td width="282"><div align="center" class="style19">
<div align="center"><%=rs("wtnr")%></div>
</div></td>
</tr>
</table> <%
rs.movenext
loop
end if
set rs=nothing
set conn=nothing
%>
</BODY>
</HTML>
把\n在页面上换成<br>
</HEAD><BODY>
<div align="center">
<!--#include file="conn.asp" -->
<span class="style17"> </span>
<table width="587" border="0" align="center" cellpadding="0" cellspacing="1">
<tr bgcolor="#3399CC">
<!-- 用户名 -->
<td width="58" height="25"><div align="center" class="style29 style19" style="color: #FFFFFF">
<div align="center">检查人员</div>
</div></td>
<td width="285"><div align="center" class="style29 style19" style="color: #FFFFFF">
<div align="center">问题处理情况</div>
</div></td>
<!--备份 -->
</tr>
</table>
</div>
<% set rs=conn.execute("SELECT fxr,STUFF((SELECT ','+wtnr FROM wtk T2 WHERE T1.fxr=T2.fxr FOR XML PATH('')),1,6,'')wtnr FROM wtk T1 GROUP BY fxr")
rs.open sql,conn,2,2 if rs.EOF or rs.BOF then
Response.Write " 请输入所要查询的时间段进行查询"
else
do while not rs.EOF or rs.BOF
%> <table width="588" border="1" align="center" cellpadding="0" cellspacing="1">
<tr>
<!-- 用户名 -->
<td width="56" height="25"><div align="center" class="style19">
<div align="center"><%=rs("fxr")%></div>
</div></td>
<td width="282"><div align="center" class="style19">
<div align="center"><%=rs("wtnr")%></div>
</div></td>
</tr>
</table> <%
rs.movenext
loop
end if
set rs=nothing
set conn=nothing
%>
</BODY>
</HTML>
呃,这边改为1试试
这个不执行就是带123的那个
AS
内容那你试下,看存储过程可以不
,STUFF((
SELECT
CAST(ROW_NUMBER()OVER(PARTITION BY fxr ORDER BY GETDATE()) AS VARCHAR)
+'.'+wtnr
FROM wtk T2
WHERE T1.fxr=T2.fxr
FOR XML PATH(''))
,1,1,'')
FROM wtk T1 GROUP BY fxr这个有1 2 3没有换行不过,你既然是用在ASP页面上的,你可以利用ASP语言的功能,
还是用SELECT * FROM TB的方式查询
然后,for循环处理,将相同行合并
不管是行号,还是换行,都很方便处理,而且要速度可能要快些
,(SELECT
CAST(ROW_NUMBER()OVER(PARTITION BY fxr ORDER BY GETDATE()) AS VARCHAR)
+'.'+wtnr
FROM wtk T2
WHERE T1.fxr=T2.fxr
FOR XML PATH(''))wtnr
FROM wtk T1 GROUP BY fxr哈哈,不小心给删了
要不,随便给个标记符,查出来后,你再换成换行符
SELECT fxr
,(SELECT
CAST(ROW_NUMBER()OVER(PARTITION BY fxr ORDER BY GETDATE()) AS VARCHAR)
+'.'+wtnr
FROM wtk T2
WHERE T1.fxr=T2.fxr
FOR XML PATH(''))wtnr into #a
FROM wtk T1 GROUP BY fxrselect * from #a
后在写条sql把123给换行了呀,能按照学生的思路写一条换行语句么,
name nvarchar(10),
nr nvarchar(1000)
)insert into #t values
('王', '检查网站系统'),
('王', '检查网站内容'),
('王', '检查网站框架'),
('李', '内容内容内容')
with cte as
(
select *,ROW_NUMBER()over(PARTITION by name order by name) id from #t
)
select *,name nname from cte where id=1
union all
select '',nr,id,name from cte where id>1
order by nname,id
CREATE VIEW 视图名称
AS
SELECT fxr,CAST(STUFF((SELECT CHAR(13)+CAST(ROW_NUMBER()OVER(PARTITION BY fxr ORDER BY GETDATE()) AS VARCHAR)+'.'+wtnr FROM wtk T2 WHERE T1.fxr=T2.fxr FOR XML PATH('')),1,6,'')AS XML).value('.','varchar(MAX)') FROM wtk T1 GROUP BY fxr
GO
--执行的时候直接 SELECT * FROM 视图名称
rs.open sql,conn,2,2
这算什么逻辑?
先用 conn.execute 做了一次查询,然后什么都没做又做了一次查询。
既然只需要第二次查询结果,前一次查询又什么用?
最终输出结果由sql决定,sql变量里面是什么内容?