[MS SQL] Group by 後查詢出第一筆

ms sql group by

跟著鄉民看熱鬧 2020-07-27 13:59:54 ‧ 1479 瀏覽

分享至

想了解下表如何查詢出Group by customer後的每項第一筆資料：
id | customer | total
---+----------+------
1 | Joe | AA
2 | Sally | CC
3 | Joe | BB
4 | Sally | DD

結果：
FIRST(id) | customer | FIRST(total)
----------+----------+-------------
1 | Joe | AA
2 | Sally | CC

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

glj8989332

iT邦研究生 4 級 ‧ 2020-07-27 14:22:47

用partition by的語法

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Id DESC) AS rn
   FROM MyCustomer
)
SELECT Id, Customer, Total
FROM cte
WHERE rn = 1

可成功的範例
SQL Fiddle

回應
分享
檢舉

登入發表回應

rogeryao

iT邦超人 7 級 ‧ 2020-07-27 15:23:47

CREATE TABLE Test (
  [id] int,
  [customer] varchar(20),
  [total] varchar(20)
);

INSERT INTO Test ([id], [customer], [total])
  VALUES (1, 'Joe', 'AA'),
  (2, 'Sally', 'CC'),
  (3, 'Joe', 'BB'),
  (4, 'Sally', 'DD');

SELECT  *
FROM Test;

SELECT A.newid as 'FIRST(id)',A.customer,B.total as 'FIRST(total)'
FROM (
SELECT MIN(id) AS newid,customer
FROM Test
GROUP BY customer) AS A
LEFT JOIN Test AS B ON A.newid = B.id AND A.customer = B.customer
ORDER BY A.customer, A.newid

Demo