我有一个查询,它将显示除最大记录之外的所有选定记录(每个查询最多可以有5个记录)。但是,它会将其显示在单独的行中。如何在一行中显示结果?
当前数据:
John Smith
Jack Smith
Gerald Smith所需数据在一行中(或在单独的字段中,但不在多行中):
约翰·史密斯;杰克·史密斯;杰拉尔德·史密斯
SQL语句:
SELECT distinct /*all existing records */
rtrim(ltrim(pn.sFirstName + ' ' + pn.uLastName)) as record1,
'' as record2,
'' as record3,
'' as record4,
'' as record5
FROM tenant t (nolock)
INNER JOIN person pn (nolock) on pn.hmy = t.person
WHERE pn.hmy <>
(SELECT MAX(pn.hmy)
FROM tenant t (nolock)
INNER JOIN person pn (nolock) on pn.hmy = t.person
WHERE t.scode = '111111'
)and t.scode = '111111'谢谢你的帮助!
发布于 2013-03-22 21:59:53
看起来您正在使用SQL Server。如果您使用的是版本2005或更高版本,则可以使用row_number()执行此操作
SELECT (case when seqnum = 2 then rtrim(ltrim(max(pn.sFirstName+ ' ' + pn.uLastName))) as record1,
(case when seqnum = 3 then rtrim(ltrim(max(pn.sFirstName+ ' ' + pn.uLastName))) as record2,
(case when seqnum = 4 then rtrim(ltrim(max(pn.sFirstName+ ' ' + pn.uLastName))) as record3,
(case when seqnum = 5 then rtrim(ltrim(max(pn.sFirstName+ ' ' + pn.uLastName))) as record4,
(case when seqnum = 6 then rtrim(ltrim(max(pn.sFirstName+ ' ' + pn.uLastName))) as record5
FROM (select pn.*,
ROW_NUMBER() over (partition by t.scode order by pn.hmy desc) as seqnum
from tenant t (nolock) INNER JOIN
person pn (nolock)
on pn.hmy = t.person
where t.scode = '111111'
) pn内部查询根据pn.hmy分配一个序列号,最大值为1。外部查询汇总数据,使用条件选择第二到第六个值。
发布于 2013-03-22 21:47:22
您需要一个变量来保存这些信息,例如
declare @result;
set @result = '';
select @result = @result + ',' + rtrim(ltrim(pn.sFirstName + ' ' + pn.uLastName))
from .....
select substring(@result, 0, len(@result));https://stackoverflow.com/questions/15571819
复制相似问题