SELECT T1.TITLE+'['+CASE WHEN T2.ID IS NOT NULL THEN 'YES' ELSE 'NO' END)+']' FROM [表1] T1 LEFT JOIN [表2] T2 ON T1.ID=T2.MID
如果T2中可能有多条MID与T1的ID匹配,有两种写法,第一种适合大批量输出,第二种适合小批量输出。 SELECT T1.TITLE+'['+CASE WHEN T2.ID IS NOT NULL THEN 'YES' ELSE 'NO' END)+']' FROM [表1] T1 LEFT JOIN ( SELECT MID,MAX(ID) AS ID FROM [表2] GROUP BY MID ) T2 ON T1.ID=T2.MIDSELECT T1.TITLE+'['+CASE WHEN EXISTS( SELECT 1 FROM [表2] T2 WHERE T2.MID=T1.ID ) THEN 'YES' ELSE 'NO' END)+']' FROM [表1] T1
<% indexI = 0 sSql = "Select t1.ID as aa,t1.Name as bb,t1.Title as cc,t1.ClassID as dd,t1.KeepSecret as ee,t1.CreateDateTime as gg,t2.MID as ff From [MagistrateMailbox] t1,[MagistrateReply] t2 where Order By t1.ID DESC" oRs.Open sSql, oConn, 1, 1 If Not (oRs.EOF And oRs.BOF) Then oRS.PageSize = conPageSize TotalRecords = oRS.RecordCount TotalPages = oRS.PageCount MaxPerPage = oRS.PageSize CurrentPage = Trim(Request("PageNo"))%> <table border="0" cellpadding="0" cellspacing="0" width="100%" id="ListMail"> <tbody> <tr style="background-color:#D0D4C8;"> <th>序号</th> <th nowrap>发信人</th> <th colspan="1">主题</th> <th colspan="1">来信时间</th> <th colspan="1">状态</th> </tr> <% Do While Not oRs.EOF indexII = indexII + 1 indexI = indexI + 1 %>
<tr <%If 0 = (indexI Mod 2) Then:Response.Write("style=""background-color:#FCFDEB;""")%>> <td align="center" style="width:61;" nowrap><% Response.Write(indexII)'=aMailHelp(oRs("dd"),0)%></td> <td align="center" width="20%"><%If Not oRs("ee") Then:Response.Write(Server.HTMLEncode(oRs("bb"))):Else:Response.Write("保密"):End If%></td> <td width="100%"><a href="/pcxf/PCMail.asp?AID=<%=oRs("aa")%>" target="_blank"><%If Not oRs("ee") Then:Response.Write(Server.HTMLEncode(oRs("cc"))):Else:Response.Write("****************************"):End If%></a></td> <td align="center" nowrap style="line-height:100%;"><%=FormatDateTime(oRs("gg"), 2)%></td> <td align="center" nowrap style="line-height:100%;"><%if oRs("aa")=oRs("ff") Then:Response.Write("YES"):Else:Response.Write("NO"):End If%></td> </tr> <% If indexI >= MaxPerPage Then Exit Do oRs.MoveNext Loop 程序就是这样的,应该不是你写的那个代码样?
select a.title + case when b.id is not null then 'yes' else 'no' end from 表1 a left join 表2 b on a.id=b.mid
select title+(case when exists(select 1 from 表2 where mid=a.id) then 'yes' else 'no' end) from 表1 a
FROM [表1] T1
LEFT JOIN [表2] T2 ON T1.ID=T2.MID
SELECT T1.TITLE+'['+CASE WHEN T2.ID IS NOT NULL THEN 'YES' ELSE 'NO' END)+']'
FROM [表1] T1
LEFT JOIN ( SELECT MID,MAX(ID) AS ID
FROM [表2]
GROUP BY MID
) T2 ON T1.ID=T2.MIDSELECT T1.TITLE+'['+CASE WHEN EXISTS(
SELECT 1 FROM [表2] T2 WHERE T2.MID=T1.ID
) THEN 'YES' ELSE 'NO' END)+']'
FROM [表1] T1
sSql = "Select t1.ID as aa,t1.Name as bb,t1.Title as cc,t1.ClassID as dd,t1.KeepSecret as ee,t1.CreateDateTime as gg,t2.MID as ff From [MagistrateMailbox] t1,[MagistrateReply] t2 where Order By t1.ID DESC"
oRs.Open sSql, oConn, 1, 1
If Not (oRs.EOF And oRs.BOF) Then
oRS.PageSize = conPageSize
TotalRecords = oRS.RecordCount
TotalPages = oRS.PageCount
MaxPerPage = oRS.PageSize
CurrentPage = Trim(Request("PageNo"))%>
<table border="0" cellpadding="0" cellspacing="0" width="100%" id="ListMail">
<tbody>
<tr style="background-color:#D0D4C8;">
<th>序号</th>
<th nowrap>发信人</th>
<th colspan="1">主题</th>
<th colspan="1">来信时间</th>
<th colspan="1">状态</th>
</tr>
<%
Do While Not oRs.EOF
indexII = indexII + 1
indexI = indexI + 1
%>
<tr <%If 0 = (indexI Mod 2) Then:Response.Write("style=""background-color:#FCFDEB;""")%>>
<td align="center" style="width:61;" nowrap><% Response.Write(indexII)'=aMailHelp(oRs("dd"),0)%></td>
<td align="center" width="20%"><%If Not oRs("ee") Then:Response.Write(Server.HTMLEncode(oRs("bb"))):Else:Response.Write("保密"):End If%></td>
<td width="100%"><a href="/pcxf/PCMail.asp?AID=<%=oRs("aa")%>" target="_blank"><%If Not oRs("ee") Then:Response.Write(Server.HTMLEncode(oRs("cc"))):Else:Response.Write("****************************"):End If%></a></td>
<td align="center" nowrap style="line-height:100%;"><%=FormatDateTime(oRs("gg"), 2)%></td>
<td align="center" nowrap style="line-height:100%;"><%if oRs("aa")=oRs("ff") Then:Response.Write("YES"):Else:Response.Write("NO"):End If%></td>
</tr>
<%
If indexI >= MaxPerPage Then Exit Do
oRs.MoveNext
Loop
程序就是这样的,应该不是你写的那个代码样?
a.title + case when b.id is not null then 'yes' else 'no' end
from
表1 a left join 表2 b
on
a.id=b.mid
from 表1 a