22,209
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('sc') IS NOT NULL DROP TABLE sc
GO
CREATE TABLE sc(
s# VARCHAR(10),
c# VARCHAR(10),
score INT
)
GO
SET NOCOUNT ON
INSERT INTO sc VALUES ('0001','02',112);
INSERT INTO sc VALUES ('0001','03',23);
INSERT INTO sc VALUES ('0002','01',25);
INSERT INTO sc VALUES ('0001','01',78);
INSERT INTO sc VALUES ('0003','02',87);
INSERT INTO sc VALUES ('0003','03',45);
INSERT INTO sc VALUES ('0004','01',41);
INSERT INTO sc VALUES ('0004','03',78);
INSERT INTO sc VALUES ('0005','01',46);
INSERT INTO sc VALUES ('0005','02',55);
INSERT INTO sc VALUES ('0005','03',69);
GO
---------- 以上为测试数据 ---------------
--1. Top 100 嵌套
SELECT * FROM (
SELECT TOP 100 PERCENT * FROM sc ORDER BY c#
) AS t
--2. Top 100 不嵌套
SELECT TOP 100 PERCENT * FROM sc ORDER BY c#
--3. Top 99 嵌套
SELECT * FROM (
SELECT TOP 99.99 PERCENT * FROM sc ORDER BY c#
) AS t
USE tempdb
GO
IF OBJECT_ID('sc') IS NOT NULL DROP TABLE sc
GO
CREATE TABLE sc(
s# VARCHAR(10),
c# VARCHAR(10),
score INT
)
GO
SET NOCOUNT ON
INSERT INTO sc VALUES ('0001','02',112);
INSERT INTO sc VALUES ('0001','03',23);
INSERT INTO sc VALUES ('0002','01',25);
INSERT INTO sc VALUES ('0001','01',78);
INSERT INTO sc VALUES ('0003','02',87);
INSERT INTO sc VALUES ('0003','03',45);
INSERT INTO sc VALUES ('0004','01',41);
INSERT INTO sc VALUES ('0004','03',78);
INSERT INTO sc VALUES ('0005','01',46);
INSERT INTO sc VALUES ('0005','02',55);
INSERT INTO sc VALUES ('0005','03',69);
GO
---------- 以上为测试数据 ---------------
--1. Top 100 嵌套
SELECT * FROM (
SELECT TOP 100 PERCENT * FROM sc ORDER BY c#
) AS t
--2. Top 100 不嵌套
SELECT TOP 100 PERCENT * FROM sc ORDER BY c#
--3. Top 99 嵌套
SELECT * FROM (
SELECT TOP 99.99 PERCENT * FROM sc ORDER BY c#
) AS t
USE tempdb
GO
IF OBJECT_ID('sc') IS NOT NULL DROP TABLE sc
GO
CREATE TABLE sc(
s# VARCHAR(10),
c# VARCHAR(10),
score INT
)
GO
SET NOCOUNT ON
INSERT INTO sc VALUES ('0001','02',112);
INSERT INTO sc VALUES ('0001','03',23);
INSERT INTO sc VALUES ('0002','01',25);
INSERT INTO sc VALUES ('0001','01',78);
INSERT INTO sc VALUES ('0003','02',87);
INSERT INTO sc VALUES ('0003','03',45);
INSERT INTO sc VALUES ('0004','01',41);
INSERT INTO sc VALUES ('0004','03',78);
INSERT INTO sc VALUES ('0005','01',46);
INSERT INTO sc VALUES ('0005','02',55);
INSERT INTO sc VALUES ('0005','03',69);
GO
---------- 以上为测试数据 ---------------
--方法1. 将 Top 100 PERCENT => Top 99.99999 PERCENT
select * from (
select s#, string_agg(c#, '') as c#value
from (select TOP 99.99999 PERCENT * from sc order by c#) as sc2
group by s#
) as A
where A.s# = '0001'
/*
s# c#value
---------- --------
0001 010203
*/
--方法2
select * from (
select s#, string_agg(c#,'') WITHIN GROUP (ORDER BY c# ASC) as c#value
from sc
group by s#
) as A
where A.s# = '0001'
/*
s# c#value
---------- --------
0001 010203
*/
--方法3 去嵌套最简洁版
select s#, string_agg(c#,'') WITHIN GROUP (ORDER BY c# ASC) as c#value
from sc
WHERE s#='0001'
group by s#
/*
s# c#value
---------- --------
0001 010203
*/
SELECT
*
FROM
(
SELECT
s#,
string_agg(c#, '') within group(order by c#) AS c#value
FROM
(
SELECT TOP 100 PERCENT
*
FROM
SC
ORDER BY
c#
) AS sc2
GROUP BY
s#
) AS A;