有表A:
ID Type Name
1 1 asdf
2 1 qwer
3 2 asdf
4 3 zxcv
5 3 asdf
6 3 qwer
怎么得到
ID Type Name
1 1 asdf
3 2 asdf
4 3 zxcv
就是按Type分组 其他字段查出第一条
ID Type Name
1 1 asdf
2 1 qwer
3 2 asdf
4 3 zxcv
5 3 asdf
6 3 qwer
怎么得到
ID Type Name
1 1 asdf
3 2 asdf
4 3 zxcv
就是按Type分组 其他字段查出第一条
where (select count(*) from A where Type = a.Type and ID>a.ID) < 2
SELECT A.* FROM A join (SELECT MIN(ID) ID,Type FROM A GROUP BY Type) B ON A.ID=B.ID
(
[ID] INT,
[Type] NVARCHAR(10),
[Name] NVARCHAR(20)
)
INSERT INTO @A
SELECT 1,'1','asdf' UNION ALL
SELECT 2,'1','qwer' UNION ALL
SELECT 3,'2','asdf' UNION ALL
SELECT 4,'3','zxcv' UNION ALL
SELECT 5,'3','asdf' UNION ALL
SELECT 6,'3','qwer' SELECT * FROM @ASELECT C.[ID],C.[Type],B.[Name]
FROM
(SELECT MIN(A.[ID]) AS [ID],A.[Type]
FROM @A A
GROUP BY A.[Type]) C LEFT OUTER JOIN @A B
ON C.[ID] = B.[ID]--(6 個資料列受到影響)
--ID Type Name
------------- ---------- --------------------
--1 1 asdf
--2 1 qwer
--3 2 asdf
--4 3 zxcv
--5 3 asdf
--6 3 qwer
--
--(6 個資料列受到影響)
--
--ID Type Name
------------- ---------- --------------------
--1 1 asdf
--3 2 asdf
--4 3 zxcv(3 個資料列受到影響)
DECLARE @A TABLE
(
[ID] INT,
[Type] NVARCHAR(10),
[Name] NVARCHAR(20)
)--插入測試數據
INSERT INTO @A
SELECT 1,'1','asdf' UNION ALL
SELECT 2,'1','qwer' UNION ALL
SELECT 3,'2','asdf' UNION ALL
SELECT 4,'3','zxcv' UNION ALL
SELECT 5,'3','asdf' UNION ALL
SELECT 6,'3','qwer' --驗證測試數據
SELECT * FROM @A
--(6 個資料列受到影響)
--ID Type Name
------------- ---------- --------------------
--1 1 asdf
--2 1 qwer
--3 2 asdf
--4 3 zxcv
--5 3 asdf
--6 3 qwer--查詢需求數據
SELECT C.[ID],C.[Type],B.[Name]
FROM
(SELECT MIN(A.[ID]) AS [ID],A.[Type]
FROM @A A
GROUP BY A.[Type]) C LEFT OUTER JOIN @A B
ON C.[ID] = B.[ID]--結果:
--(6 個資料列受到影響)
--
--ID Type Name
------------- ---------- --------------------
--1 1 asdf
--3 2 asdf
--4 3 zxcv--(3 個資料列受到影響)
SELECT A.* FROM A JOIN (SELECT MIN(ID) ID,Type FROM A GROUP BY Type) B ON A.ID=B.ID